-- ============================================================
-- Schema Update v10
-- Run this AFTER schema_update_v9.sql.
-- Accounts Payable (supplier bills + payments) and Accounts Receivable
-- (customer invoices + receipts). Each bill/invoice and each payment/receipt
-- automatically creates a matching journal entry in the General Ledger.
-- ============================================================

CREATE TABLE IF NOT EXISTS ap_bills (
  id INT AUTO_INCREMENT PRIMARY KEY,
  bill_number VARCHAR(30) UNIQUE NOT NULL,
  supplier_id INT NOT NULL,
  po_id INT NULL,
  expense_account_id INT NOT NULL, -- what was purchased (Inventory, an Expense account, etc.)
  bill_date DATE NOT NULL,
  due_date DATE NULL,
  amount DECIMAL(14,2) NOT NULL,
  amount_paid DECIMAL(14,2) NOT NULL DEFAULT 0,
  status ENUM('open','partially_paid','paid','void') DEFAULT 'open',
  notes VARCHAR(255) NULL,
  journal_entry_id INT NULL,
  created_by INT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (supplier_id) REFERENCES suppliers(id),
  FOREIGN KEY (po_id) REFERENCES purchase_orders(id) ON DELETE SET NULL,
  FOREIGN KEY (expense_account_id) REFERENCES chart_of_accounts(id),
  FOREIGN KEY (journal_entry_id) REFERENCES journal_entries(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS ap_payments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  bill_id INT NOT NULL,
  payment_date DATE NOT NULL,
  amount DECIMAL(14,2) NOT NULL,
  cash_account_id INT NOT NULL, -- which Cash/Bank account paid from
  payment_method VARCHAR(50) NULL,
  reference VARCHAR(100) NULL,
  notes VARCHAR(255) NULL,
  journal_entry_id INT NULL,
  created_by INT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (bill_id) REFERENCES ap_bills(id) ON DELETE CASCADE,
  FOREIGN KEY (cash_account_id) REFERENCES chart_of_accounts(id),
  FOREIGN KEY (journal_entry_id) REFERENCES journal_entries(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS ar_invoices (
  id INT AUTO_INCREMENT PRIMARY KEY,
  invoice_number VARCHAR(30) UNIQUE NOT NULL,
  customer_name VARCHAR(150) NOT NULL,
  revenue_account_id INT NOT NULL, -- what was earned (Room Income, Other Income, etc.)
  invoice_date DATE NOT NULL,
  due_date DATE NULL,
  amount DECIMAL(14,2) NOT NULL,
  amount_received DECIMAL(14,2) NOT NULL DEFAULT 0,
  status ENUM('open','partially_paid','paid','void') DEFAULT 'open',
  notes VARCHAR(255) NULL,
  journal_entry_id INT NULL,
  created_by INT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (revenue_account_id) REFERENCES chart_of_accounts(id),
  FOREIGN KEY (journal_entry_id) REFERENCES journal_entries(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS ar_receipts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  invoice_id INT NOT NULL,
  receipt_date DATE NOT NULL,
  amount DECIMAL(14,2) NOT NULL,
  cash_account_id INT NOT NULL,
  payment_method VARCHAR(50) NULL,
  reference VARCHAR(100) NULL,
  notes VARCHAR(255) NULL,
  journal_entry_id INT NULL,
  created_by INT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (invoice_id) REFERENCES ar_invoices(id) ON DELETE CASCADE,
  FOREIGN KEY (cash_account_id) REFERENCES chart_of_accounts(id),
  FOREIGN KEY (journal_entry_id) REFERENCES journal_entries(id)
) ENGINE=InnoDB;
