-- https://www.phpmyadmin.net/
--

START TRANSACTION;



--
-- Database: `u167398274_jpos`
--

-- --------------------------------------------------------

--
-- Table structure for table `deliveries`
--

CREATE TABLE `deliveries` (
  `id` int(11) NOT NULL,
  `purchase_order_id` int(11) DEFAULT NULL,
  `supplier_id` int(11) NOT NULL,
  `expected_delivery_date` date DEFAULT NULL,
  `actual_delivery_date` date DEFAULT NULL,
  `delivery_date` date DEFAULT NULL,
  `status` enum('Scheduled','In Transit','Delivered','Delayed','Cancelled') DEFAULT 'Scheduled',
  `tracking_number` varchar(255) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `inventory`
--

CREATE TABLE `inventory` (
  `id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `stock` int(11) DEFAULT 0,
  `price` decimal(10,2) DEFAULT 0.00,
  `barcode` varchar(50) DEFAULT NULL,
  `quantity` int(11) NOT NULL DEFAULT 0,
  `is_active` tinyint(1) DEFAULT 1,
  `expiration_date` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `inventory`
--

INSERT INTO `inventory` (`id`, `name`, `stock`, `price`, `barcode`, `quantity`, `is_active`, `expiration_date`) VALUES
(22, 'Screw Driver', 9996, 120.00, NULL, 0, 1, NULL),
(26, 'Steel Brash', 9982, 65.00, NULL, 0, 1, NULL),
(27, 'Tiles', 9985, 185.00, NULL, 0, 1, NULL),
(28, 'Pait Brush # 4 101.6mm', 9979, 145.00, NULL, 0, 1, NULL),
(29, 'Solignum wood 1 liter', 9990, 680.00, NULL, 0, 1, NULL),
(30, 'Vulca Seal Bostik', 9985, 75.00, NULL, 0, 1, NULL),
(31, 'rectube 1x3 ha', 9996, 787.00, NULL, 0, 1, NULL),
(32, 'tubular 1.5 2x2', 9993, 690.00, NULL, 0, 1, NULL),
(33, 'paint g200 latex white', 10000, 2600.00, NULL, 0, 1, NULL),
(34, 'hardiflex 4.5', 9996, 415.00, NULL, 0, 1, NULL),
(35, 'tile trim', 9997, 150.00, NULL, 0, 1, NULL),
(36, 'tile trim silver', 9999, 150.00, NULL, 0, 1, NULL),
(37, 'paint brush 1 1/2', 10000, 38.00, NULL, 0, 1, NULL),
(38, 'paint brush 2', 9997, 40.00, NULL, 0, 1, NULL),
(39, 'syto 40mm pad lock', 9999, 170.00, NULL, 0, 1, NULL),
(40, 'stainless steel lock 4', 10000, 110.00, NULL, 0, 1, NULL),
(41, 'philock narrow gold 4', 10000, 110.00, NULL, 0, 1, NULL),
(42, 'philock narrow 3', 10000, 85.00, NULL, 0, 1, NULL),
(43, 'no more nails 100g', 10000, 115.00, NULL, 0, 1, NULL),
(44, 'no more nails 30g', 10000, 75.00, NULL, 0, 1, NULL),
(45, 'vulca seal 75ml', 9999, 75.00, NULL, 0, 1, NULL),
(46, 'toilet bowl small', 9999, 1200.00, NULL, 0, 1, NULL),
(47, 'metal cladding white', 10000, 770.00, NULL, 0, 1, NULL),
(48, 'nylon rope', 9987, 35.00, NULL, 0, 1, NULL),
(49, '47x28', 9999, 1500.00, NULL, 0, 1, NULL),
(50, 'screen frame', 9998, 343.00, NULL, 0, 1, NULL),
(51, 'jw bumper', 9998, 267.00, NULL, 0, 1, NULL),
(52, 'black screw \"1 1/2', 9900, 1.50, NULL, 0, 1, NULL),
(53, 'metal screw 8x2', 9999, 85.00, NULL, 0, 1, NULL),
(54, 'fluted red wood finish', 9999, 450.00, NULL, 0, 1, NULL),
(55, 'metal cladding wf', 9990, 850.00, NULL, 0, 1, NULL),
(56, 'nylon rope', 9980, 15.00, NULL, 0, 1, NULL),
(57, 'tine wire', 9999, 75.00, NULL, 0, 1, NULL),
(58, 'Lockstil', 9999, 513.00, NULL, 0, 1, NULL),
(59, 'Interlocker INS', 10000, 530.00, NULL, 0, 1, NULL),
(60, 'Metal Screw 6x1/2', 10000, 65.00, NULL, 0, 1, NULL),
(61, 'Metal Screw 6x3/4', 10000, 75.00, NULL, 0, 1, NULL),
(62, 'Metal Screw 6x1', 10000, 90.00, NULL, 0, 1, NULL),
(63, 'Jalousy Screw 8x1/2', 10000, 130.00, NULL, 0, 1, NULL),
(64, 'Metal Screw 8x1/2', 10000, 95.00, NULL, 0, 1, NULL),
(65, 'Gross 12x3', 10000, 280.00, NULL, 0, 1, NULL),
(66, 'Gross 8x1', 10000, 85.00, NULL, 0, 1, NULL),
(67, 'Angle Bar 1/8x1 1x2 2.5mm', 10000, 310.00, NULL, 0, 1, NULL),
(68, 'Angle Bar 1/8x1 1/2 3mm', 10000, 520.00, NULL, 0, 1, NULL),
(69, 'alphalock h.a', 9999, 280.00, NULL, 0, 1, NULL),
(70, 'Metal Screw 12x3', 10000, 4.50, NULL, 0, 1, NULL),
(71, 'Metal Screw 12x3', 9975, 4.50, NULL, 0, 1, NULL),
(72, 'Toxs #8', 9975, 1.00, NULL, 0, 1, NULL),
(73, 'Black Screw 1 1/2 WOOD', 9950, 1.50, NULL, 0, 1, NULL),
(74, 'Metal Furring .4mm', 10000, 90.00, NULL, 0, 1, NULL),
(75, 'Carrying Channel .5mm', 10000, 95.00, NULL, 0, 1, NULL),
(76, 'Metal Stud 2x3 .4mm', 10000, 145.00, NULL, 0, 1, NULL),
(77, 'Metal Track .4mm', 10000, 140.00, NULL, 0, 1, NULL),
(78, 'Wall Angle .4mm', 10000, 40.00, NULL, 0, 1, NULL),
(79, 'Farring Clip', 10000, 5.00, NULL, 0, 1, NULL),
(80, 'Tile Adhesive', 9998, 365.00, NULL, 0, 1, NULL),
(81, 'Skimcoat', 9999, 495.00, NULL, 0, 1, NULL),
(82, 'Tubular 1x1 1.2mm', 10000, 303.00, NULL, 0, 1, NULL),
(83, 'Tubular 1x2 1.2mm', 10000, 420.00, NULL, 0, 1, NULL),
(84, 'Tubular 2x2 1.2mm', 10000, 576.00, NULL, 0, 1, NULL),
(85, 'Tubular 2x3 1.2mm', 10000, 720.00, NULL, 0, 1, NULL),
(86, 'Tubular 2x4 1.2mm', 10000, 870.00, NULL, 0, 1, NULL),
(87, 'Tubular 2x6 1.2mm', 10000, 1390.00, NULL, 0, 1, NULL),
(88, 'Tubular 1x1 1.5mm', 9995, 330.00, NULL, 0, 1, NULL),
(89, 'Tubular 1x2 1.5mm', 10000, 550.00, NULL, 0, 1, NULL),
(90, 'Tubular 2x2 1.5mm', 10000, 690.00, NULL, 0, 1, NULL),
(91, 'Tubular 2x3 1.5mm', 10000, 860.00, NULL, 0, 1, NULL),
(92, 'Tubular 2x4 1.5mm', 10000, 1195.00, NULL, 0, 1, NULL),
(93, 'Tubular 2x6 1.5mm', 10000, 1753.00, NULL, 0, 1, NULL),
(94, 'silver bronze 4x7 1/4', 9994, 1420.00, NULL, 0, 1, NULL),
(95, 'metal cladding wf', 9993, 825.00, NULL, 0, 1, NULL),
(96, 'metal cladding wf', 10000, 825.00, NULL, 0, 1, NULL),
(97, 'metal cladding', 10000, 825.00, NULL, 0, 1, NULL),
(98, 'black screw 3', 9970, 3.00, NULL, 0, 1, NULL),
(99, 'carborandom', 9998, 250.00, NULL, 0, 1, NULL),
(100, 'lavatory', 10000, 1200.00, NULL, 0, 1, NULL),
(101, 'labatory', 10000, 1200.00, NULL, 0, 1, NULL),
(102, 'lavatory', 9999, 1200.00, NULL, 0, 1, NULL),
(103, 'rectube 1x2', 9996, 540.00, NULL, 0, 1, NULL),
(104, 'double  jamb w screen', 9996, 431.00, NULL, 0, 1, NULL),
(105, 'hinges', 9997, 15.00, NULL, 0, 1, NULL),
(106, 'door closer', 9999, 150.00, NULL, 0, 1, NULL),
(107, 'bended handle', 9998, 35.00, NULL, 0, 1, NULL),
(108, 'blind rivets', 9998, 120.00, NULL, 0, 1, NULL),
(109, 'sealant white', 9970, 135.00, NULL, 0, 1, NULL),
(110, 'rectube 1x1', 9998, 320.00, NULL, 0, 1, NULL),
(111, 'sq 1x1', 10000, 320.00, NULL, 0, 1, NULL),
(112, 'rect 1 3/4x3', 10000, 920.00, NULL, 0, 1, NULL),
(113, 'rectube 1 3/4x4', 10000, 1100.00, NULL, 0, 1, NULL),
(114, 'ob 1 3/4x3', 10000, 750.00, NULL, 0, 1, NULL),
(115, 'ob 1 3/4x4', 10000, 790.00, NULL, 0, 1, NULL),
(116, 'sobc', 10000, 420.00, NULL, 0, 1, NULL),
(117, 'insolation foam double', 9997, 2800.00, NULL, 0, 1, NULL),
(118, 'sealant white w/n', 9990, 160.00, NULL, 0, 1, NULL),
(119, 'concrete nail #1', 9999, 100.00, NULL, 0, 1, NULL),
(120, 'roller brush 9', 9997, 195.00, NULL, 0, 1, NULL),
(121, 'top bottom', 9999, 478.00, NULL, 0, 1, NULL),
(122, 'd sill', 9999, 753.00, NULL, 0, 1, NULL),
(123, 'd. head', 10000, 620.00, NULL, 0, 1, NULL),
(124, 'lockstile h.a', 10000, 513.00, NULL, 0, 1, NULL),
(125, 'Interlocker h.a', 9999, 530.00, NULL, 0, 1, NULL),
(126, 'screen frame', 9999, 343.00, NULL, 0, 1, NULL),
(127, 'glass divider', 9991, 365.00, NULL, 0, 1, NULL),
(128, 'pannel roller w/ bearing', 9992, 14.00, NULL, 0, 1, NULL),
(129, 'screen guide', 9992, 5.00, NULL, 0, 1, NULL),
(130, 'd jamb', 9999, 431.00, NULL, 0, 1, NULL),
(131, 'Tubular 1x1 1.5mm', 10000, 330.00, NULL, 0, 1, NULL),
(132, 'wilding rog', 9999, 210.00, NULL, 0, 1, NULL),
(133, 'hinges \"3\"', 9997, 65.00, NULL, 0, 1, NULL),
(134, 'blind revits 5/32 x 3/4', 9999, 300.00, NULL, 0, 1, NULL),
(135, 'black screw \"2\"', 9970, 1.50, NULL, 0, 1, NULL),
(136, 'black screw 1', 9980, 1.00, NULL, 0, 1, NULL),
(137, 'tox # 6', 9999, 65.00, NULL, 0, 1, NULL),
(138, 'lagadi bakal', 9999, 35.00, NULL, 0, 1, NULL),
(139, 'plap dis', 9999, 45.00, NULL, 0, 1, NULL),
(140, 'sq 1 3/4x1 3/4', 9995, 680.00, NULL, 0, 1, NULL),
(141, 'apo cement', 9999, 230.00, NULL, 0, 1, NULL),
(142, 'b.rivits 5/32x1/2', 9999, 300.00, NULL, 0, 1, NULL),
(143, 'cement apo', 10000, 220.00, NULL, 0, 1, NULL),
(144, 'cement apo', 9995, 225.00, NULL, 0, 1, NULL),
(145, 'Wall Angle .4mm', 9980, 40.00, NULL, 0, 1, NULL),
(146, 'stanley fatmax', 10000, 395.00, NULL, 0, 1, NULL),
(147, 'diamond cup wheel', 10000, 380.00, NULL, 0, 1, NULL),
(148, 'screen bracket', 10000, 5.00, NULL, 0, 1, NULL),
(149, 'syto pad lock 30mm', 10000, 110.00, NULL, 0, 1, NULL),
(150, 'tiles 30x60 hs114', 9996, 95.00, NULL, 0, 1, NULL),
(151, 'tile trim gold', 9999, 150.00, NULL, 0, 1, NULL),
(152, 'royu 3 gang outlet', 9999, 85.00, NULL, 0, 1, NULL),
(153, 'sealant black', 9996, 135.00, NULL, 0, 1, NULL),
(154, 'ula padlock', 9999, 85.00, NULL, 0, 1, NULL),
(155, 'cylindrical knobset', 9999, 680.00, NULL, 0, 1, NULL),
(156, 'texscrew 2 1/2 metal', 9850, 2.50, NULL, 0, 1, NULL),
(157, 'pipes round 1 1/2 (s20)', 9999, 768.00, NULL, 0, 1, NULL),
(158, 'tiles 40x40', 9996, 95.00, NULL, 0, 1, NULL),
(159, 'stir nossing', 9999, 550.00, NULL, 0, 1, NULL),
(160, 'skimcoat', 10000, 495.00, NULL, 0, 1, NULL),
(161, 'solo paint', 9999, 2600.00, NULL, 0, 1, NULL),
(162, 'TILES 60X60', 9950, 185.00, NULL, 0, 1, NULL),
(163, 'Tile Adhesive', 10000, 365.00, NULL, 0, 1, NULL),
(164, 'yc perimeter pcw', 9999, 495.00, NULL, 0, 1, NULL),
(165, 'plainsheet 6ft {red}', 9999, 500.00, NULL, 0, 1, NULL),
(166, 'u clip', 9992, 150.00, NULL, 0, 1, NULL),
(167, 'selant', 10000, 135.00, NULL, 0, 1, NULL),
(168, 'metal tix screw 2 1/2', 9995, 625.00, NULL, 0, 1, NULL),
(169, 'metal screw 2 1/2', 10000, 625.00, NULL, 0, 1, NULL),
(170, 'jw bumper h/a', 9998, 267.00, NULL, 0, 1, NULL),
(171, 'metal black screw 1', 9960, 1.00, NULL, 0, 1, NULL),
(172, 'pvc pipe s600 orange \"4\"', 9999, 375.00, NULL, 0, 1, NULL),
(173, 'volt bring', 9998, 250.00, NULL, 0, 1, NULL);

-- --------------------------------------------------------

--
-- Table structure for table `items`
--

CREATE TABLE `items` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `sku` varchar(100) DEFAULT NULL,
  `category_id` int(11) DEFAULT NULL,
  `unit` varchar(50) DEFAULT 'pcs',
  `cost_price` decimal(10,2) DEFAULT NULL,
  `selling_price` decimal(10,2) DEFAULT NULL,
  `stock_quantity` int(11) DEFAULT 0,
  `minimum_stock` int(11) DEFAULT 0,
  `status` enum('Active','Inactive') DEFAULT 'Active',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `purchase_orders`
--

CREATE TABLE `purchase_orders` (
  `id` int(11) NOT NULL,
  `supplier_id` int(11) DEFAULT NULL,
  `item_id` int(11) DEFAULT NULL,
  `quantity` int(11) DEFAULT NULL,
  `status` enum('Pending','Completed','Cancelled') DEFAULT 'Pending',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `total_amount` decimal(10,2) NOT NULL DEFAULT 0.00,
  `order_date` datetime DEFAULT current_timestamp(),
  `delivery_date` datetime DEFAULT NULL,
  `expected_delivery_date` date DEFAULT NULL,
  `actual_delivery_date` date DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `priority` enum('Low','Medium','High') DEFAULT 'Medium',
  `created_by` int(11) DEFAULT NULL,
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `purchase_order_items`
--

CREATE TABLE `purchase_order_items` (
  `id` int(11) NOT NULL,
  `po_id` int(11) NOT NULL,
  `inventory_id` int(11) DEFAULT NULL,
  `item_name` varchar(255) NOT NULL,
  `quantity` int(11) NOT NULL,
  `unit_cost` decimal(10,2) NOT NULL,
  `total_cost` decimal(10,2) NOT NULL,
  `received_quantity` int(11) DEFAULT 0,
  `purchase_order_id` int(11) NOT NULL,
  `item_id` int(11) NOT NULL,
  `unit_price` decimal(10,2) NOT NULL DEFAULT 0.00,
  `total_price` decimal(10,2) NOT NULL DEFAULT 0.00
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `sales`
--

CREATE TABLE `sales` (
  `id` int(11) NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  `item_id` int(11) DEFAULT NULL,
  `quantity` int(11) DEFAULT NULL,
  `total_amount` decimal(10,2) NOT NULL DEFAULT 0.00,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `sale_date` datetime DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `sales`
--

INSERT INTO `sales` (`id`, `user_id`, `item_id`, `quantity`, `total_amount`, `created_at`, `sale_date`) VALUES
(84, 1, 46, 1, 1200.00, '2025-12-17 06:23:15', '2025-12-17 14:23:15'),
(85, 1, 48, 1, 35.00, '2025-12-17 06:39:29', '2025-12-17 14:39:29'),
(86, 1, 48, 1, 35.00, '2025-12-17 06:40:27', '2025-12-17 14:40:27'),
(87, 1, 48, 1, 35.00, '2025-12-17 06:44:55', '2025-12-17 14:44:55'),
(88, 1, 48, 10, 350.00, '2025-12-17 06:46:16', '2025-12-17 14:46:16'),
(89, 1, 49, 1, 1500.00, '2025-12-17 06:55:30', '2025-12-17 14:55:30'),
(90, 1, 50, 2, 686.00, '2025-12-17 06:58:44', '2025-12-17 14:58:44'),
(91, 1, 51, 2, 534.00, '2025-12-17 07:20:28', '2025-12-17 15:20:28'),
(92, 1, 52, 100, 150.00, '2025-12-17 07:22:53', '2025-12-17 15:22:53'),
(93, 1, 53, 1, 85.00, '2025-12-17 07:30:55', '2025-12-17 15:30:55'),
(94, 1, 54, 1, 450.00, '2025-12-17 07:33:38', '2025-12-17 15:33:38'),
(95, 1, 55, 10, 8500.00, '2025-12-17 07:40:19', '2025-12-17 15:40:19'),
(96, 1, 56, 3, 45.00, '2025-12-17 07:49:21', '2025-12-17 15:49:21'),
(97, 1, 56, 1, 15.00, '2025-12-17 07:50:47', '2025-12-17 15:50:47'),
(98, 1, 57, 1, 75.00, '2025-12-17 08:13:36', '2025-12-17 16:13:36'),
(99, 1, 69, 1, 280.00, '2025-12-17 08:25:10', '2025-12-17 16:25:10'),
(100, 1, 71, 25, 112.50, '2025-12-17 08:31:20', '2025-12-17 16:31:20'),
(101, 1, 72, 25, 25.00, '2025-12-17 08:31:20', '2025-12-17 16:31:20'),
(102, 1, 73, 50, 75.00, '2025-12-17 08:31:20', '2025-12-17 16:31:20'),
(103, 1, 36, 1, 150.00, '2025-12-18 00:30:34', '2025-12-18 08:30:34'),
(104, 1, 95, 7, 5775.00, '2025-12-18 00:53:41', '2025-12-18 08:53:41'),
(105, 1, 98, 30, 90.00, '2025-12-18 00:58:34', '2025-12-18 08:58:34'),
(106, 1, 99, 2, 500.00, '2025-12-18 01:02:04', '2025-12-18 09:02:04'),
(107, 1, 102, 1, 1200.00, '2025-12-18 01:09:56', '2025-12-18 09:09:56'),
(108, 1, 103, 4, 2160.00, '2025-12-18 01:21:45', '2025-12-18 09:21:45'),
(109, 1, 104, 4, 1724.00, '2025-12-18 01:48:39', '2025-12-18 09:48:39'),
(110, 1, 105, 3, 45.00, '2025-12-18 01:57:23', '2025-12-18 09:57:23'),
(111, 1, 106, 1, 150.00, '2025-12-18 01:57:23', '2025-12-18 09:57:23'),
(112, 1, 107, 2, 70.00, '2025-12-18 01:57:23', '2025-12-18 09:57:23'),
(113, 1, 108, 2, 240.00, '2025-12-18 01:57:23', '2025-12-18 09:57:23'),
(114, 1, 109, 10, 1350.00, '2025-12-18 02:03:48', '2025-12-18 10:03:48'),
(115, 1, 110, 2, 640.00, '2025-12-18 02:03:48', '2025-12-18 10:03:48'),
(116, 1, 117, 3, 8400.00, '2025-12-18 02:18:48', '2025-12-18 10:18:48'),
(117, 1, 118, 10, 1600.00, '2025-12-18 02:18:48', '2025-12-18 10:18:48'),
(118, 1, 119, 1, 100.00, '2025-12-18 02:18:48', '2025-12-18 10:18:48'),
(119, 1, 120, 3, 585.00, '2025-12-18 02:18:48', '2025-12-18 10:18:48'),
(120, 1, 38, 3, 120.00, '2025-12-18 02:18:48', '2025-12-18 10:18:48'),
(121, 1, 56, 6, 90.00, '2025-12-19 00:48:09', '2025-12-19 08:48:09'),
(122, 1, 121, 1, 478.00, '2025-12-19 00:58:28', '2025-12-19 08:58:28'),
(123, 1, 122, 1, 753.00, '2025-12-19 00:58:28', '2025-12-19 08:58:28'),
(124, 1, 128, 8, 112.00, '2025-12-19 00:58:28', '2025-12-19 08:58:28'),
(125, 1, 129, 8, 40.00, '2025-12-19 00:58:28', '2025-12-19 08:58:28'),
(126, 1, 127, 2, 730.00, '2025-12-19 00:58:28', '2025-12-19 08:58:28'),
(127, 1, 126, 1, 343.00, '2025-12-19 00:58:28', '2025-12-19 08:58:28'),
(128, 1, 58, 1, 513.00, '2025-12-19 00:58:28', '2025-12-19 08:58:28'),
(129, 1, 125, 1, 530.00, '2025-12-19 00:58:28', '2025-12-19 08:58:28'),
(130, 1, 130, 1, 431.00, '2025-12-19 00:58:28', '2025-12-19 08:58:28'),
(131, 1, 88, 2, 660.00, '2025-12-19 01:09:49', '2025-12-19 09:09:49'),
(132, 1, 132, 1, 210.00, '2025-12-19 01:09:49', '2025-12-19 09:09:49'),
(133, 1, 133, 3, 195.00, '2025-12-19 01:09:49', '2025-12-19 09:09:49'),
(134, 1, 134, 1, 300.00, '2025-12-19 01:09:49', '2025-12-19 09:09:49'),
(135, 1, 135, 30, 45.00, '2025-12-19 01:09:49', '2025-12-19 09:09:49'),
(136, 1, 136, 20, 20.00, '2025-12-19 01:09:49', '2025-12-19 09:09:49'),
(137, 1, 137, 1, 65.00, '2025-12-19 01:09:49', '2025-12-19 09:09:49'),
(138, 1, 138, 1, 35.00, '2025-12-19 01:09:49', '2025-12-19 09:09:49'),
(139, 1, 139, 1, 45.00, '2025-12-19 01:09:49', '2025-12-19 09:09:49'),
(140, 1, 140, 5, 3400.00, '2025-12-19 01:54:39', '2025-12-19 09:54:39'),
(141, 1, 127, 6, 2190.00, '2025-12-19 01:54:39', '2025-12-19 09:54:39'),
(142, 1, 109, 10, 1350.00, '2025-12-19 01:54:39', '2025-12-19 09:54:39'),
(143, 1, 56, 10, 150.00, '2025-12-19 02:01:33', '2025-12-19 10:01:33'),
(144, 1, 141, 1, 230.00, '2025-12-19 02:28:46', '2025-12-19 10:28:46'),
(145, 1, 142, 1, 300.00, '2025-12-19 02:43:05', '2025-12-19 10:43:05'),
(146, 1, 144, 5, 1125.00, '2025-12-19 02:47:09', '2025-12-19 10:47:09'),
(147, 1, 145, 20, 800.00, '2025-12-19 02:48:38', '2025-12-19 10:48:38'),
(148, 1, 45, 1, 75.00, '2025-12-19 03:01:40', '2025-12-19 11:01:40'),
(149, 1, 150, 4, 380.00, '2025-12-19 04:04:42', '2025-12-19 12:04:42'),
(150, 1, 151, 1, 150.00, '2025-12-19 04:04:42', '2025-12-19 12:04:42'),
(151, 1, 152, 1, 85.00, '2025-12-19 04:04:42', '2025-12-19 12:04:42'),
(152, 1, 127, 1, 365.00, '2025-12-19 04:16:14', '2025-12-19 12:16:14'),
(153, 1, 153, 4, 540.00, '2025-12-19 04:16:14', '2025-12-19 12:16:14'),
(154, 1, 88, 2, 660.00, '2025-12-19 04:34:34', '2025-12-19 12:34:34'),
(155, 1, 154, 1, 85.00, '2025-12-19 04:34:34', '2025-12-19 12:34:34'),
(156, 1, 155, 1, 680.00, '2025-12-19 04:34:34', '2025-12-19 12:34:34'),
(157, 1, 156, 150, 375.00, '2025-12-19 04:44:44', '2025-12-19 12:44:44'),
(158, 1, 88, 1, 330.00, '2025-12-19 04:47:37', '2025-12-19 12:47:37'),
(159, 1, 157, 1, 768.00, '2025-12-19 04:59:06', '2025-12-19 12:59:06'),
(160, 1, 161, 1, 2600.00, '2025-12-19 05:09:42', '2025-12-19 13:09:42'),
(161, 1, 159, 1, 550.00, '2025-12-19 05:09:42', '2025-12-19 13:09:42'),
(162, 1, 158, 4, 380.00, '2025-12-19 05:09:42', '2025-12-19 13:09:42'),
(163, 1, 162, 50, 9250.00, '2025-12-19 05:14:43', '2025-12-19 13:14:43'),
(164, 1, 80, 2, 730.00, '2025-12-19 05:14:43', '2025-12-19 13:14:43'),
(165, 1, 164, 1, 495.00, '2025-12-19 05:18:25', '2025-12-19 13:18:25'),
(166, 1, 81, 1, 495.00, '2025-12-19 05:25:18', '2025-12-19 13:25:18'),
(167, 1, 165, 1, 500.00, '2025-12-19 05:32:20', '2025-12-19 13:32:20'),
(168, 1, 94, 5, 7100.00, '2025-12-19 05:43:28', '2025-12-19 13:43:28'),
(169, 1, 166, 8, 1200.00, '2025-12-19 05:48:06', '2025-12-19 13:48:06'),
(170, 1, 109, 10, 1350.00, '2025-12-19 05:50:11', '2025-12-19 13:50:11'),
(171, 1, 168, 5, 3125.00, '2025-12-19 05:55:56', '2025-12-19 13:55:56'),
(172, 1, 94, 1, 1420.00, '2025-12-19 06:03:47', '2025-12-19 14:03:47'),
(173, 1, 170, 2, 534.00, '2025-12-19 06:25:40', '2025-12-19 14:25:40'),
(174, 1, 171, 40, 40.00, '2025-12-19 06:30:59', '2025-12-19 14:30:59'),
(175, 1, 172, 1, 375.00, '2025-12-19 06:36:54', '2025-12-19 14:36:54'),
(176, 1, 173, 2, 500.00, '2025-12-19 06:36:54', '2025-12-19 14:36:54'),
(177, 1, 39, 1, 170.00, '2026-02-14 07:45:53', '2026-02-14 15:45:53'),
(178, 1, 27, 1, 185.00, '2026-02-14 07:45:53', '2026-02-14 15:45:53'),
(179, 1, 35, 1, 150.00, '2026-02-14 07:45:53', '2026-02-14 15:45:53'),
(180, 1, 28, 1, 145.00, '2026-02-14 07:45:53', '2026-02-14 15:45:53'),
(181, 1, 28, 10, 1450.00, '2026-02-14 07:45:53', '2026-02-14 15:45:53');

-- --------------------------------------------------------

--
-- Table structure for table `suppliers`
--

CREATE TABLE `suppliers` (
  `id` int(11) NOT NULL,
  `name` varchar(100) DEFAULT NULL,
  `contact` varchar(50) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `phone` varchar(50) DEFAULT NULL,
  `payment_terms` varchar(100) DEFAULT NULL,
  `rating` int(11) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `suppliers`
--

INSERT INTO `suppliers` (`id`, `name`, `contact`, `address`, `email`, `phone`, `payment_terms`, `rating`, `created_at`, `updated_at`) VALUES
(10, 'kent', 'kent', 'TUPI', 'kent@gmail.com', '123123123', 'COD', 5, '2025-09-05 02:00:36', '2025-09-05 02:00:36'),
(11, 'ej', 'kulot', 'General Santos City', 'ejromero294@gmail.com', '09103443488', 'COD', 2, '2025-09-07 17:37:12', '2025-09-07 17:49:52');

-- --------------------------------------------------------

--
-- Table structure for table `supplier_contacts`
--

CREATE TABLE `supplier_contacts` (
  `id` int(11) NOT NULL,
  `supplier_id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `position` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `phone` varchar(50) DEFAULT NULL,
  `is_primary` tinyint(1) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `supplier_documents`
--

CREATE TABLE `supplier_documents` (
  `id` int(11) NOT NULL,
  `supplier_id` int(11) NOT NULL,
  `document_name` varchar(255) NOT NULL,
  `document_type` enum('Contract','Certificate','License','Insurance','Other') DEFAULT 'Other',
  `file_path` varchar(500) DEFAULT NULL,
  `expiry_date` date DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `uploaded_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `supplier_performance`
--

CREATE TABLE `supplier_performance` (
  `id` int(11) NOT NULL,
  `supplier_id` int(11) NOT NULL,
  `month_year` date NOT NULL,
  `total_orders` int(11) DEFAULT 0,
  `completed_orders` int(11) DEFAULT 0,
  `cancelled_orders` int(11) DEFAULT 0,
  `total_value` decimal(15,2) DEFAULT 0.00,
  `avg_delivery_time` decimal(5,2) DEFAULT 0.00,
  `on_time_deliveries` int(11) DEFAULT 0,
  `late_deliveries` int(11) DEFAULT 0,
  `quality_rating` decimal(3,2) DEFAULT 0.00,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `deliveries`
--
ALTER TABLE `deliveries`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_supplier_id` (`supplier_id`),
  ADD KEY `idx_purchase_order_id` (`purchase_order_id`),
  ADD KEY `idx_delivery_date` (`delivery_date`),
  ADD KEY `idx_status` (`status`);

--
-- Indexes for table `inventory`
--
ALTER TABLE `inventory`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `barcode` (`barcode`);

--
-- Indexes for table `items`
--
ALTER TABLE `items`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_name` (`name`),
  ADD KEY `idx_sku` (`sku`),
  ADD KEY `idx_status` (`status`);

--
-- Indexes for table `purchase_orders`
--
ALTER TABLE `purchase_orders`
  ADD PRIMARY KEY (`id`),
  ADD KEY `item_id` (`item_id`),
  ADD KEY `fk_purchase_orders_created_by` (`created_by`),
  ADD KEY `fk_purchase_orders_supplier_id` (`supplier_id`);

--
-- Indexes for table `purchase_order_items`
--
ALTER TABLE `purchase_order_items`
  ADD PRIMARY KEY (`id`),
  ADD KEY `po_id` (`po_id`),
  ADD KEY `inventory_id` (`inventory_id`),
  ADD KEY `fk_inventory_item_id` (`item_id`);

--
-- Indexes for table `sales`
--
ALTER TABLE `sales`
  ADD PRIMARY KEY (`id`),
  ADD KEY `user_id` (`user_id`),
  ADD KEY `sales_ibfk_2` (`item_id`);

--
-- Indexes for table `suppliers`
--
ALTER TABLE `suppliers`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `supplier_contacts`
--
ALTER TABLE `supplier_contacts`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_supplier_id` (`supplier_id`);

--
-- Indexes for table `supplier_documents`
--
ALTER TABLE `supplier_documents`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_supplier_id` (`supplier_id`),
  ADD KEY `idx_expiry_date` (`expiry_date`);

--
-- Indexes for table `supplier_performance`
--
ALTER TABLE `supplier_performance`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `unique_supplier_month` (`supplier_id`,`month_year`),
  ADD KEY `idx_supplier_id` (`supplier_id`),
  ADD KEY `idx_month_year` (`month_year`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `deliveries`
--
ALTER TABLE `deliveries`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `inventory`
--
ALTER TABLE `inventory`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=174;

--
-- AUTO_INCREMENT for table `items`
--
ALTER TABLE `items`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `purchase_orders`
--
ALTER TABLE `purchase_orders`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=28;

--
-- AUTO_INCREMENT for table `purchase_order_items`
--
ALTER TABLE `purchase_order_items`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=22;

--
-- AUTO_INCREMENT for table `sales`
--
ALTER TABLE `sales`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=182;

--
-- AUTO_INCREMENT for table `suppliers`
--
ALTER TABLE `suppliers`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=12;

--
-- AUTO_INCREMENT for table `supplier_contacts`
--
ALTER TABLE `supplier_contacts`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `supplier_documents`
--
ALTER TABLE `supplier_documents`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `supplier_performance`
--
ALTER TABLE `supplier_performance`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `purchase_orders`
--
ALTER TABLE `purchase_orders`
  ADD CONSTRAINT `fk_purchase_orders_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_purchase_orders_supplier_id` FOREIGN KEY (`supplier_id`) REFERENCES `suppliers` (`id`),
  ADD CONSTRAINT `purchase_orders_ibfk_1` FOREIGN KEY (`supplier_id`) REFERENCES `suppliers` (`id`),
  ADD CONSTRAINT `purchase_orders_ibfk_2` FOREIGN KEY (`item_id`) REFERENCES `inventory` (`id`);

--
-- Constraints for table `purchase_order_items`
--
ALTER TABLE `purchase_order_items`
  ADD CONSTRAINT `fk_inventory_item_id` FOREIGN KEY (`item_id`) REFERENCES `inventory` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `purchase_order_items_ibfk_1` FOREIGN KEY (`po_id`) REFERENCES `purchase_orders` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `purchase_order_items_ibfk_2` FOREIGN KEY (`inventory_id`) REFERENCES `inventory` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `sales`
--
ALTER TABLE `sales`
  ADD CONSTRAINT `sales_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `sales_ibfk_2` FOREIGN KEY (`item_id`) REFERENCES `inventory` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `supplier_contacts`
--
ALTER TABLE `supplier_contacts`
  ADD CONSTRAINT `supplier_contacts_ibfk_1` FOREIGN KEY (`supplier_id`) REFERENCES `suppliers` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `supplier_documents`
--
ALTER TABLE `supplier_documents`
  ADD CONSTRAINT `supplier_documents_ibfk_1` FOREIGN KEY (`supplier_id`) REFERENCES `suppliers` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `supplier_performance`
--
ALTER TABLE `supplier_performance`
  ADD CONSTRAINT `supplier_performance_ibfk_1` FOREIGN KEY (`supplier_id`) REFERENCES `suppliers` (`id`) ON DELETE CASCADE;
COMMIT;

