CREATE DATABASE IF NOT EXISTS oz_movers;
USE oz_movers;

CREATE TABLE IF NOT EXISTS admins (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  password VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT IGNORE INTO admins (email, password) VALUES
  ('admin@ozmoverslogistics.com.au', '$2a$10$dummy_hash_replace_with_bcrypt');

CREATE TABLE IF NOT EXISTS shipments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tracking_id VARCHAR(50) NOT NULL UNIQUE,
  status VARCHAR(50) DEFAULT 'Pending',

  sender_name VARCHAR(255),
  sender_email VARCHAR(255),
  sender_phone VARCHAR(100),
  sender_address TEXT,

  receiver_name VARCHAR(255),
  receiver_email VARCHAR(255),
  receiver_phone VARCHAR(100),
  receiver_address TEXT,

  origin VARCHAR(255),
  destination VARCHAR(255),
  weight VARCHAR(100),
  dimensions VARCHAR(255),
  freight_type VARCHAR(100),

  estimated_delivery DATE,
  shipping_cost DECIMAL(10,2) DEFAULT 0,
  insurance_cost DECIMAL(10,2) DEFAULT 0,
  total_cost DECIMAL(10,2) DEFAULT 0,
  payment_status VARCHAR(50) DEFAULT 'Unpaid',

  contact_email VARCHAR(255),
  contact_phone VARCHAR(100),

  notes TEXT,
  images JSON,

  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS tracking_updates (
  id INT AUTO_INCREMENT PRIMARY KEY,
  shipment_id INT NOT NULL,
  location VARCHAR(255),
  description VARCHAR(500),
  status VARCHAR(100),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (shipment_id) REFERENCES shipments(id) ON DELETE CASCADE
);