CREATE TABLE IF NOT EXISTS supplier_proformas (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  company_id BIGINT UNSIGNED NOT NULL,
  branch_id BIGINT UNSIGNED NOT NULL,
  supplier_id BIGINT UNSIGNED NOT NULL,
  purchase_order_id BIGINT UNSIGNED NULL,
  proforma_number VARCHAR(60) NOT NULL,
  issue_date DATE NOT NULL,
  due_date DATE NULL,
  currency_code CHAR(3) NOT NULL DEFAULT 'USD',
  exchange_rate_value DECIMAL(18,6) NOT NULL DEFAULT 1.000000,
  subtotal_net_usd DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
  subtotal_net_ars DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
  total_usd DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
  total_ars DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
  status VARCHAR(30) NOT NULL DEFAULT 'REGISTERED',
  notes TEXT NULL,
  created_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_supplier_proformas_company (company_id),
  KEY idx_supplier_proformas_supplier (supplier_id),
  KEY idx_supplier_proformas_status (status),
  KEY idx_supplier_proformas_branch (branch_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS supplier_proforma_items (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  supplier_proforma_id BIGINT UNSIGNED NOT NULL,
  purchase_order_item_id BIGINT UNSIGNED NULL,
  product_id BIGINT UNSIGNED NOT NULL,
  description VARCHAR(255) NOT NULL,
  quantity DECIMAL(18,6) NOT NULL,
  quantity_received DECIMAL(18,6) NOT NULL DEFAULT 0.000000,
  unit_cost_usd DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
  unit_cost_ars DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
  line_total_usd DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
  line_total_ars DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_supplier_proforma_items_proforma (supplier_proforma_id),
  KEY idx_supplier_proforma_items_product (product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE supplier_payment_allocations
  ADD COLUMN IF NOT EXISTS supplier_proforma_id BIGINT UNSIGNED NULL AFTER supplier_invoice_id,
  ADD KEY IF NOT EXISTS idx_spa_supplier_proforma (supplier_proforma_id);

ALTER TABLE supplier_account_movements
  ADD COLUMN IF NOT EXISTS supplier_proforma_id BIGINT UNSIGNED NULL AFTER supplier_invoice_id,
  ADD KEY IF NOT EXISTS idx_sam_supplier_proforma (supplier_proforma_id);
