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

START TRANSACTION;



--
-- Database: `stock_system`
--

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

--
-- Table structure for table `admin_approvals`
--

CREATE TABLE `admin_approvals` (
  `id` int(11) NOT NULL,
  `mrf_id` int(11) NOT NULL,
  `form_number` varchar(100) DEFAULT NULL,
  `ref_id` varchar(100) DEFAULT NULL,
  `approved_at` datetime DEFAULT current_timestamp(),
  `approved_by` varchar(100) DEFAULT 'admin'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `mrf`
--

CREATE TABLE `mrf` (
  `id` int(11) NOT NULL,
  `ref_id` varchar(50) DEFAULT NULL,
  `form_number` varchar(50) NOT NULL,
  `date_created` date NOT NULL,
  `client_store` varchar(100) NOT NULL,
  `office_id` int(11) NOT NULL,
  `requested_by` varchar(100) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `status` enum('pending','approved') NOT NULL DEFAULT 'pending'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

--
-- Dumping data for table `mrf`
--

INSERT INTO `mrf` (`id`, `ref_id`, `form_number`, `date_created`, `client_store`, `office_id`, `requested_by`, `created_at`, `status`) VALUES
(86, '', 'MRF-0001', '2025-11-19', 'RT moujan Mall 13182 Khamis Mushayat', 1, 'naimu', '2025-11-19 07:21:50', 'approved'),
(87, '', 'MRF-0002', '2025-11-19', 'RT Sarath al abida 13141', 1, 'Mujeeb', '2025-11-19 12:13:07', 'pending');

--
-- Triggers `mrf`
--
DELIMITER $$
CREATE TRIGGER `update_stocklog_mrf` AFTER UPDATE ON `mrf` FOR EACH ROW BEGIN

    IF NEW.status = 'approved' THEN

        UPDATE stock_log

        SET mrf_id = NEW.id

        WHERE from_office_id = NEW.office_id

          AND DATE(date) = NEW.date_created

          AND mrf_id IS NULL;

    END IF;

END
$$
DELIMITER ;

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

--
-- Table structure for table `mrf_items`
--

CREATE TABLE `mrf_items` (
  `id` int(11) NOT NULL,
  `mrf_id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `quantity` int(11) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

--
-- Dumping data for table `mrf_items`
--

INSERT INTO `mrf_items` (`id`, `mrf_id`, `product_id`, `description`, `quantity`, `created_at`) VALUES
(156, 86, 55, '', 2, '2025-11-19 07:21:50'),
(157, 86, 56, '', 1, '2025-11-19 07:21:50'),
(158, 87, 57, '', 6, '2025-11-19 12:13:07');

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

--
-- Table structure for table `offices`
--

CREATE TABLE `offices` (
  `id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `region` varchar(100) NOT NULL,
  `incharge` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `offices`
--

INSERT INTO `offices` (`id`, `name`, `region`, `incharge`) VALUES
(1, 'Head Office Warehouse', 'Dammam', 'Shaz'),
(12, 'Riyadh', 'Riyadh', 'Anees');

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

--
-- Table structure for table `products`
--

CREATE TABLE `products` (
  `id` int(11) NOT NULL,
  `pro_id` varchar(20) NOT NULL,
  `name` varchar(100) NOT NULL,
  `date_added` datetime DEFAULT current_timestamp(),
  `quantity` int(11) NOT NULL DEFAULT 0,
  `category` varchar(50) NOT NULL,
  `model` varchar(100) NOT NULL,
  `brand` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `products`
--

INSERT INTO `products` (`id`, `pro_id`, `name`, `date_added`, `quantity`, `category`, `model`, `brand`) VALUES
(39, 'PRO-001', '6 MP Smart Hybrid Light Fixed Dome Network Camera', '2025-11-18 11:31:38', 0, 'Security', 'DS-2CD1163G2-LIU', 'Hikvision'),
(40, 'PRO-002', 'Cable tie - 250', '2025-11-18 11:32:27', 0, 'Accessories', 'Cable tie - 250', 'Vision'),
(41, 'PRO-003', 'Cable tie - 200', '2025-11-18 11:32:50', 0, 'Accessories', 'Cable tie - 200', 'Vision'),
(42, 'PRO-004', 'Casette 9MM', '2025-11-18 11:33:28', 0, 'Accessories', 'Casette 9MM', 'Vision'),
(43, 'PRO-005', 'HDMI EXTENDER 200M', '2025-11-18 11:34:23', 0, 'Accessories', 'HDEX200M', 'Vision'),
(44, 'PRO-006', 'HDMI EXTENDER 120M', '2025-11-18 11:35:02', 0, 'Accessories', 'HDEX120M', 'Vision'),
(45, 'PRO-007', 'HDMI EXTENDER 60M', '2025-11-18 11:35:31', 0, 'Accessories', 'HDEX60M', 'Vision'),
(46, 'PRO-008', 'PVC Box Small', '2025-11-18 11:36:22', 0, 'Accessories', 'PVC Box Small', 'Vision'),
(47, 'PRO-009', 'PVC Box deep', '2025-11-18 11:36:37', 0, 'Accessories', 'PVC Box deep', 'Vision'),
(48, 'PRO-010', 'RJ45 Connector', '2025-11-18 11:37:06', 0, 'Accessories', 'RJ45 Connector', 'Vision'),
(49, 'PRO-011', 'HDMI Cable 4k 3M', '2025-11-18 11:38:26', 0, 'Accessories', 'HDCA4K3M', 'QP'),
(50, 'PRO-012', 'HDMI Cable 4K 1.5M', '2025-11-18 11:39:02', 0, 'Accessories', 'HDCA4K1.5M', 'QP'),
(51, 'PRO-013', 'WaterProof camera Box 10x10', '2025-11-18 11:40:26', 0, 'Accessories', 'WTRPF100x100', 'Vision'),
(52, 'PRO-014', 'FB-302 FUJISTAR WALL MOUNT-26\"-55\"', '2025-11-18 11:41:55', 0, 'Accessories', 'FB-302', 'Fujistar'),
(53, 'PRO-015', 'Numbering Book 1-99', '2025-11-18 11:42:34', 0, 'Accessories', 'NUMB1-99', 'Vision'),
(55, 'PRO-016', 'AJAX DoorProtect Wireless magnetic opening detector', '2025-11-19 09:59:24', 0, 'Security', 'DoorProtect', 'Ajax'),
(56, 'PRO-017', 'AJAX SpaceControl Two-way wireless key fob with panic button', '2025-11-19 10:00:24', 0, 'Security', 'SpaceControl', 'Ajax'),
(57, 'PRO-018', 'Battery', '2025-11-19 14:00:20', 0, 'Electrical', '12v9AH', 'Vision');

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

--
-- Table structure for table `product_serials`
--

CREATE TABLE `product_serials` (
  `id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  `stock_batch_id` int(11) NOT NULL,
  `serial_number` varchar(255) DEFAULT NULL,
  `status` enum('in_stock','sold','transferred') NOT NULL DEFAULT 'in_stock'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `product_serials`
--

INSERT INTO `product_serials` (`id`, `product_id`, `stock_batch_id`, `serial_number`, `status`) VALUES
(245, 56, 137, 'ace3ce0b1', 'transferred'),
(246, 55, 138, '4823114014932', 'transferred'),
(247, 55, 138, '4823114014932', 'transferred'),
(248, 57, 139, '123', 'in_stock'),
(249, 57, 139, '123', 'in_stock');

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

--
-- Table structure for table `product_stock`
--

CREATE TABLE `product_stock` (
  `id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  `office_id` int(11) NOT NULL,
  `quantity` int(11) NOT NULL,
  `cost` decimal(10,2) NOT NULL,
  `date_added` datetime DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `stock_batches`
--

CREATE TABLE `stock_batches` (
  `id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  `office_id` int(11) NOT NULL,
  `quantity` int(11) NOT NULL,
  `unit_cost` decimal(10,2) NOT NULL,
  `date_added` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `stock_batches`
--

INSERT INTO `stock_batches` (`id`, `product_id`, `office_id`, `quantity`, `unit_cost`, `date_added`) VALUES
(137, 56, 1, 0, 96.60, '2025-11-19 08:29:19'),
(138, 55, 1, 0, 124.20, '2025-11-19 08:31:55'),
(139, 57, 1, 2, 53.00, '2025-11-19 12:20:32');

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

--
-- Table structure for table `stock_log`
--

CREATE TABLE `stock_log` (
  `id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  `mrf_id` int(11) DEFAULT NULL,
  `from_office_id` int(11) DEFAULT NULL,
  `to_office_id` int(11) DEFAULT NULL,
  `quantity` int(11) NOT NULL,
  `type` enum('in','out','transfer','return') NOT NULL,
  `date` datetime DEFAULT current_timestamp(),
  `unit_cost` decimal(10,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `stock_log`
--

INSERT INTO `stock_log` (`id`, `product_id`, `mrf_id`, `from_office_id`, `to_office_id`, `quantity`, `type`, `date`, `unit_cost`) VALUES
(335, 56, NULL, NULL, 1, 1, 'in', '2025-11-19 11:29:19', 96.60),
(336, 55, NULL, NULL, 1, 2, 'in', '2025-11-19 11:31:55', 124.20),
(338, 55, 86, 1, NULL, 2, 'out', '2025-11-19 12:27:22', 124.20),
(339, 56, 86, 1, NULL, 1, 'out', '2025-11-19 12:27:22', 96.60),
(340, 57, NULL, NULL, 1, 2, 'in', '2025-11-19 15:20:32', 53.00);

--
-- Indexes for dumped tables
--

--
-- Indexes for table `admin_approvals`
--
ALTER TABLE `admin_approvals`
  ADD PRIMARY KEY (`id`),
  ADD KEY `mrf_id` (`mrf_id`);

--
-- Indexes for table `mrf`
--
ALTER TABLE `mrf`
  ADD PRIMARY KEY (`id`),
  ADD KEY `office_id` (`office_id`);

--
-- Indexes for table `mrf_items`
--
ALTER TABLE `mrf_items`
  ADD PRIMARY KEY (`id`),
  ADD KEY `mrf_id` (`mrf_id`),
  ADD KEY `product_id` (`product_id`);

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

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

--
-- Indexes for table `product_serials`
--
ALTER TABLE `product_serials`
  ADD PRIMARY KEY (`id`),
  ADD KEY `product_id` (`product_id`),
  ADD KEY `stock_batch_id` (`stock_batch_id`);

--
-- Indexes for table `product_stock`
--
ALTER TABLE `product_stock`
  ADD PRIMARY KEY (`id`),
  ADD KEY `product_id` (`product_id`);

--
-- Indexes for table `stock_batches`
--
ALTER TABLE `stock_batches`
  ADD PRIMARY KEY (`id`),
  ADD KEY `product_id` (`product_id`),
  ADD KEY `office_id` (`office_id`);

--
-- Indexes for table `stock_log`
--
ALTER TABLE `stock_log`
  ADD PRIMARY KEY (`id`),
  ADD KEY `product_id` (`product_id`),
  ADD KEY `from_office_id` (`from_office_id`),
  ADD KEY `to_office_id` (`to_office_id`);

--
-- AUTO_INCREMENT for dumped tables
--

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

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

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

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

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

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

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

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

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

--
-- Constraints for dumped tables
--

--
-- Constraints for table `admin_approvals`
--
ALTER TABLE `admin_approvals`
  ADD CONSTRAINT `admin_approvals_ibfk_1` FOREIGN KEY (`mrf_id`) REFERENCES `mrf` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `mrf`
--
ALTER TABLE `mrf`
  ADD CONSTRAINT `mrf_ibfk_1` FOREIGN KEY (`office_id`) REFERENCES `offices` (`id`);

--
-- Constraints for table `mrf_items`
--
ALTER TABLE `mrf_items`
  ADD CONSTRAINT `mrf_items_ibfk_1` FOREIGN KEY (`mrf_id`) REFERENCES `mrf` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `mrf_items_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`);

--
-- Constraints for table `product_serials`
--
ALTER TABLE `product_serials`
  ADD CONSTRAINT `product_serials_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`),
  ADD CONSTRAINT `product_serials_ibfk_2` FOREIGN KEY (`stock_batch_id`) REFERENCES `stock_batches` (`id`);

--
-- Constraints for table `product_stock`
--
ALTER TABLE `product_stock`
  ADD CONSTRAINT `product_stock_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`);

--
-- Constraints for table `stock_batches`
--
ALTER TABLE `stock_batches`
  ADD CONSTRAINT `stock_batches_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`),
  ADD CONSTRAINT `stock_batches_ibfk_2` FOREIGN KEY (`office_id`) REFERENCES `offices` (`id`);

--
-- Constraints for table `stock_log`
--
ALTER TABLE `stock_log`
  ADD CONSTRAINT `stock_log_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`),
  ADD CONSTRAINT `stock_log_ibfk_2` FOREIGN KEY (`from_office_id`) REFERENCES `offices` (`id`),
  ADD CONSTRAINT `stock_log_ibfk_3` FOREIGN KEY (`to_office_id`) REFERENCES `offices` (`id`);
COMMIT;

