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

-- FastAPI backend: users table
CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(64) NOT NULL UNIQUE,
  email VARCHAR(255) DEFAULT NULL UNIQUE,
  password_hash VARCHAR(255) NULL,
  role ENUM('customer','partner','staff','admin','office') NOT NULL DEFAULT 'customer',
  customer_id INT DEFAULT NULL,
  partner_id INT DEFAULT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  last_login DATETIME DEFAULT NULL,
  password_reset_token VARCHAR(100) DEFAULT NULL,
  password_reset_expires DATETIME DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Core: Messages tables
CREATE TABLE IF NOT EXISTS messages (
  id INT AUTO_INCREMENT PRIMARY KEY,
  message_unique_number VARCHAR(50) UNIQUE,
  customer_id INT NOT NULL,
  customer_name VARCHAR(255) NOT NULL,
  message_title VARCHAR(255) NOT NULL,
  message_status ENUM('Open','Awaiting Reply','Closed','Archived') NOT NULL DEFAULT 'Open',
  opened_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  last_message_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  assigned_staff_id INT DEFAULT NULL,
  assigned_staff_name VARCHAR(255) DEFAULT NULL,
  related_quote_id INT DEFAULT NULL,
  related_quote_number VARCHAR(50) DEFAULT NULL,
  message_priority ENUM('Normal','Urgent','Follow-Up') NOT NULL DEFAULT 'Normal',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_messages_customer_id (customer_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Ensure any legacy triggers are removed; use generated column instead
DROP TRIGGER IF EXISTS messages_set_unique_number;

-- Conversation log for each message (thread)
CREATE TABLE IF NOT EXISTS message_entries (
  id INT AUTO_INCREMENT PRIMARY KEY,
  message_id INT NOT NULL,
  sender_role ENUM('Customer','Staff') NOT NULL,
  sender_name VARCHAR(255) NOT NULL,
  message_header VARCHAR(255) DEFAULT NULL,
  message_content TEXT NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_message_entries_message_id (message_id),
  CONSTRAINT fk_message_entries_message
    FOREIGN KEY (message_id) REFERENCES messages(id)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Message attachments (pdf/images)
CREATE TABLE IF NOT EXISTS message_attachments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  entry_id INT NOT NULL,
  file_url VARCHAR(500) NOT NULL,
  file_name VARCHAR(255) DEFAULT NULL,
  mime_type VARCHAR(100) DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_message_attachments_entry_id (entry_id),
  CONSTRAINT fk_message_attachments_entry
    FOREIGN KEY (entry_id) REFERENCES message_entries(id)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Core: Customers table
CREATE TABLE IF NOT EXISTS customers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  customer_unique_number VARCHAR(50) GENERATED ALWAYS AS (CONCAT('CUST-', LPAD(id, 6, '0'))) STORED UNIQUE,
  status ENUM('Active','Inactive','On-Hold') NOT NULL DEFAULT 'Active',
  customer_type ENUM('Export','Local') NOT NULL,
  first_names VARCHAR(255) NOT NULL,
  surname VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL UNIQUE,
  contact_number VARCHAR(50) NOT NULL,
  whatsapp_number VARCHAR(50) DEFAULT NULL,
  physical_country VARCHAR(100) DEFAULT NULL,
  physical_street VARCHAR(255) DEFAULT NULL,
  physical_province_state VARCHAR(100) DEFAULT NULL,
  physical_city VARCHAR(100) DEFAULT NULL,
  physical_postal_code VARCHAR(20) DEFAULT NULL,
  ship_country VARCHAR(100) DEFAULT NULL,
  ship_street VARCHAR(255) DEFAULT NULL,
  ship_province_state VARCHAR(100) DEFAULT NULL,
  ship_city VARCHAR(100) DEFAULT NULL,
  ship_postal_code VARCHAR(20) DEFAULT NULL,
  referred_by VARCHAR(255) DEFAULT NULL,
  preferred_currency ENUM('USD','EUR','BWP','ZAR') DEFAULT 'USD',
  citizenship ENUM('South African Citizen','Foreign National') NOT NULL,
  rsa_id VARCHAR(50) DEFAULT NULL,
  passport_number VARCHAR(50) DEFAULT NULL,
  documents TEXT DEFAULT NULL,
  office_notes TEXT DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT chk_citizenship_id CHECK (
    (citizenship = 'South African Citizen' AND rsa_id IS NOT NULL AND passport_number IS NULL)
    OR (citizenship = 'Foreign National' AND passport_number IS NOT NULL AND rsa_id IS NULL)
  )
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Remove legacy trigger if present
DROP TRIGGER IF EXISTS customers_set_unique_number;

-- (Removed duplicate legacy customers definition)

-- FastAPI backend: customer_orders table
CREATE TABLE IF NOT EXISTS customer_orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  orderUniqueNumber VARCHAR(50) NOT NULL UNIQUE,
  relatedQuoteNumber VARCHAR(50),
  relatedInvoiceNumber VARCHAR(50),
  orderType ENUM('Export', 'Local') NOT NULL,
  processingType ENUM('Fully Processed', 'Dip & Pack', 'Fully Processed + D&P', 'Other') NOT NULL,
  customerName VARCHAR(255) NOT NULL,
  orderDelivery ENUM('Shipping Partner', 'Customer Collect') NOT NULL,
  depositReceived VARCHAR(100),
  instructionsFinalised ENUM('Yes', 'No') NOT NULL,
  orderPaidInFull VARCHAR(100),
  officeNotes TEXT,
  date DATE NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;



-- Core: Partners table
CREATE TABLE IF NOT EXISTS partners (
  id INT AUTO_INCREMENT PRIMARY KEY,
  partner_unique_number VARCHAR(50) GENERATED ALWAYS AS (CONCAT('PART-', LPAD(id, 6, '0'))) STORED UNIQUE,
  status ENUM('Active','Inactive','On-Hold') NOT NULL DEFAULT 'Active',
  partner_type ENUM('Professional Hunter','Outfitter','Professional Hunter & Outfitter','Shipping') NOT NULL,
  -- Company details
  company_name VARCHAR(255) NOT NULL,
  company_vat VARCHAR(100) DEFAULT NULL,
  company_email VARCHAR(255) NOT NULL,
  company_contact_number VARCHAR(50) NOT NULL,
  company_country VARCHAR(100) NOT NULL,
  company_street VARCHAR(255) NOT NULL,
  company_province VARCHAR(100) NOT NULL,
  company_city VARCHAR(100) NOT NULL,
  company_postal_code VARCHAR(20) NOT NULL,
  -- Personal details
  first_name VARCHAR(100) NOT NULL,
  surname VARCHAR(100) NOT NULL,
  email VARCHAR(255) NOT NULL,
  contact_number VARCHAR(50) NOT NULL,
  -- Outfitter specific
  hunting_outfitter_id VARCHAR(100) DEFAULT NULL,
  comms_local_percent DECIMAL(5,2) DEFAULT NULL,
  comms_export_percent DECIMAL(5,2) DEFAULT NULL,
  outfitter_documents TEXT DEFAULT NULL,
  -- Professional Hunter specific
  professional_hunter_id VARCHAR(100) DEFAULT NULL,
  professional_hunter_permit_type ENUM('Standard','Dangerous Game','Standard & Dangerous Game') DEFAULT NULL,
  professional_hunter_permit_issue_date DATE DEFAULT NULL,
  professional_hunter_permit_expiry_date DATE DEFAULT NULL,
  professional_hunter_documents TEXT DEFAULT NULL,
  -- Office notes
  office_notes TEXT DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Remove legacy trigger if present
DROP TRIGGER IF EXISTS partners_set_unique_number;


-- Core: Quotes tables
CREATE TABLE IF NOT EXISTS quotes (
  id INT AUTO_INCREMENT PRIMARY KEY,
  quote_unique_number VARCHAR(50) GENERATED ALWAYS AS (CONCAT('QUO-', LPAD(id, 6, '0'))) STORED UNIQUE,
  partner_id INT DEFAULT NULL,
  partner_name VARCHAR(255) DEFAULT NULL,
  order_no VARCHAR(50) NOT NULL,
  quote_date DATE NOT NULL,
  order_type ENUM('Local','Export') NOT NULL,
  -- Customer snapshot
  customer_id INT DEFAULT NULL,
  customer_first_names VARCHAR(255) NOT NULL,
  customer_surname VARCHAR(255) NOT NULL,
  customer_email VARCHAR(255) NOT NULL,
  customer_contact_number VARCHAR(50) NOT NULL,
  customer_country VARCHAR(100) NOT NULL,
  customer_street VARCHAR(255) NOT NULL,
  customer_province_state VARCHAR(100) DEFAULT NULL,
  customer_city VARCHAR(100) NOT NULL,
  customer_postal_code VARCHAR(20) NOT NULL,
  -- Company snapshot
  company_name VARCHAR(255) NOT NULL,
  company_physical_address VARCHAR(255) NOT NULL,
  company_phone_number VARCHAR(50) NOT NULL,
  company_email VARCHAR(255) NOT NULL,
  company_vat_number VARCHAR(100) DEFAULT NULL,
  company_registration_number VARCHAR(100) DEFAULT NULL,
  company_logo_url VARCHAR(500) DEFAULT NULL,
  -- Quote status and currency
  quote_status ENUM(
    'Draft - In Progress',
    'Draft - For Customer Review',
    'Draft - Customer Requested Changes',
    'Draft - Pending Office Approval',
    'Rejected',
    'Accepted - Deposit Due',
    'Accepted - Deposit Received',
    'Accepted - Converted to Order'
  ) NOT NULL DEFAULT 'Draft - In Progress',
  quote_currency ENUM('USD','EUR','BWP','ZAR') NOT NULL DEFAULT 'USD',
  -- Totals
  sub_total DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  vat_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  total_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  deposit_percent DECIMAL(5,2) DEFAULT NULL,
  deposit_amount_due DECIMAL(12,2) DEFAULT NULL,
  -- Terms and Payment
  terms_and_conditions TEXT DEFAULT NULL,
  payment_details TEXT DEFAULT NULL,
  -- Office notes
  office_notes TEXT DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Remove legacy trigger if present
DROP TRIGGER IF EXISTS quotes_set_unique_number;

-- Quote line items
CREATE TABLE IF NOT EXISTS quote_line_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  quote_id INT NOT NULL,
  item_quantity INT NOT NULL DEFAULT 1,
  item_type ENUM('Animal','Bird','Administrative') NOT NULL,
  species VARCHAR(255) DEFAULT NULL,
  unit_price DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  total_price DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  detailed_instructions TEXT DEFAULT NULL,
  line_item_status ENUM('Draft','Approved','Cancelled') NOT NULL DEFAULT 'Draft',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_quote_id (quote_id),
  CONSTRAINT fk_quote_line_items_quote
    FOREIGN KEY (quote_id) REFERENCES quotes(id)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Line item processing selections (multi-select support)
CREATE TABLE IF NOT EXISTS quote_line_item_processes (
  id INT AUTO_INCREMENT PRIMARY KEY,
  line_item_id INT NOT NULL,
  processing_type ENUM(
    'Dip & Pack','Skins','Full Mount','Shoulder Mount','Wall Pedestal Shoulder Mount',
    'Pedestal Shoulder Mount','Skull Mount','Rug Mount','Africa Map Mount','Half Mount',
    'Standalone Bases','Manufactured Product'
  ) NOT NULL,
  processing_subtype VARCHAR(255) DEFAULT NULL,
  INDEX idx_line_item_id (line_item_id),
  CONSTRAINT fk_qli_process_item
    FOREIGN KEY (line_item_id) REFERENCES quote_line_items(id)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Quote documents/images
CREATE TABLE IF NOT EXISTS quote_documents (
  id INT AUTO_INCREMENT PRIMARY KEY,
  quote_id INT NOT NULL,
  file_url VARCHAR(500) NOT NULL,
  file_name VARCHAR(255) DEFAULT NULL,
  mime_type VARCHAR(100) DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_quote_doc_quote_id (quote_id),
  CONSTRAINT fk_quote_documents_quote
    FOREIGN KEY (quote_id) REFERENCES quotes(id)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- HS Codes for quotes (multi-select)
CREATE TABLE IF NOT EXISTS quote_hs_codes (
  id INT AUTO_INCREMENT PRIMARY KEY,
  quote_id INT NOT NULL,
  hs_code VARCHAR(50) NOT NULL,
  INDEX idx_quote_hs_quote_id (quote_id),
  CONSTRAINT fk_quote_hs_codes_quote
    FOREIGN KEY (quote_id) REFERENCES quotes(id)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Core: Orders table
CREATE TABLE IF NOT EXISTS orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  order_unique_number VARCHAR(50) GENERATED ALWAYS AS (CONCAT('ORD-', LPAD(id, 6, '0'))) STORED UNIQUE,
  related_quote_id INT DEFAULT NULL,
  related_quote_number VARCHAR(50) DEFAULT NULL,
  related_invoice_id INT DEFAULT NULL,
  related_invoice_number VARCHAR(50) DEFAULT NULL,
  order_type ENUM('Export','Local') NOT NULL,
  processing_type ENUM('Fully Processed','Dip & Pack','Fully Processed + D&P','Other') NOT NULL,
  customer_id INT NOT NULL,
  customer_name VARCHAR(255) NOT NULL,
  order_delivery ENUM('Shipping Partner','Customer Collect') NOT NULL,
  deposit_received VARCHAR(100) DEFAULT 'No',
  instructions_finalised ENUM('Yes','No') NOT NULL DEFAULT 'No',
  order_paid_in_full VARCHAR(100) DEFAULT 'No',
  office_notes TEXT DEFAULT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_orders_customer_id (customer_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Remove legacy trigger if present
DROP TRIGGER IF EXISTS orders_set_unique_number;

-- Core: Invoices tables
CREATE TABLE IF NOT EXISTS invoices (
  id INT AUTO_INCREMENT PRIMARY KEY,
  invoice_unique_number VARCHAR(50) GENERATED ALWAYS AS (CONCAT('INV-', LPAD(id, 6, '0'))) STORED UNIQUE,
  related_quote_id INT DEFAULT NULL,
  related_quote_number VARCHAR(50) DEFAULT NULL,
  partner_id INT DEFAULT NULL,
  partner_name VARCHAR(255) DEFAULT NULL,
  invoice_date DATE NOT NULL,
  order_no VARCHAR(50) DEFAULT NULL,
  order_type ENUM('Local','Export') NOT NULL,
  -- Customer snapshot
  customer_id INT DEFAULT NULL,
  customer_first_names VARCHAR(255) NOT NULL,
  customer_surname VARCHAR(255) NOT NULL,
  customer_email VARCHAR(255) DEFAULT NULL,
  customer_contact_number VARCHAR(50) DEFAULT NULL,
  customer_country VARCHAR(100) NOT NULL,
  customer_street VARCHAR(255) DEFAULT NULL,
  customer_province_state VARCHAR(100) DEFAULT NULL,
  customer_city VARCHAR(100) DEFAULT NULL,
  customer_postal_code VARCHAR(20) DEFAULT NULL,
  -- Company snapshot
  company_name VARCHAR(255) NOT NULL,
  company_physical_address VARCHAR(255) NOT NULL,
  company_phone_number VARCHAR(50) NOT NULL,
  company_email VARCHAR(255) NOT NULL,
  company_vat_number VARCHAR(100) DEFAULT NULL,
  company_registration_number VARCHAR(100) DEFAULT NULL,
  company_logo_url VARCHAR(500) DEFAULT NULL,
  -- Invoice details
  invoice_type ENUM('Pro-Forma Invoice','Tax Invoice') NOT NULL,
  payment_type ENUM('Deposit','Installment','Final') NOT NULL,
  invoice_status ENUM('Draft – In Progress','Issued','Partial Payment Received','Paid in Full','Withdrawn') NOT NULL DEFAULT 'Draft – In Progress',
  invoice_currency ENUM('USD','EUR','BWP','ZAR') NOT NULL,
  -- Totals
  sub_total DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  vat_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  total_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  deposit_percent DECIMAL(5,2) DEFAULT NULL,
  deposit_amount_due DECIMAL(12,2) DEFAULT NULL,
  -- T&Cs and payment details
  terms_and_conditions TEXT DEFAULT NULL,
  payment_details TEXT DEFAULT NULL,
  office_notes TEXT DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Remove legacy trigger if present
DROP TRIGGER IF EXISTS invoices_set_unique_number;

-- Invoice line items (snapshot of quote items)
CREATE TABLE IF NOT EXISTS invoice_line_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  invoice_id INT NOT NULL,
  item_quantity INT NOT NULL DEFAULT 1,
  item_type ENUM('Animal','Bird','Administrative') NOT NULL,
  species VARCHAR(255) DEFAULT NULL,
  processing_type ENUM(
    'Dip & Pack','Skins','Full Mount','Shoulder Mount','Wall Pedestal Shoulder Mount',
    'Pedestal Shoulder Mount','Skull Mount','Rug Mount','Africa Map Mount','Half Mount',
    'Standalone Bases','Manufactured Product'
  ) DEFAULT NULL,
  processing_subtype VARCHAR(255) DEFAULT NULL,
  unit_price DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  total_price DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_invoice_id (invoice_id),
  CONSTRAINT fk_invoice_line_items_invoice
    FOREIGN KEY (invoice_id) REFERENCES invoices(id)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- HS Codes on invoice (can update from quote)
CREATE TABLE IF NOT EXISTS invoice_hs_codes (
  id INT AUTO_INCREMENT PRIMARY KEY,
  invoice_id INT NOT NULL,
  hs_code VARCHAR(50) NOT NULL,
  INDEX idx_invoice_hs_invoice_id (invoice_id),
  CONSTRAINT fk_invoice_hs_codes_invoice
    FOREIGN KEY (invoice_id) REFERENCES invoices(id)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Payments captured on invoice
CREATE TABLE IF NOT EXISTS invoice_payments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  invoice_id INT NOT NULL,
  payment_date DATE NOT NULL,
  amount DECIMAL(12,2) NOT NULL,
  notes VARCHAR(255) DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_invoice_payments_invoice_id (invoice_id),
  CONSTRAINT fk_invoice_payments_invoice
    FOREIGN KEY (invoice_id) REFERENCES invoices(id)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


