-- Fix 1: Add last_payment_date to billing_info for period-accurate paid totals
ALTER TABLE billing_info
  ADD COLUMN last_payment_date DATE NULL AFTER payment_due_date;

-- Fix 2: Store billing data for accounts not in customer_accounts
CREATE TABLE IF NOT EXISTS unmatched_billing_data (
  id INT AUTO_INCREMENT PRIMARY KEY,
  billing_account_no VARCHAR(20) NOT NULL,
  customer_account_no VARCHAR(20),
  customer_name VARCHAR(255),
  phone_no VARCHAR(30),
  mobile_no VARCHAR(20),
  email VARCHAR(255),
  month INT NOT NULL,
  year INT NOT NULL,
  billing_period VARCHAR(7) NOT NULL,
  invoice_amount DECIMAL(10,2) DEFAULT 0,
  due_amount DECIMAL(10,2) DEFAULT 0,
  last_paid_amount DECIMAL(10,2) DEFAULT 0,
  last_payment_date DATE NULL,
  outstanding_amount DECIMAL(10,2) DEFAULT 0,
  payment_status VARCHAR(50) DEFAULT 'Unpaid',
  invoice_no VARCHAR(50),
  invoice_date DATE NULL,
  payment_due_date DATE NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_unmatched (billing_account_no, billing_period)
);
