-- ============================================================
--  CareBill — multi-currency and export invoicing.
--  Existing database:  mysql carebill < sql/currency.sql
--  (schema.sql already carries this for fresh installs)
--
--  Every document keeps two sets of figures: the currency it was billed in, and
--  the rupee equivalent at the rate on the invoice date. GST records, ageing and
--  every report run on the rupee column, because adding dollars to rupees in an
--  ageing total is how a collections system quietly starts lying.
-- ============================================================

CREATE TABLE currencies (
  code            CHAR(3) PRIMARY KEY,
  name            VARCHAR(60) NOT NULL,
  symbol          VARCHAR(8),
  decimals        TINYINT DEFAULT 2,
  status          ENUM('active','inactive') DEFAULT 'active'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO currencies (code,name,symbol,decimals) VALUES
 ('INR','Indian Rupee','Rs.',2),
 ('USD','US Dollar','$',2),
 ('EUR','Euro','EUR',2),
 ('GBP','Pound Sterling','GBP',2),
 ('AED','UAE Dirham','AED',2),
 ('BHD','Bahraini Dinar','BHD',3),
 ('SAR','Saudi Riyal','SAR',2),
 ('QAR','Qatari Riyal','QAR',2),
 ('OMR','Omani Rial','OMR',3),
 ('KES','Kenyan Shilling','KSh',2),
 ('UGX','Ugandan Shilling','USh',0),
 ('TZS','Tanzanian Shilling','TSh',2),
 ('NGN','Nigerian Naira','NGN',2),
 ('XOF','West African Franc','CFA',0),
 ('ZAR','South African Rand','R',2),
 ('LKR','Sri Lankan Rupee','LKR',2),
 ('NPR','Nepalese Rupee','NPR',2),
 ('BDT','Bangladeshi Taka','BDT',2),
 ('MUR','Mauritian Rupee','MUR',2),
 ('SGD','Singapore Dollar','S$',2),
 ('AUD','Australian Dollar','A$',2)
ON DUPLICATE KEY UPDATE name=VALUES(name);

-- rate history. For GST the rate that matters is the one notified for the
-- invoice date, not today's market rate, so rates are dated and kept.
CREATE TABLE fx_rates (
  id              BIGINT AUTO_INCREMENT PRIMARY KEY,
  currency        CHAR(3) NOT NULL,
  rate_date       DATE NOT NULL,
  rate            DECIMAL(16,6) NOT NULL,        -- units of INR for one unit of currency
  source          VARCHAR(40) DEFAULT 'manual',  -- manual | cbic | bank
  note            VARCHAR(160),
  created_by      INT NULL,
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_fx (currency, rate_date, source),
  INDEX ix_fx_lookup (currency, rate_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE clients
  ADD COLUMN currency CHAR(3) DEFAULT 'INR' AFTER country,
  ADD COLUMN export_type ENUM('domestic','export_lut','export_igst','sez_lut','sez_igst','deemed')
      DEFAULT 'domestic' AFTER currency;

ALTER TABLE services
  ADD COLUMN currency CHAR(3) DEFAULT 'INR' AFTER gst_rate;

ALTER TABLE invoices
  ADD COLUMN currency CHAR(3) DEFAULT 'INR' AFTER client_id,
  ADD COLUMN fx_rate DECIMAL(16,6) DEFAULT 1 AFTER currency,
  ADD COLUMN export_type ENUM('domestic','export_lut','export_igst','sez_lut','sez_igst','deemed')
      DEFAULT 'domestic' AFTER fx_rate,
  ADD COLUMN base_taxable DECIMAL(16,2) DEFAULT 0 AFTER taxable,
  ADD COLUMN base_grand_total DECIMAL(16,2) DEFAULT 0 AFTER grand_total,
  ADD COLUMN base_balance DECIMAL(16,2) DEFAULT 0 AFTER balance;

ALTER TABLE payments
  ADD COLUMN currency CHAR(3) DEFAULT 'INR' AFTER brand_id,
  ADD COLUMN fx_rate DECIMAL(16,6) DEFAULT 1 AFTER currency,
  ADD COLUMN base_amount DECIMAL(16,2) DEFAULT 0 AFTER amount;

ALTER TABLE payment_allocations
  MODIFY kind ENUM('payment','tds','writeoff','discount','bank_charge','gst_hold',
                   'credit_note','round_off','fx_diff','other') DEFAULT 'payment';

INSERT INTO settings (k,v) VALUES
 ('base_currency','INR'),
 ('fx_warn_days','7'),
 ('lut_number',''), ('lut_valid_till','')
ON DUPLICATE KEY UPDATE k=k;

DROP VIEW IF EXISTS v_outstanding;
CREATE VIEW v_outstanding AS
SELECT i.id, i.id AS invoice_id, i.brand_id, b.name AS brand_name, i.client_id, c.name AS client_name, c.location,
       c.collector_id, i.invoice_no, i.doc_type, i.invoice_date, i.due_date,
       i.period_from, i.period_to, i.currency, i.fx_rate,
       i.grand_total, i.amount_paid, i.amount_adjusted, i.balance,
       i.base_grand_total, i.base_balance,
       i.status, i.reminder_stage, i.ptp_date, i.dispute_flag,
       DATEDIFF(CURDATE(), i.due_date) AS days_overdue,
       CASE WHEN i.balance <= 0 THEN 'settled'
            WHEN DATEDIFF(CURDATE(), i.due_date) <= 0  THEN 'current'
            WHEN DATEDIFF(CURDATE(), i.due_date) <= 30 THEN '1-30'
            WHEN DATEDIFF(CURDATE(), i.due_date) <= 60 THEN '31-60'
            WHEN DATEDIFF(CURDATE(), i.due_date) <= 90 THEN '61-90'
            ELSE '90+' END AS bucket
FROM invoices i
JOIN clients c ON c.id = i.client_id
JOIN brands  b ON b.id = i.brand_id
WHERE i.status IN ('issued','partly_paid') AND i.doc_type <> 'credit_note';