-- =============================================================
--  Turi Express Courier Services — Full Database Schema & Seed
--  Website: turischool.com/ship
--  Compatible: MySQL 5.7+ / MariaDB 10.3+
--  Created: 2026-04-30
-- =============================================================

-- Create and select the database
CREATE DATABASE IF NOT EXISTS `turi_express` 
  CHARACTER SET utf8mb4 
  COLLATE utf8mb4_unicode_ci;

USE `turi_express`;

-- =============================================================
-- TABLE: users
-- Admin/staff who log in to manage shipments
-- =============================================================
CREATE TABLE IF NOT EXISTS `users` (
  `id`              INT UNSIGNED    NOT NULL AUTO_INCREMENT,
  `name`            VARCHAR(150)    NOT NULL,
  `email`           VARCHAR(255)    NOT NULL UNIQUE,
  `password`        VARCHAR(255)    NOT NULL,          -- bcrypt hash
  `role`            ENUM('admin','staff') NOT NULL DEFAULT 'staff',
  `is_active`       TINYINT(1)      NOT NULL DEFAULT 1,
  `created_at`      TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`      TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- =============================================================
-- TABLE: shipments
-- Core tracking record — one row per package
-- =============================================================
CREATE TABLE IF NOT EXISTS `shipments` (
  `id`                    INT UNSIGNED    NOT NULL AUTO_INCREMENT,

  -- Tracking
  `tracking_number`       VARCHAR(50)     NOT NULL UNIQUE,   -- e.g. TEC-2024-0001
  `barcode`               VARCHAR(100)    NULL,              -- JsBarcode value

  -- Package details
  `description`           TEXT            NULL,
  `weight`                DECIMAL(10,2)   NULL,              -- kg
  `weight_unit`           ENUM('kg','lbs') NOT NULL DEFAULT 'kg',
  `quantity`              INT UNSIGNED    NOT NULL DEFAULT 1,
  `package_type`          VARCHAR(100)    NULL,              -- Box, Envelope, Pallet…
  `dimensions`            VARCHAR(100)    NULL,              -- e.g. "30x20x15 cm"

  -- Service
  `service_type`          ENUM('standard','express','air_freight','ocean_freight','cargo','packaging_storage')
                          NOT NULL DEFAULT 'standard',
  `shipping_mode`         ENUM('air','sea','land','multimodal') NOT NULL DEFAULT 'air',

  -- Locations
  `origin_address`        VARCHAR(500)    NOT NULL,
  `origin_city`           VARCHAR(100)    NOT NULL,
  `origin_state`          VARCHAR(100)    NULL,
  `origin_country`        VARCHAR(100)    NOT NULL,
  `origin_lat`            DECIMAL(10,7)   NULL,
  `origin_lng`            DECIMAL(10,7)   NULL,

  `destination_address`   VARCHAR(500)    NOT NULL,
  `destination_city`      VARCHAR(100)    NOT NULL,
  `destination_state`     VARCHAR(100)    NULL,
  `destination_country`   VARCHAR(100)    NOT NULL,
  `destination_lat`       DECIMAL(10,7)   NULL,
  `destination_lng`       DECIMAL(10,7)   NULL,

  -- Dates
  `ship_date`             DATE            NOT NULL,
  `estimated_delivery`    DATE            NULL,
  `actual_delivery`       DATETIME        NULL,

  -- Status
  `status`                ENUM(
                              'order_placed',
                              'picked_up',
                              'in_transit',
                              'out_for_delivery',
                              'delivered',
                              'on_hold',
                              'returned',
                              'cancelled'
                          ) NOT NULL DEFAULT 'order_placed',

  -- Financials (for receipt/invoice)
  `declared_value`        DECIMAL(12,2)   NULL,
  `currency`              CHAR(3)         NOT NULL DEFAULT 'NGN',
  `freight_charge`        DECIMAL(12,2)   NULL DEFAULT 0.00,
  `insurance_charge`      DECIMAL(12,2)   NULL DEFAULT 0.00,
  `handling_charge`       DECIMAL(12,2)   NULL DEFAULT 0.00,
  `other_charge`          DECIMAL(12,2)   NULL DEFAULT 0.00,
  `total_charge`          DECIMAL(12,2)   GENERATED ALWAYS AS (
                              `freight_charge` + `insurance_charge` + `handling_charge` + `other_charge`
                          ) STORED,

  -- Notes
  `special_instructions`  TEXT            NULL,
  `internal_notes`        TEXT            NULL,

  -- Meta
  `created_by`            INT UNSIGNED    NULL,
  `created_at`            TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`            TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (`id`),
  UNIQUE  INDEX `idx_tracking_number` (`tracking_number`),
  INDEX   `idx_status`                (`status`),
  INDEX   `idx_ship_date`             (`ship_date`),
  CONSTRAINT `fk_shipment_user`
    FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- =============================================================
-- TABLE: senders
-- Person/company sending the package
-- =============================================================
CREATE TABLE IF NOT EXISTS `senders` (
  `id`            INT UNSIGNED    NOT NULL AUTO_INCREMENT,
  `shipment_id`   INT UNSIGNED    NOT NULL,
  `full_name`     VARCHAR(200)    NOT NULL,
  `company`       VARCHAR(200)    NULL,
  `email`         VARCHAR(255)    NULL,
  `phone`         VARCHAR(50)     NOT NULL,
  `address`       VARCHAR(500)    NOT NULL,
  `city`          VARCHAR(100)    NOT NULL,
  `state`         VARCHAR(100)    NULL,
  `country`       VARCHAR(100)    NOT NULL,
  `postal_code`   VARCHAR(20)     NULL,
  `created_at`    TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_sender_shipment` (`shipment_id`),
  CONSTRAINT `fk_sender_shipment`
    FOREIGN KEY (`shipment_id`) REFERENCES `shipments` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- =============================================================
-- TABLE: receivers
-- Person/company receiving the package
-- =============================================================
CREATE TABLE IF NOT EXISTS `receivers` (
  `id`            INT UNSIGNED    NOT NULL AUTO_INCREMENT,
  `shipment_id`   INT UNSIGNED    NOT NULL,
  `full_name`     VARCHAR(200)    NOT NULL,
  `company`       VARCHAR(200)    NULL,
  `email`         VARCHAR(255)    NULL,
  `phone`         VARCHAR(50)     NOT NULL,
  `address`       VARCHAR(500)    NOT NULL,
  `city`          VARCHAR(100)    NOT NULL,
  `state`         VARCHAR(100)    NULL,
  `country`       VARCHAR(100)    NOT NULL,
  `postal_code`   VARCHAR(20)     NULL,
  `created_at`    TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_receiver_shipment` (`shipment_id`),
  CONSTRAINT `fk_receiver_shipment`
    FOREIGN KEY (`shipment_id`) REFERENCES `shipments` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- =============================================================
-- TABLE: tracking_history
-- Timeline of tracking events — drives the visual tracking bar
-- Each row = one status step shown in the UI (.box element)
-- =============================================================
CREATE TABLE IF NOT EXISTS `tracking_history` (
  `id`            INT UNSIGNED    NOT NULL AUTO_INCREMENT,
  `shipment_id`   INT UNSIGNED    NOT NULL,
  `status`        VARCHAR(150)    NOT NULL,   -- Label shown in .box .status
  `note`          TEXT            NULL,        -- Detail shown in .box .note (orange text)
  `location`      VARCHAR(300)    NULL,        -- City/facility where event occurred
  `event_date`    DATETIME        NOT NULL,    -- Shown in .box .date
  `is_active`     TINYINT(1)      NOT NULL DEFAULT 1,  -- Highlighted (orange) in tracker bar
  `sort_order`    INT UNSIGNED    NOT NULL DEFAULT 0,
  `created_at`    TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_history_shipment`    (`shipment_id`),
  INDEX `idx_history_event_date`  (`event_date`),
  CONSTRAINT `fk_history_shipment`
    FOREIGN KEY (`shipment_id`) REFERENCES `shipments` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- =============================================================
-- TABLE: contact_messages
-- Messages submitted via the contact form
-- =============================================================
CREATE TABLE IF NOT EXISTS `contact_messages` (
  `id`            INT UNSIGNED    NOT NULL AUTO_INCREMENT,
  `name`          VARCHAR(200)    NOT NULL,
  `email`         VARCHAR(255)    NOT NULL,
  `address`       VARCHAR(500)    NULL,
  `message`       TEXT            NOT NULL,
  `is_read`       TINYINT(1)      NOT NULL DEFAULT 0,
  `created_at`    TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_contact_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- =============================================================
-- TABLE: newsletter_subscribers
-- Email addresses from the newsletter form
-- =============================================================
CREATE TABLE IF NOT EXISTS `newsletter_subscribers` (
  `id`            INT UNSIGNED    NOT NULL AUTO_INCREMENT,
  `email`         VARCHAR(255)    NOT NULL UNIQUE,
  `is_active`     TINYINT(1)      NOT NULL DEFAULT 1,
  `subscribed_at` TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `idx_newsletter_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- =============================================================
-- TABLE: quote_requests
-- "Get A Quote" modal form submissions
-- =============================================================
CREATE TABLE IF NOT EXISTS `quote_requests` (
  `id`            INT UNSIGNED    NOT NULL AUTO_INCREMENT,
  `name`          VARCHAR(200)    NOT NULL,
  `email`         VARCHAR(255)    NOT NULL,
  `address`       VARCHAR(500)    NULL,
  `message`       TEXT            NOT NULL,
  `is_handled`    TINYINT(1)      NOT NULL DEFAULT 0,
  `created_at`    TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_quote_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- =============================================================
-- SEED DATA
-- =============================================================

-- Admin user (password: Admin@1234 — bcrypt hash below)
INSERT INTO `users` (`name`, `email`, `password`, `role`) VALUES
('Turi Admin', 'admin@turischool.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin'),
('Turi Staff', 'staff@turischool.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'staff');


-- Shipment 1 — In Transit (Air Freight, Lagos → London)
INSERT INTO `shipments` (
  `tracking_number`, `barcode`, `description`, `weight`, `quantity`, `package_type`,
  `dimensions`, `service_type`, `shipping_mode`,
  `origin_address`, `origin_city`, `origin_state`, `origin_country`, `origin_lat`, `origin_lng`,
  `destination_address`, `destination_city`, `destination_state`, `destination_country`, `destination_lat`, `destination_lng`,
  `ship_date`, `estimated_delivery`, `status`,
  `declared_value`, `currency`, `freight_charge`, `insurance_charge`, `handling_charge`, `other_charge`,
  `special_instructions`, `created_by`
) VALUES (
  'TEC-2024-0001', 'TEC2024000110001', 'Electronics — Laptop and accessories',
  4.50, 1, 'Box', '40x30x20 cm', 'air_freight', 'air',
  '25 Ogunbiyi Street, Ajao Estate', 'Lagos', 'Lagos', 'Nigeria', 6.5568, 3.3491,
  '14 Baker Street', 'London', 'England', 'United Kingdom', 51.5236, -0.1577,
  '2024-12-10', '2024-12-17', 'in_transit',
  350000.00, 'NGN', 85000.00, 12500.00, 5000.00, 2500.00,
  'Fragile — Handle with care', 1
);

INSERT INTO `senders` (`shipment_id`, `full_name`, `company`, `email`, `phone`, `address`, `city`, `state`, `country`, `postal_code`)
VALUES (1, 'Adebayo Okafor', 'TechBridge Nigeria Ltd', 'adebayo@techbridge.ng', '08012345678',
        '25 Ogunbiyi Street, Ajao Estate', 'Lagos', 'Lagos', 'Nigeria', '100213');

INSERT INTO `receivers` (`shipment_id`, `full_name`, `company`, `email`, `phone`, `address`, `city`, `state`, `country`, `postal_code`)
VALUES (1, 'James Williams', 'BritTech Solutions', 'james.w@brittechsolutions.co.uk', '+447911123456',
        '14 Baker Street', 'London', 'England', 'United Kingdom', 'NW1 6XE');

INSERT INTO `tracking_history` (`shipment_id`, `status`, `note`, `location`, `event_date`, `is_active`, `sort_order`) VALUES
(1, 'Order Placed',      'Shipment booked and confirmed',                         'Lagos, Nigeria',           '2024-12-10 09:00:00', 0, 1),
(1, 'Picked Up',         'Package collected from sender address',                  'Lagos, Nigeria',           '2024-12-11 11:30:00', 0, 2),
(1, 'In Transit',        'Departed Murtala Muhammed International Airport (LOS)',  'Lagos, Nigeria',           '2024-12-12 03:45:00', 1, 3),
(1, 'In Transit',        'Arrived at Heathrow cargo hub',                          'London, United Kingdom',   '2024-12-13 08:15:00', 0, 4),
(1, 'Out for Delivery',  'Package out for final delivery',                         'London, United Kingdom',   NULL, 0, 5),
(1, 'Delivered',         'Delivered successfully',                                 'London, United Kingdom',   NULL, 0, 6);


-- Shipment 2 — Delivered (Ocean Freight, Lagos → Dubai)
INSERT INTO `shipments` (
  `tracking_number`, `barcode`, `description`, `weight`, `quantity`, `package_type`,
  `dimensions`, `service_type`, `shipping_mode`,
  `origin_address`, `origin_city`, `origin_state`, `origin_country`, `origin_lat`, `origin_lng`,
  `destination_address`, `destination_city`, `destination_state`, `destination_country`, `destination_lat`, `destination_lng`,
  `ship_date`, `estimated_delivery`, `actual_delivery`, `status`,
  `declared_value`, `currency`, `freight_charge`, `insurance_charge`, `handling_charge`, `other_charge`,
  `created_by`
) VALUES (
  'TEC-2024-0002', 'TEC2024000210002', 'Textiles — Ankara fabrics (commercial quantity)',
  120.00, 5, 'Pallet', '120x80x100 cm', 'ocean_freight', 'sea',
  '3 Apapa Wharf Road', 'Lagos', 'Lagos', 'Nigeria', 6.4390, 3.3680,
  'Jebel Ali Free Zone, Warehouse 47', 'Dubai', 'Dubai', 'United Arab Emirates', 24.9835, 55.0777,
  '2024-11-05', '2024-11-28', '2024-11-26 14:30:00', 'delivered',
  1200000.00, 'NGN', 210000.00, 35000.00, 15000.00, 8000.00,
  1
);

INSERT INTO `senders` (`shipment_id`, `full_name`, `company`, `email`, `phone`, `address`, `city`, `state`, `country`, `postal_code`)
VALUES (2, 'Ngozi Adeyemi', 'Fab Africa Exports', 'ngozi@fabafricaexports.com', '09051234567',
        '3 Apapa Wharf Road', 'Lagos', 'Lagos', 'Nigeria', '100001');

INSERT INTO `receivers` (`shipment_id`, `full_name`, `company`, `email`, `phone`, `address`, `city`, `state`, `country`, `postal_code`)
VALUES (2, 'Ahmed Al-Rashid', 'Gulf Textile Trading LLC', 'ahmed@gulftextile.ae', '+971501234567',
        'Jebel Ali Free Zone, Warehouse 47', 'Dubai', 'Dubai', 'United Arab Emirates', '00000');

INSERT INTO `tracking_history` (`shipment_id`, `status`, `note`, `location`, `event_date`, `is_active`, `sort_order`) VALUES
(2, 'Order Placed',     'Shipment booked and confirmed',                     'Lagos, Nigeria',               '2024-11-05 08:00:00', 0, 1),
(2, 'Picked Up',        'Package collected from Apapa warehouse',             'Lagos, Nigeria',               '2024-11-06 10:00:00', 0, 2),
(2, 'In Transit',       'Vessel departed Apapa Port — MV Ocean Horizon',     'Lagos, Nigeria',               '2024-11-08 22:00:00', 0, 3),
(2, 'In Transit',       'Vessel arrived Port of Jebel Ali',                  'Dubai, UAE',                   '2024-11-24 07:30:00', 0, 4),
(2, 'Out for Delivery', 'Customs cleared — dispatched to delivery address',  'Dubai, UAE',                   '2024-11-26 09:00:00', 0, 5),
(2, 'Delivered',        'Delivered and signed for by Ahmed Al-Rashid',       'Dubai, UAE',                   '2024-11-26 14:30:00', 1, 6);


-- Shipment 3 — On Hold (Cargo Transportation, Port Harcourt → Abuja)
INSERT INTO `shipments` (
  `tracking_number`, `barcode`, `description`, `weight`, `quantity`, `package_type`,
  `dimensions`, `service_type`, `shipping_mode`,
  `origin_address`, `origin_city`, `origin_state`, `origin_country`, `origin_lat`, `origin_lng`,
  `destination_address`, `destination_city`, `destination_state`, `destination_country`, `destination_lat`, `destination_lng`,
  `ship_date`, `estimated_delivery`, `status`,
  `declared_value`, `currency`, `freight_charge`, `insurance_charge`, `handling_charge`, `other_charge`,
  `special_instructions`, `created_by`
) VALUES (
  'TEC-2025-0003', 'TEC2025000310003', 'Industrial equipment — spare parts',
  75.00, 3, 'Crate', '80x60x60 cm', 'cargo', 'land',
  '7 Rumuola Road', 'Port Harcourt', 'Rivers', 'Nigeria', 4.8156, 7.0498,
  '15 Constitution Avenue', 'Abuja', 'FCT', 'Nigeria', 9.0579, 7.4951,
  '2025-01-15', '2025-01-18', 'on_hold',
  500000.00, 'NGN', 45000.00, 10000.00, 8000.00, 3000.00,
  'Customs documentation required — contact office', 1
);

INSERT INTO `senders` (`shipment_id`, `full_name`, `company`, `email`, `phone`, `address`, `city`, `state`, `country`, `postal_code`)
VALUES (3, 'Emeka Nwosu', 'PetroTech Equipment Ltd', 'emeka@petrotechequip.com', '08098765432',
        '7 Rumuola Road', 'Port Harcourt', 'Rivers', 'Nigeria', '500001');

INSERT INTO `receivers` (`shipment_id`, `full_name`, `company`, `email`, `phone`, `address`, `city`, `state`, `country`, `postal_code`)
VALUES (3, 'Uche Obi', 'Federal Ministry of Works', 'uche.obi@fmworks.gov.ng', '09020000123',
        '15 Constitution Avenue', 'Abuja', 'FCT', 'Nigeria', '900001');

INSERT INTO `tracking_history` (`shipment_id`, `status`, `note`, `location`, `event_date`, `is_active`, `sort_order`) VALUES
(3, 'Order Placed', 'Shipment booked and confirmed',                                  'Port Harcourt, Rivers',    '2025-01-15 09:00:00', 0, 1),
(3, 'Picked Up',    'Package collected from sender',                                  'Port Harcourt, Rivers',    '2025-01-15 15:00:00', 0, 2),
(3, 'In Transit',   'En route to Abuja via East-West Road',                           'Enugu, Nigeria',           '2025-01-16 08:00:00', 0, 3),
(3, 'On Hold',      'Shipment held at Abuja customs for documentation verification',   'Abuja, FCT',               '2025-01-17 11:00:00', 1, 4),
(3, 'Out for Delivery', 'Awaiting clearance',                                         'Abuja, FCT',               NULL, 0, 5),
(3, 'Delivered',    'Pending delivery',                                               'Abuja, FCT',               NULL, 0, 6);


-- Sample newsletter subscriber
INSERT INTO `newsletter_subscribers` (`email`) VALUES
('subscriber1@example.com'),
('subscriber2@example.com');

-- Sample contact message
INSERT INTO `contact_messages` (`name`, `email`, `address`, `message`) VALUES
('Chidi Eze', 'chidi.eze@example.com', '12 Marina Street, Lagos',
 'I would like to know the status of my shipment. Please contact me urgently.');

-- Sample quote request
INSERT INTO `quote_requests` (`name`, `email`, `address`, `message`) VALUES
('Fatima Sule', 'fatima.sule@example.com', '44 Aminu Kano Crescent, Abuja',
 'Need a quote for shipping 20kg of goods from Abuja to London next month.');


-- =============================================================
-- USEFUL VIEWS
-- =============================================================

-- Shipment overview with sender, receiver, and current status
CREATE OR REPLACE VIEW `v_shipment_overview` AS
SELECT
  s.id,
  s.tracking_number,
  s.status,
  s.service_type,
  s.shipping_mode,
  s.ship_date,
  s.estimated_delivery,
  s.actual_delivery,
  s.origin_city,
  s.origin_country,
  s.destination_city,
  s.destination_country,
  s.total_charge,
  s.currency,
  sn.full_name    AS sender_name,
  sn.phone        AS sender_phone,
  r.full_name     AS receiver_name,
  r.phone         AS receiver_phone,
  s.created_at
FROM `shipments` s
LEFT JOIN `senders`   sn ON sn.shipment_id = s.id
LEFT JOIN `receivers`  r ON r.shipment_id  = s.id;


-- Latest tracking event per shipment (for dashboard summary)
CREATE OR REPLACE VIEW `v_latest_tracking_event` AS
SELECT
  th.shipment_id,
  th.status        AS latest_status,
  th.note          AS latest_note,
  th.location      AS latest_location,
  th.event_date    AS latest_event_date
FROM `tracking_history` th
INNER JOIN (
  SELECT shipment_id, MAX(sort_order) AS max_order
  FROM   `tracking_history`
  WHERE  event_date IS NOT NULL
  GROUP  BY shipment_id
) latest ON latest.shipment_id = th.shipment_id
        AND latest.max_order   = th.sort_order;


-- =============================================================
-- END OF FILE
-- =============================================================
