CREATE DATABASE IF NOT EXISTS courier CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE courier;

CREATE TABLE admins (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(190) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  status ENUM('active','disabled') NOT NULL DEFAULT 'active',
  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
) ENGINE=InnoDB;

CREATE TABLE shipments (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tracking_number VARCHAR(32) NOT NULL UNIQUE,
  reference_number VARCHAR(80) NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'CREATED',
  origin_city VARCHAR(120) NOT NULL,
  origin_country VARCHAR(120) NOT NULL,
  destination_city VARCHAR(120) NOT NULL,
  destination_country VARCHAR(120) NOT NULL,
  current_location VARCHAR(180) NULL,
  dispatch_at DATETIME NULL,
  estimated_delivery_at DATETIME NULL,
  delivered_at DATETIME NULL,
  sender_name VARCHAR(160) NOT NULL,
  sender_email VARCHAR(190) NULL,
  sender_phone VARCHAR(50) NULL,
  sender_address TEXT NULL,
  recipient_name VARCHAR(160) NOT NULL,
  recipient_email VARCHAR(190) NULL,
  recipient_phone VARCHAR(50) NULL,
  recipient_address TEXT NULL,
  package_description VARCHAR(255) NULL,
  package_type VARCHAR(80) NULL,
  weight DECIMAL(12,2) NOT NULL DEFAULT 0,
  weight_unit VARCHAR(10) NOT NULL DEFAULT 'kg',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_shipments_status(status),
  INDEX idx_shipments_destination(destination_country)
) ENGINE=InnoDB;

CREATE TABLE shipment_events (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  shipment_id BIGINT UNSIGNED NOT NULL,
  status VARCHAR(40) NOT NULL,
  title VARCHAR(180) NOT NULL,
  description TEXT NULL,
  location VARCHAR(180) NULL,
  event_time DATETIME NOT NULL,
  created_by INT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_event_shipment FOREIGN KEY (shipment_id) REFERENCES shipments(id) ON DELETE CASCADE,
  CONSTRAINT fk_event_admin FOREIGN KEY (created_by) REFERENCES admins(id) ON DELETE SET NULL,
  INDEX idx_events_shipment_time(shipment_id,event_time)
) ENGINE=InnoDB;

CREATE TABLE reviews (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(190) NULL,
  rating TINYINT UNSIGNED NOT NULL,
  review TEXT NOT NULL,
  status ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  admin_response TEXT NULL,
  approved_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_reviews_status(status)
) ENGINE=InnoDB;

CREATE TABLE contact_messages (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(190) NOT NULL,
  phone VARCHAR(50) NULL,
  subject VARCHAR(190) NULL,
  message TEXT NOT NULL,
  status ENUM('unread','read','archived') NOT NULL DEFAULT 'unread',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_messages_status(status)
) ENGINE=InnoDB;

CREATE TABLE audit_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  admin_id INT UNSIGNED NULL,
  action VARCHAR(100) NOT NULL,
  entity_type VARCHAR(80) NOT NULL,
  entity_id BIGINT UNSIGNED NULL,
  metadata JSON NULL,
  ip_address VARCHAR(45) NULL,
  user_agent VARCHAR(500) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_audit_admin FOREIGN KEY (admin_id) REFERENCES admins(id) ON DELETE SET NULL,
  INDEX idx_audit_created(created_at),
  INDEX idx_audit_entity(entity_type,entity_id)
) ENGINE=InnoDB;

-- Generate a real password hash before inserting the first administrator:
-- php -r "echo password_hash('CHANGE_THIS_PASSWORD', PASSWORD_DEFAULT), PHP_EOL;"
-- INSERT INTO admins (name,email,password_hash) VALUES ('Administrator','admin@example.com','PASTE_HASH_HERE');

INSERT INTO shipments
(tracking_number,status,origin_city,origin_country,destination_city,destination_country,current_location,estimated_delivery_at,sender_name,recipient_name,package_description,weight)
VALUES
('DEMO7X9K2P','IN_TRANSIT','London','United Kingdom','Michigan','United States','London Distribution Centre',DATE_ADD(NOW(),INTERVAL 4 DAY),'Demo Sender','Demo Recipient','Sample parcel',2.50);

SET @demo_shipment_id = (SELECT id FROM shipments WHERE tracking_number='DEMO7X9K2P');
INSERT INTO shipment_events
(shipment_id,status,title,description,location,event_time)
VALUES
(@demo_shipment_id,'CREATED','Shipment created','Shipment record created.','London',DATE_SUB(NOW(),INTERVAL 3 DAY)),
(@demo_shipment_id,'PICKED_UP','Shipment picked up','Parcel collected from sender.','London',DATE_SUB(NOW(),INTERVAL 2 DAY));