-- ============================================================================
-- PHASE 4 — Inventory & Purchasing: Part Categories, Parts, Suppliers,
-- Purchases, Inventory Transactions, Service/Repair Parts Connection
-- Run after schema_phase3.sql.
-- ============================================================================

SET NAMES utf8mb4;
USE `garage_management`;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE IF NOT EXISTS `part_categories` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL,
  `description` VARCHAR(255) DEFAULT NULL,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `uq_part_categories_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `suppliers` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(150) NOT NULL,
  `contact_person` VARCHAR(150) DEFAULT NULL,
  `phone` VARCHAR(30) DEFAULT NULL,
  `email` VARCHAR(150) DEFAULT NULL,
  `address` VARCHAR(255) DEFAULT NULL,
  `tax_number` VARCHAR(60) DEFAULT NULL,
  `payment_terms` VARCHAR(100) DEFAULT NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `uq_suppliers_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `parts` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `sku` VARCHAR(40) NOT NULL,
  `part_number` VARCHAR(60) DEFAULT NULL,
  `name` VARCHAR(150) NOT NULL,
  `part_category_id` INT UNSIGNED DEFAULT NULL,
  `brand` VARCHAR(100) DEFAULT NULL,
  `compatible_vehicle` VARCHAR(150) DEFAULT NULL,
  `unit` VARCHAR(30) NOT NULL DEFAULT 'pcs',
  `cost_price` DECIMAL(12,2) NOT NULL DEFAULT 0,
  `selling_price` DECIMAL(12,2) NOT NULL DEFAULT 0,
  `stock_quantity` DECIMAL(12,2) NOT NULL DEFAULT 0,
  `min_stock_level` DECIMAL(12,2) NOT NULL DEFAULT 0,
  `max_stock_level` DECIMAL(12,2) DEFAULT NULL,
  `supplier_id` INT UNSIGNED DEFAULT NULL,
  `location` VARCHAR(100) DEFAULT NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `uq_parts_sku` (`sku`),
  KEY `idx_parts_category` (`part_category_id`),
  KEY `idx_parts_supplier` (`supplier_id`),
  KEY `idx_parts_name` (`name`),
  CONSTRAINT `fk_parts_category` FOREIGN KEY (`part_category_id`) REFERENCES `part_categories`(`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_parts_supplier` FOREIGN KEY (`supplier_id`) REFERENCES `suppliers`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `work_order_items`
  ADD CONSTRAINT `fk_wo_items_part` FOREIGN KEY IF NOT EXISTS (`part_id`) REFERENCES `parts`(`id`) ON DELETE SET NULL;

CREATE TABLE IF NOT EXISTS `inventory_transactions` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `part_id` INT UNSIGNED NOT NULL,
  `transaction_type` ENUM('purchase','service_usage','repair_usage','return','adjustment','damage','transfer') NOT NULL,
  `quantity` DECIMAL(12,2) NOT NULL COMMENT 'signed: positive = stock in, negative = stock out',
  `reference_type` VARCHAR(30) DEFAULT NULL,
  `reference_id` INT UNSIGNED DEFAULT NULL,
  `notes` VARCHAR(255) DEFAULT NULL,
  `user_id` INT UNSIGNED DEFAULT NULL,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  KEY `idx_inv_txn_part` (`part_id`),
  KEY `idx_inv_txn_type` (`transaction_type`),
  CONSTRAINT `fk_inv_txn_part` FOREIGN KEY (`part_id`) REFERENCES `parts`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_inv_txn_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `purchases` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `purchase_number` VARCHAR(30) NOT NULL,
  `supplier_id` INT UNSIGNED NOT NULL,
  `purchase_date` DATE NOT NULL,
  `status` ENUM('pending','received','cancelled') NOT NULL DEFAULT 'pending',
  `payment_status` ENUM('unpaid','partial','paid') NOT NULL DEFAULT 'unpaid',
  `subtotal` DECIMAL(12,2) NOT NULL DEFAULT 0,
  `discount` DECIMAL(12,2) NOT NULL DEFAULT 0,
  `tax` DECIMAL(12,2) NOT NULL DEFAULT 0,
  `total` DECIMAL(12,2) NOT NULL DEFAULT 0,
  `paid_amount` DECIMAL(12,2) NOT NULL DEFAULT 0,
  `notes` VARCHAR(255) DEFAULT NULL,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `received_at` DATETIME DEFAULT NULL,
  UNIQUE KEY `uq_purchase_number` (`purchase_number`),
  KEY `idx_purchases_supplier` (`supplier_id`),
  CONSTRAINT `fk_purchases_supplier` FOREIGN KEY (`supplier_id`) REFERENCES `suppliers`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `purchase_items` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `purchase_id` INT UNSIGNED NOT NULL,
  `part_id` INT UNSIGNED DEFAULT NULL,
  `description` VARCHAR(200) NOT NULL,
  `quantity` DECIMAL(12,2) NOT NULL DEFAULT 1,
  `unit_cost` DECIMAL(12,2) NOT NULL DEFAULT 0,
  `line_total` DECIMAL(12,2) NOT NULL DEFAULT 0,
  KEY `idx_purchase_items_purchase` (`purchase_id`),
  CONSTRAINT `fk_purchase_items_purchase` FOREIGN KEY (`purchase_id`) REFERENCES `purchases`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_purchase_items_part` FOREIGN KEY (`part_id`) REFERENCES `parts`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS = 1;