<?php
// Database connection for MySQL (phpMyAdmin / XAMPP)
// DB: ninja

function db() {
  static $pdo = null;
  if ($pdo) return $pdo;
  $host = '127.0.0.1';
  $db   = 'ninja';
  $user = 'root';
  $pass = '';
  $charset = 'utf8mb4';
  $dsn = "mysql:host=$host;dbname=$db;charset=$charset";
  $options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
  ];
  $pdo = new PDO($dsn, $user, $pass, $options);
  // Create agents table if not exists
  $pdo->exec("
    CREATE TABLE IF NOT EXISTS agents (
      id INT AUTO_INCREMENT PRIMARY KEY,
      agent_name VARCHAR(255) NOT NULL,
      email VARCHAR(255),
      contact_number VARCHAR(64),
      second_phone VARCHAR(64),
      business_name VARCHAR(255),
      country VARCHAR(128),
      address VARCHAR(512),
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  ");
  $pdo->exec("
    CREATE TABLE IF NOT EXISTS balance_transactions (
      id INT AUTO_INCREMENT PRIMARY KEY,
      transaction_id VARCHAR(64) NOT NULL,
      amount DECIMAL(12,2) NOT NULL,
      details VARCHAR(512),
      tx_datetime DATETIME NOT NULL,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      INDEX idx_tx_datetime (tx_datetime),
      INDEX idx_transaction_id (transaction_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  ");
  $pdo->exec("
    CREATE TABLE IF NOT EXISTS agent_balance_transactions (
      id INT AUTO_INCREMENT PRIMARY KEY,
      transaction_id VARCHAR(64) NOT NULL,
      agent_id INT NOT NULL,
      amount DECIMAL(12,2) NOT NULL,
      details VARCHAR(512),
      tx_datetime DATETIME NOT NULL,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      INDEX idx_agent_id (agent_id),
      INDEX idx_tx_datetime (tx_datetime),
      CONSTRAINT fk_agent_balance_agent FOREIGN KEY (agent_id) REFERENCES agents(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  ");
  $pdo->exec("
    CREATE TABLE IF NOT EXISTS inventory_items (
      id INT AUTO_INCREMENT PRIMARY KEY,
      item_no VARCHAR(128) NOT NULL,
      code_number VARCHAR(128) NOT NULL,
      category VARCHAR(64) NOT NULL,
      opening_qty DECIMAL(14,3) NOT NULL,
      opening_rate DECIMAL(14,2) NOT NULL,
      cost DECIMAL(14,2) NOT NULL,
      weight DECIMAL(14,3) NOT NULL,
      unit VARCHAR(32) NOT NULL,
      id_val DECIMAL(14,3) NULL,
      od DECIMAL(14,3) NULL,
      w DECIMAL(14,3) NULL,
      id1 DECIMAL(14,3) NULL,
      od2 DECIMAL(14,3) NULL,
      w2 DECIMAL(14,3) NULL,
      notes TEXT NULL,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME NULL,
      INDEX idx_item_no (item_no),
      INDEX idx_code_number (code_number)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  ");
  $pdo->exec("
    CREATE TABLE IF NOT EXISTS suppliers (
      id INT AUTO_INCREMENT PRIMARY KEY,
      supplier_name VARCHAR(255) NOT NULL,
      last_name VARCHAR(255),
      email VARCHAR(255),
      address VARCHAR(512),
      country VARCHAR(128),
      contact_number VARCHAR(64),
      wechat_id VARCHAR(128),
      business_name VARCHAR(255),
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  ");
  $pdo->exec("
    CREATE TABLE IF NOT EXISTS shippers (
      id INT AUTO_INCREMENT PRIMARY KEY,
      shipper_name VARCHAR(255) NOT NULL,
      email VARCHAR(255),
      address VARCHAR(512),
      country VARCHAR(128),
      contact_number VARCHAR(64),
      second_phone VARCHAR(64),
      business_name VARCHAR(255),
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  ");
  try {
    $pdo->exec("ALTER TABLE shippers ADD COLUMN is_deleted TINYINT(1) NOT NULL DEFAULT 0");
  } catch (Throwable $e) {
    // ignore if column already exists
  }
  $pdo->exec("
    CREATE TABLE IF NOT EXISTS tt_transactions (
      id INT AUTO_INCREMENT PRIMARY KEY,
      tt_id VARCHAR(64) NOT NULL,
      agent_id INT NOT NULL,
      currency VARCHAR(16) NOT NULL,
      amount_pkr DECIMAL(14,2) NOT NULL,
      currency_bought DECIMAL(14,2) NOT NULL,
      exchange_rate DECIMAL(14,4) NOT NULL,
      details TEXT,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      INDEX idx_agent (agent_id),
      INDEX idx_tt_id (tt_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  ");
  $pdo->exec("
    CREATE TABLE IF NOT EXISTS purchase_demands (
      id INT AUTO_INCREMENT PRIMARY KEY,
      demand_id VARCHAR(64) NOT NULL,
      supplier_id INT NOT NULL,
      item_id INT NOT NULL,
      priority VARCHAR(32) NOT NULL,
      notes TEXT,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      INDEX idx_supplier (supplier_id),
      INDEX idx_item (item_id),
      INDEX idx_demand_id (demand_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  ");
  $pdo->exec("
    CREATE TABLE IF NOT EXISTS invoices (
      id INT AUTO_INCREMENT PRIMARY KEY,
      invoice_no VARCHAR(64) NOT NULL,
      supplier_id INT NOT NULL,
      batch_no VARCHAR(64),
      maturity_date DATE,
      currency VARCHAR(16),
      exchange_rate DECIMAL(14,4) NOT NULL DEFAULT 1,
      current_expense DECIMAL(14,2) DEFAULT 0,
      discount DECIMAL(14,2) DEFAULT 0,
      image_path VARCHAR(255),
      notes TEXT,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      INDEX idx_invoice_no (invoice_no),
      INDEX idx_supplier (supplier_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  ");
  
  // Attempt to add image_path column if it doesn't exist (for existing tables)
  try {
      $pdo->exec("ALTER TABLE invoices ADD COLUMN image_path VARCHAR(255) AFTER discount");
  } catch (Exception $e) {
      // Column likely already exists
  }

  $pdo->exec("
    CREATE TABLE IF NOT EXISTS invoice_items (
      id INT AUTO_INCREMENT PRIMARY KEY,
      invoice_id INT NOT NULL,
      item_id INT NOT NULL,
      quantity DECIMAL(14,3) NOT NULL,
      unit_price DECIMAL(14,2) NOT NULL,
      weight DECIMAL(14,3) DEFAULT 0,
      total_amount DECIMAL(14,2) NOT NULL,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      INDEX idx_invoice (invoice_id),
      CONSTRAINT fk_invoice_items_invoice FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  ");
  $pdo->exec("
    CREATE TABLE IF NOT EXISTS shipments (
      id INT AUTO_INCREMENT PRIMARY KEY,
      shipment_id VARCHAR(64) NOT NULL,
      enter_date DATE NOT NULL,
      arrival_date DATE NOT NULL,
      container_no VARCHAR(64) NOT NULL,
      container_weight DECIMAL(14,3) NOT NULL,
      shipper_id INT NOT NULL,
      mode VARCHAR(32) NOT NULL,
      pcs_ctns_pallets INT NOT NULL,
      via VARCHAR(128) NOT NULL,
      invoice_id INT NOT NULL,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      INDEX idx_shipment_id (shipment_id),
      INDEX idx_shipper (shipper_id),
      INDEX idx_invoice (invoice_id),
      CONSTRAINT fk_shipments_shipper FOREIGN KEY (shipper_id) REFERENCES shippers(id) ON DELETE RESTRICT,
      CONSTRAINT fk_shipments_invoice FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE RESTRICT
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  ");
  $pdo->exec("
    CREATE TABLE IF NOT EXISTS shipment_modes (
      id INT AUTO_INCREMENT PRIMARY KEY,
      name VARCHAR(64) NOT NULL UNIQUE,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  ");
  $pdo->exec("
    CREATE TABLE IF NOT EXISTS shipment_vias (
      id INT AUTO_INCREMENT PRIMARY KEY,
      name VARCHAR(128) NOT NULL UNIQUE,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  ");
  $pdo->exec("
    CREATE TABLE IF NOT EXISTS currencies (
      id INT AUTO_INCREMENT PRIMARY KEY,
      code VARCHAR(16) NOT NULL UNIQUE,
      name VARCHAR(64) NULL,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  ");
  return $pdo;
}
