CREATE DATABASE IF NOT EXISTS swiftpath
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE swiftpath;

CREATE TABLE IF NOT EXISTS admins (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(80) NOT NULL UNIQUE,
  email VARCHAR(160) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  full_name VARCHAR(120) NOT NULL,
  role ENUM('super_admin','admin') NOT NULL DEFAULT 'admin',
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  last_login_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_admins_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS orders (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tracking_id VARCHAR(16) NOT NULL,
  recipient_name VARCHAR(120) NOT NULL,
  ship_to_country VARCHAR(120) NOT NULL,
  ship_to_address VARCHAR(255) NOT NULL,
  ship_from_name VARCHAR(120) NOT NULL DEFAULT '',
  ship_from_address VARCHAR(255) NOT NULL DEFAULT '',
  weight_kg DECIMAL(10,2) NOT NULL,
  package_value DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  shipping_fee DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  delivery_service VARCHAR(120) NOT NULL DEFAULT '',
  shipment_created_date DATE NOT NULL,
  security_protocol VARCHAR(120) NOT NULL DEFAULT '',
  package_description TEXT NULL,
  current_status ENUM('Order Placed','Dispatched','In Transit','At Sorting Center','Out for Delivery','Delivered') NOT NULL DEFAULT 'Order Placed',
  estimated_delivery VARCHAR(120) NOT NULL DEFAULT '',
  dispatch_title VARCHAR(160) NOT NULL DEFAULT '',
  dispatch_location VARCHAR(160) NOT NULL DEFAULT '',
  dispatch_message VARCHAR(500) NOT NULL DEFAULT '',
  next_milestone VARCHAR(160) NOT NULL DEFAULT '',
  last_status_at DATETIME NULL,
  created_by INT UNSIGNED NULL,
  updated_by INT UNSIGNED NULL,
  deleted_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_orders_tracking_id (tracking_id),
  INDEX idx_orders_tracking_active (tracking_id, deleted_at),
  INDEX idx_orders_status (current_status),
  INDEX idx_orders_recipient (recipient_name),
  INDEX idx_orders_created_at (created_at),
  CONSTRAINT fk_orders_created_by FOREIGN KEY (created_by) REFERENCES admins(id) ON DELETE SET NULL,
  CONSTRAINT fk_orders_updated_by FOREIGN KEY (updated_by) REFERENCES admins(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS tracking_progress (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_id BIGINT UNSIGNED NOT NULL,
  status ENUM('Order Placed','Dispatched','In Transit','At Sorting Center','Out for Delivery','Delivered') NOT NULL,
  step_order TINYINT UNSIGNED NOT NULL,
  progress_percent TINYINT UNSIGNED NOT NULL DEFAULT 0,
  completed_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_progress_order_status (order_id, status),
  INDEX idx_progress_order_step (order_id, step_order),
  CONSTRAINT fk_progress_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS tracking_history (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_id BIGINT UNSIGNED NOT NULL,
  status VARCHAR(120) NOT NULL,
  location VARCHAR(160) NOT NULL DEFAULT '',
  description VARCHAR(500) NOT NULL DEFAULT '',
  occurred_at DATETIME NOT NULL,
  sort_order INT UNSIGNED NOT NULL DEFAULT 0,
  is_highlight TINYINT(1) NOT NULL DEFAULT 0,
  created_by INT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_history_order_sort (order_id, sort_order, occurred_at),
  INDEX idx_history_order_time (order_id, occurred_at),
  INDEX idx_history_status (status),
  CONSTRAINT fk_history_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
  CONSTRAINT fk_history_created_by FOREIGN KEY (created_by) REFERENCES admins(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS login_sessions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  admin_id INT UNSIGNED NOT NULL,
  session_id VARCHAR(128) NOT NULL,
  token_hash VARCHAR(64) NULL,
  ip_address VARCHAR(45) NOT NULL DEFAULT '',
  user_agent VARCHAR(255) NOT NULL DEFAULT '',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  expires_at DATETIME NULL,
  revoked_at DATETIME NULL,
  UNIQUE KEY uq_login_session_id (session_id),
  INDEX idx_login_sessions_token (token_hash, revoked_at),
  INDEX idx_login_sessions_admin (admin_id, revoked_at),
  CONSTRAINT fk_login_sessions_admin FOREIGN KEY (admin_id) REFERENCES admins(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS activity_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  admin_id INT UNSIGNED NULL,
  order_id BIGINT UNSIGNED NULL,
  action VARCHAR(80) NOT NULL,
  metadata JSON NULL,
  ip_address VARCHAR(45) NOT NULL DEFAULT '',
  user_agent VARCHAR(255) NOT NULL DEFAULT '',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_activity_admin_time (admin_id, created_at),
  INDEX idx_activity_order_time (order_id, created_at),
  INDEX idx_activity_action (action),
  CONSTRAINT fk_activity_admin FOREIGN KEY (admin_id) REFERENCES admins(id) ON DELETE SET NULL,
  CONSTRAINT fk_activity_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO admins (username, email, password_hash, full_name, role)
VALUES ('admin', 'admin@swiftpath.com', '$2y$10$VGxu4sP/js23P5DwdyjrJupqHf7Gw0D8H4833vRZfM6KW7eGOPUme', 'SwiftPath Administrator', 'super_admin')
ON DUPLICATE KEY UPDATE email = VALUES(email), updated_at = CURRENT_TIMESTAMP;

INSERT INTO orders (
  tracking_id, recipient_name, ship_to_country, ship_to_address, ship_from_name, ship_from_address,
  weight_kg, package_value, shipping_fee, delivery_service, shipment_created_date, security_protocol,
  package_description, current_status, estimated_delivery, dispatch_title, dispatch_location,
  dispatch_message, next_milestone, last_status_at, created_by, updated_by
)
VALUES (
  'SPD856277004', 'Cora Childers', 'United States', '214 Royal Drive, Melbourne, FL 32904',
  'Cole Hawkins', 'Canada, Ontario, Logistics Hub YYZ', 14.00, 1000.00, 417.00,
  'Economy Air (7 days)', '2026-04-28', 'Insured & Tracked', 'Cash / Documents',
  'Dispatched', 'May 2, 2026', 'Dispatched from Distribution Hub', 'Distribution Hub - Toronto',
  'Shipment en route to air freight terminal.', 'In transit to regional gateway',
  '2026-04-29 09:50:00', 1, 1
)
ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID(id);

SET @demo_order_id = LAST_INSERT_ID();

INSERT INTO tracking_progress (order_id, status, step_order, progress_percent, completed_at)
VALUES
(@demo_order_id, 'Order Placed', 1, 0, '2026-04-28 16:56:00'),
(@demo_order_id, 'Dispatched', 2, 20, '2026-04-29 09:50:00'),
(@demo_order_id, 'In Transit', 3, 40, NULL),
(@demo_order_id, 'At Sorting Center', 4, 60, NULL),
(@demo_order_id, 'Out for Delivery', 5, 80, NULL),
(@demo_order_id, 'Delivered', 6, 100, NULL)
ON DUPLICATE KEY UPDATE completed_at = VALUES(completed_at), progress_percent = VALUES(progress_percent);

INSERT INTO tracking_history (order_id, status, location, description, occurred_at, sort_order, is_highlight, created_by)
SELECT @demo_order_id, 'Order Placed', 'Origin Warehouse - Canada Gateway', 'Order confirmed, awaiting processing.', '2026-04-28 16:56:00', 1, 0, 1
WHERE NOT EXISTS (SELECT 1 FROM tracking_history WHERE order_id = @demo_order_id AND status = 'Order Placed' AND occurred_at = '2026-04-28 16:56:00');

INSERT INTO tracking_history (order_id, status, location, description, occurred_at, sort_order, is_highlight, created_by)
SELECT @demo_order_id, 'Order Processed', 'Sorting Facility', 'Label created, package registered in system.', '2026-04-28 16:51:00', 2, 0, 1
WHERE NOT EXISTS (SELECT 1 FROM tracking_history WHERE order_id = @demo_order_id AND status = 'Order Processed' AND occurred_at = '2026-04-28 16:51:00');

INSERT INTO tracking_history (order_id, status, location, description, occurred_at, sort_order, is_highlight, created_by)
SELECT @demo_order_id, 'Dispatched', 'Distribution Hub - Toronto', 'Shipment en route to air freight terminal.', '2026-04-29 09:50:00', 3, 1, 1
WHERE NOT EXISTS (SELECT 1 FROM tracking_history WHERE order_id = @demo_order_id AND status = 'Dispatched' AND occurred_at = '2026-04-29 09:50:00');

INSERT INTO tracking_history (order_id, status, location, description, occurred_at, sort_order, is_highlight, created_by)
SELECT @demo_order_id, 'Arrived at Air Cargo Hub', 'Chicago International Gateway', 'Cleared for international transit.', '2026-04-30 02:14:00', 4, 0, 1
WHERE NOT EXISTS (SELECT 1 FROM tracking_history WHERE order_id = @demo_order_id AND status = 'Arrived at Air Cargo Hub' AND occurred_at = '2026-04-30 02:14:00');

INSERT INTO tracking_history (order_id, status, location, description, occurred_at, sort_order, is_highlight, created_by)
SELECT @demo_order_id, 'In Transit to Destination', 'Flight scheduled', 'Estimated arrival at regional facility soon.', '2026-05-01 08:22:00', 5, 0, 1
WHERE NOT EXISTS (SELECT 1 FROM tracking_history WHERE order_id = @demo_order_id AND status = 'In Transit to Destination' AND occurred_at = '2026-05-01 08:22:00');

-- Default login after import:
-- username/email: admin or admin@swiftpath.com
-- password: admin123

-- Optimization notes:
-- 1. tracking_id has a unique key plus a composite active lookup index for fast public tracking checks.
-- 2. tracking_history is ordered by (order_id, occurred_at) to render the track.html timeline efficiently.
-- 3. Soft deletes keep auditability while hiding removed shipments from public/admin default queries.
-- 4. activity_logs and login_sessions support production auditing and incident review.