-- Delta Tides Global Private Limited
-- Clean Import for cPanel / phpMyAdmin (No CREATE DATABASE / USE statements)

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+05:00";

-- Drop existing tables if re-importing
DROP TABLE IF EXISTS `shipments`;
DROP TABLE IF EXISTS `cold_chain_logs`;
DROP TABLE IF EXISTS `inquiry_items`;
DROP TABLE IF EXISTS `inquiries`;
DROP TABLE IF EXISTS `clients`;
DROP TABLE IF EXISTS `product_specifications`;
DROP TABLE IF EXISTS `products`;
DROP TABLE IF EXISTS `categories`;
DROP TABLE IF EXISTS `users`;

-- Tables Structure
CREATE TABLE `users` (
  `user_id` INT AUTO_INCREMENT PRIMARY KEY,
  `full_name` VARCHAR(150) NOT NULL,
  `email` VARCHAR(150) NOT NULL UNIQUE,
  `password_hash` VARCHAR(255) NOT NULL,
  `role` ENUM('admin', 'export_manager', 'qa_officer') DEFAULT 'export_manager',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `categories` (
  `category_id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL,
  `slug` VARCHAR(100) NOT NULL UNIQUE,
  `description` TEXT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `products` (
  `product_id` INT AUTO_INCREMENT PRIMARY KEY,
  `category_id` INT NOT NULL,
  `commercial_name` VARCHAR(150) NOT NULL,
  `scientific_name` VARCHAR(150) NOT NULL,
  `fao_zone` VARCHAR(50) DEFAULT 'FAO 51 (Arabian Sea)',
  `catch_origin` VARCHAR(100) DEFAULT 'Sindh Coast & Indus Delta, Pakistan',
  `harvest_season` VARCHAR(100) DEFAULT 'Year-Round',
  `is_featured` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`category_id`) REFERENCES `categories` (`category_id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `product_specifications` (
  `spec_id` INT AUTO_INCREMENT PRIMARY KEY,
  `product_id` INT NOT NULL,
  `cut_type` VARCHAR(100) NOT NULL,
  `freezing_type` VARCHAR(100) NOT NULL,
  `available_grades` VARCHAR(255) NOT NULL,
  `glaze_ratio` VARCHAR(50) DEFAULT '0% - 5% Protective Glaze',
  `packaging` VARCHAR(150) DEFAULT '10kg / 20kg Master Carton (Poly Lined)',
  FOREIGN KEY (`product_id`) REFERENCES `products` (`product_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `clients` (
  `client_id` INT AUTO_INCREMENT PRIMARY KEY,
  `company_name` VARCHAR(200) NOT NULL,
  `contact_name` VARCHAR(150) NOT NULL,
  `email` VARCHAR(150) NOT NULL,
  `phone` VARCHAR(50) NOT NULL,
  `country` VARCHAR(100) NOT NULL,
  `destination_port` VARCHAR(150) NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `inquiries` (
  `inquiry_id` INT AUTO_INCREMENT PRIMARY KEY,
  `inquiry_code` VARCHAR(50) NOT NULL UNIQUE,
  `client_id` INT NOT NULL,
  `incoterm` ENUM('FOB Karachi (KPT)', 'FOB Port Qasim (PQA)', 'CFR', 'CIF') NOT NULL,
  `container_size` ENUM('20ft Reefer', '40ft High Cube Reefer') NOT NULL,
  `required_tonnage` DECIMAL(8,2) NOT NULL,
  `target_dispatch_date` DATE NULL,
  `status` ENUM('New', 'Under Review', 'Quote Issued', 'Approved', 'Shipped') DEFAULT 'New',
  `message` TEXT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`client_id`) REFERENCES `clients` (`client_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `inquiry_items` (
  `item_id` INT AUTO_INCREMENT PRIMARY KEY,
  `inquiry_id` INT NOT NULL,
  `product_id` INT NOT NULL,
  `spec_id` INT NULL,
  `tonnage` DECIMAL(8,2) NOT NULL,
  FOREIGN KEY (`inquiry_id`) REFERENCES `inquiries` (`inquiry_id`) ON DELETE CASCADE,
  FOREIGN KEY (`product_id`) REFERENCES `products` (`product_id`),
  FOREIGN KEY (`spec_id`) REFERENCES `product_specifications` (`spec_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `cold_chain_logs` (
  `log_id` INT AUTO_INCREMENT PRIMARY KEY,
  `batch_number` VARCHAR(100) NOT NULL,
  `landing_jetty` VARCHAR(100) DEFAULT 'Karachi Fish Harbour',
  `landing_temp_c` DECIMAL(4,1) NOT NULL,
  `blast_freezing_temp_c` DECIMAL(4,1) NOT NULL,
  `storage_temp_c` DECIMAL(4,1) NOT NULL,
  `reefer_temp_c` DECIMAL(4,1) NOT NULL,
  `haccp_certificate_no` VARCHAR(100) NULL,
  `logged_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Initial Seed Data
INSERT INTO `categories` (`name`, `slug`, `description`) VALUES
('Pelagic Fish', 'pelagic-fish', 'Offshore migratory species with dense protein content'),
('Demersal Species', 'demersal-species', 'Bottom-dwelling white flesh commercial species'),
('Crustaceans', 'crustaceans', 'Marine shrimp varieties harvested from Indus deltaic creeks'),
('Cephalopods', 'cephalopods', 'Fresh blast-frozen squid and cuttlefish');

INSERT INTO `products` (`category_id`, `commercial_name`, `scientific_name`, `harvest_season`) VALUES
(1, 'Ribbonfish (Hairtail)', 'Trichiurus lepturus', 'August - April'),
(2, 'Silver Pomfret', 'Pampus argenteus', 'September - March'),
(1, 'Yellowfin Tuna', 'Thunnus albacares', 'October - May'),
(4, 'Indian Ocean Squid', 'Uroteuthis duvaucelii', 'September - May'),
(3, 'Banana Prawn (Jaira)', 'Penaeus merguiensis', 'August - February'),
(1, 'Indian Mackerel', 'Rastrelliger kanagurta', 'September - April');

INSERT INTO `product_specifications` (`product_id`, `cut_type`, `freezing_type`, `available_grades`, `packaging`) VALUES
(1, 'Whole Round', 'Contact Plate Blast (-40°C)', '300-500g, 500-700g, 700g+', '10kg Block / Master Carton'),
(2, 'Whole Round (IWP)', 'Semi-IQF', '200-300g, 300-400g, 400-500g', '10kg Master Carton'),
(3, 'G&G / Loin', 'Air Blast', '10-20kg, 20-30kg, 30kg+', 'Poly-wrapped Export Sacks'),
(4, 'Whole Cleaned', 'Plate Frozen Block', 'U/5, U/10, 10/20 pcs/kg', '2x5kg Slab Cartons'),
(5, 'Headless Shell-On (HLSO)', 'IQF', '16/20, 21/25, 26/30 pcs/lb', '10x1kg Inner Bags'),
(6, 'Whole Round', 'Plate Frozen', '6-8, 8-10, 10-12 pcs/kg', '10kg Block Carton');

INSERT INTO `cold_chain_logs` (`batch_number`, `landing_jetty`, `landing_temp_c`, `blast_freezing_temp_c`, `storage_temp_c`, `reefer_temp_c`, `haccp_certificate_no`) VALUES
('DTG-2026-B01', 'Karachi Fish Harbour (KFH)', 2.8, -39.4, -22.1, -25.0, 'HACCP-PK-DTG-902');

COMMIT;