SET NAMES utf8mb4;
START TRANSACTION;

ALTER TABLE finance_ledger_entries
  MODIFY meeting_id BIGINT UNSIGNED NULL,
  MODIFY booking_id BIGINT UNSIGNED NULL,
  ADD person_id BIGINT UNSIGNED NULL AFTER booking_id,
  ADD member_id BIGINT UNSIGNED NULL AFTER person_id,
  ADD source_type VARCHAR(40) NULL AFTER source,
  ADD source_id BIGINT UNSIGNED NULL AFTER source_type,
  ADD KEY idx_finance_person(unit_id,person_id,effective_at,id),
  ADD KEY idx_finance_source(unit_id,source_type,source_id),
  ADD CONSTRAINT fk_finance_person FOREIGN KEY(person_id) REFERENCES people(id),
  ADD CONSTRAINT fk_finance_member FOREIGN KEY(member_id) REFERENCES unit_members(id);

DROP TRIGGER finance_entry_no_update;
UPDATE finance_ledger_entries SET source_type='booking',source_id=booking_id WHERE source_type IS NULL AND booking_id IS NOT NULL;
CREATE TRIGGER finance_entry_no_update BEFORE UPDATE ON finance_ledger_entries FOR EACH ROW SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Finance ledger entries are immutable';

CREATE TABLE finance_funds (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, province_id BIGINT UNSIGNED NOT NULL, unit_id BIGINT UNSIGNED NOT NULL,
 name VARCHAR(160) NOT NULL, fund_type ENUM('operating','charity_designated') NOT NULL DEFAULT 'operating',
 purpose VARCHAR(500) NULL, active TINYINT(1) NOT NULL DEFAULT 1, created_by BIGINT UNSIGNED NOT NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 UNIQUE KEY uq_finance_fund(unit_id,name), KEY idx_finance_fund_scope(unit_id,fund_type,active),
 FOREIGN KEY(province_id) REFERENCES provinces(id), FOREIGN KEY(unit_id) REFERENCES units(id), FOREIGN KEY(created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE finance_cash_entries (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, province_id BIGINT UNSIGNED NOT NULL, unit_id BIGINT UNSIGNED NOT NULL,
 ledger_entry_id BIGINT UNSIGNED NULL, fund_id BIGINT UNSIGNED NULL, direction ENUM('in','out') NOT NULL,
 category ENUM('event_payment','event_refund','subscription_payment','opening_balance','donation','purchase_bill','expense_claim','charity_disbursement','other') NOT NULL,
 amount DECIMAL(12,2) NOT NULL, effective_at DATETIME NOT NULL, source_type VARCHAR(40) NULL, source_id BIGINT UNSIGNED NULL,
 created_by BIGINT UNSIGNED NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_cash_ledger(ledger_entry_id), KEY idx_cash_unit(unit_id,effective_at,id), KEY idx_cash_fund(fund_id,effective_at,id),
 FOREIGN KEY(province_id) REFERENCES provinces(id), FOREIGN KEY(unit_id) REFERENCES units(id), FOREIGN KEY(ledger_entry_id) REFERENCES finance_ledger_entries(id),
 FOREIGN KEY(fund_id) REFERENCES finance_funds(id), FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL, CHECK(amount>0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO finance_cash_entries(province_id,unit_id,ledger_entry_id,direction,category,amount,effective_at,source_type,source_id,created_by)
SELECT province_id,unit_id,id,'in','event_payment',amount,effective_at,'booking',booking_id,created_by FROM finance_ledger_entries WHERE entry_type='payment';
INSERT INTO finance_cash_entries(province_id,unit_id,ledger_entry_id,direction,category,amount,effective_at,source_type,source_id,created_by)
SELECT province_id,unit_id,id,'out','event_refund',amount,effective_at,'booking',booking_id,created_by FROM finance_ledger_entries WHERE entry_type='refund';

CREATE TABLE subscription_reminder_policies (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, unit_id BIGINT UNSIGNED NOT NULL, name VARCHAR(120) NOT NULL,
 enabled TINYINT(1) NOT NULL DEFAULT 0, first_after_days SMALLINT UNSIGNED NOT NULL DEFAULT 14, repeat_days SMALLINT UNSIGNED NOT NULL DEFAULT 30,
 maximum_reminders TINYINT UNSIGNED NOT NULL DEFAULT 3, subject VARCHAR(180) NOT NULL, message_text VARCHAR(2000) NOT NULL,
 authorised_by BIGINT UNSIGNED NULL, authorised_at DATETIME NULL, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 UNIQUE KEY uq_reminder_policy(unit_id,name), FOREIGN KEY(unit_id) REFERENCES units(id), FOREIGN KEY(authorised_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE subscription_periods (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, province_id BIGINT UNSIGNED NOT NULL, unit_id BIGINT UNSIGNED NOT NULL,
 label VARCHAR(120) NOT NULL, starts_on DATE NOT NULL, ends_on DATE NOT NULL, due_on DATE NOT NULL,
 proration_method ENUM('none','daily','monthly') NOT NULL DEFAULT 'none', status ENUM('draft','approved','closed') NOT NULL DEFAULT 'draft',
 reminder_policy_id BIGINT UNSIGNED NULL, approved_by BIGINT UNSIGNED NULL, approved_at DATETIME NULL,
 created_by BIGINT UNSIGNED NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_subscription_period(unit_id,starts_on,ends_on), KEY idx_subscription_period(unit_id,status,due_on),
 FOREIGN KEY(province_id) REFERENCES provinces(id), FOREIGN KEY(unit_id) REFERENCES units(id), FOREIGN KEY(reminder_policy_id) REFERENCES subscription_reminder_policies(id),
 FOREIGN KEY(approved_by) REFERENCES users(id) ON DELETE SET NULL, FOREIGN KEY(created_by) REFERENCES users(id), CHECK(starts_on<=ends_on)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE subscription_rates (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, period_id BIGINT UNSIGNED NOT NULL, membership_type VARCHAR(80) NOT NULL,
 amount DECIMAL(12,2) NOT NULL, allow_proration TINYINT(1) NOT NULL DEFAULT 0, approved_by BIGINT UNSIGNED NULL, approved_at DATETIME NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_subscription_rate(period_id,membership_type),
 FOREIGN KEY(period_id) REFERENCES subscription_periods(id), FOREIGN KEY(approved_by) REFERENCES users(id) ON DELETE SET NULL, CHECK(amount>=0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE finance_invoices (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, unit_id BIGINT UNSIGNED NOT NULL, invoice_number VARCHAR(60) NOT NULL,
 person_id BIGINT UNSIGNED NOT NULL, issued_on DATE NOT NULL, due_on DATE NOT NULL, total DECIMAL(12,2) NOT NULL,
 status ENUM('issued','settled','cancelled') NOT NULL DEFAULT 'issued', created_by BIGINT UNSIGNED NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_invoice_number(unit_id,invoice_number), FOREIGN KEY(unit_id) REFERENCES units(id), FOREIGN KEY(person_id) REFERENCES people(id), FOREIGN KEY(created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE subscription_charges (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, province_id BIGINT UNSIGNED NOT NULL, unit_id BIGINT UNSIGNED NOT NULL,
 period_id BIGINT UNSIGNED NOT NULL, rate_id BIGINT UNSIGNED NOT NULL, member_id BIGINT UNSIGNED NOT NULL, person_id BIGINT UNSIGNED NOT NULL,
 membership_type_snapshot VARCHAR(80) NOT NULL, membership_start_snapshot DATE NOT NULL, base_amount DECIMAL(12,2) NOT NULL,
 charged_amount DECIMAL(12,2) NOT NULL, proration_note VARCHAR(250) NULL, status ENUM('charged','exempt','reversed') NOT NULL DEFAULT 'charged',
 invoice_id BIGINT UNSIGNED NULL, ledger_entry_id BIGINT UNSIGNED NOT NULL, created_by BIGINT UNSIGNED NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_subscription_charge(period_id,person_id), KEY idx_subscription_person(unit_id,person_id,period_id),
 FOREIGN KEY(province_id) REFERENCES provinces(id), FOREIGN KEY(unit_id) REFERENCES units(id), FOREIGN KEY(period_id) REFERENCES subscription_periods(id),
 FOREIGN KEY(rate_id) REFERENCES subscription_rates(id), FOREIGN KEY(member_id) REFERENCES unit_members(id), FOREIGN KEY(person_id) REFERENCES people(id),
 FOREIGN KEY(invoice_id) REFERENCES finance_invoices(id), FOREIGN KEY(ledger_entry_id) REFERENCES finance_ledger_entries(id), FOREIGN KEY(created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE subscription_exemptions (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, charge_id BIGINT UNSIGNED NOT NULL, unit_id BIGINT UNSIGNED NOT NULL,
 amount DECIMAL(12,2) NOT NULL, reason VARCHAR(500) NOT NULL, ledger_entry_id BIGINT UNSIGNED NOT NULL,
 approved_by BIGINT UNSIGNED NOT NULL, approved_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 KEY idx_subscription_exemption(charge_id,id), FOREIGN KEY(charge_id) REFERENCES subscription_charges(id),
 FOREIGN KEY(unit_id) REFERENCES units(id), FOREIGN KEY(ledger_entry_id) REFERENCES finance_ledger_entries(id),
 FOREIGN KEY(approved_by) REFERENCES users(id), CHECK(amount>0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE subscription_reminder_log (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, charge_id BIGINT UNSIGNED NOT NULL, policy_id BIGINT UNSIGNED NOT NULL,
 sequence_number TINYINT UNSIGNED NOT NULL, queued_by BIGINT UNSIGNED NOT NULL, queued_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_subscription_reminder(charge_id,sequence_number), FOREIGN KEY(charge_id) REFERENCES subscription_charges(id),
 FOREIGN KEY(policy_id) REFERENCES subscription_reminder_policies(id), FOREIGN KEY(queued_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE finance_opening_balances (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, unit_id BIGINT UNSIGNED NOT NULL, person_id BIGINT UNSIGNED NOT NULL,
 as_of_date DATE NOT NULL, amount DECIMAL(12,2) NOT NULL, balance_kind ENUM('amount_due','credit') NOT NULL,
 ledger_entry_id BIGINT UNSIGNED NOT NULL, reason VARCHAR(500) NOT NULL, approved_by BIGINT UNSIGNED NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_opening_balance(unit_id,person_id,as_of_date), FOREIGN KEY(unit_id) REFERENCES units(id), FOREIGN KEY(person_id) REFERENCES people(id),
 FOREIGN KEY(ledger_entry_id) REFERENCES finance_ledger_entries(id), FOREIGN KEY(approved_by) REFERENCES users(id), CHECK(amount>0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE finance_expense_items (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, province_id BIGINT UNSIGNED NOT NULL, unit_id BIGINT UNSIGNED NOT NULL,
 item_type ENUM('purchase_bill','expense_claim') NOT NULL, claimant_user_id BIGINT UNSIGNED NULL, claimant_name VARCHAR(180) NULL,
 supplier VARCHAR(180) NULL, description VARCHAR(500) NOT NULL, amount DECIMAL(12,2) NOT NULL, fund_id BIGINT UNSIGNED NULL,
 state ENUM('draft','submitted','approved','paid','rejected') NOT NULL DEFAULT 'draft', finance_note_cipher LONGTEXT NULL,
 submitted_by BIGINT UNSIGNED NULL, submitted_at DATETIME NULL, approved_by BIGINT UNSIGNED NULL, approved_at DATETIME NULL,
 rejected_by BIGINT UNSIGNED NULL, rejected_at DATETIME NULL, paid_by BIGINT UNSIGNED NULL, paid_at DATETIME NULL,
 ledger_entry_id BIGINT UNSIGNED NULL, created_by BIGINT UNSIGNED NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 KEY idx_expense_unit(unit_id,state,id), FOREIGN KEY(province_id) REFERENCES provinces(id), FOREIGN KEY(unit_id) REFERENCES units(id),
 FOREIGN KEY(claimant_user_id) REFERENCES users(id) ON DELETE SET NULL, FOREIGN KEY(fund_id) REFERENCES finance_funds(id),
 FOREIGN KEY(submitted_by) REFERENCES users(id) ON DELETE SET NULL, FOREIGN KEY(approved_by) REFERENCES users(id) ON DELETE SET NULL,
 FOREIGN KEY(rejected_by) REFERENCES users(id) ON DELETE SET NULL, FOREIGN KEY(paid_by) REFERENCES users(id) ON DELETE SET NULL,
 FOREIGN KEY(ledger_entry_id) REFERENCES finance_ledger_entries(id), FOREIGN KEY(created_by) REFERENCES users(id), CHECK(amount>0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE finance_expense_receipts (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, expense_id BIGINT UNSIGNED NOT NULL, unit_id BIGINT UNSIGNED NOT NULL,
 storage_key CHAR(64) NOT NULL, original_filename VARCHAR(180) NOT NULL, mime_type VARCHAR(100) NOT NULL, byte_size BIGINT UNSIGNED NOT NULL,
 sha256 CHAR(64) NOT NULL, uploaded_by BIGINT UNSIGNED NOT NULL, uploaded_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_expense_storage(storage_key), KEY idx_expense_receipt(expense_id,id), FOREIGN KEY(expense_id) REFERENCES finance_expense_items(id),
 FOREIGN KEY(unit_id) REFERENCES units(id), FOREIGN KEY(uploaded_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE finance_year_end_snapshots (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, province_id BIGINT UNSIGNED NOT NULL, unit_id BIGINT UNSIGNED NOT NULL,
 period_start DATE NOT NULL, period_end DATE NOT NULL, snapshot_number INT UNSIGNED NOT NULL, status ENUM('draft','reviewed','locked') NOT NULL DEFAULT 'draft',
 payload_json LONGTEXT NOT NULL, source_hash CHAR(64) NOT NULL, created_by BIGINT UNSIGNED NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 reviewed_by BIGINT UNSIGNED NULL, reviewed_at DATETIME NULL, locked_by BIGINT UNSIGNED NULL, locked_at DATETIME NULL,
 UNIQUE KEY uq_year_snapshot(unit_id,period_start,period_end,snapshot_number), KEY idx_year_snapshot(unit_id,period_end,status),
 FOREIGN KEY(province_id) REFERENCES provinces(id), FOREIGN KEY(unit_id) REFERENCES units(id), FOREIGN KEY(created_by) REFERENCES users(id),
 FOREIGN KEY(reviewed_by) REFERENCES users(id) ON DELETE SET NULL, FOREIGN KEY(locked_by) REFERENCES users(id) ON DELETE SET NULL,
 CHECK(period_start<=period_end)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TRIGGER finance_cash_no_update BEFORE UPDATE ON finance_cash_entries FOR EACH ROW SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Cash entries are immutable';
CREATE TRIGGER finance_cash_no_delete BEFORE DELETE ON finance_cash_entries FOR EACH ROW SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Cash entries are immutable';

INSERT INTO migration_history(version,name,checksum,execution_ms)
VALUES('202609280039','Subscriptions expenses designated funds and year end snapshots',SHA2('202609280039_subscriptions_expenses_v1',256),0);
COMMIT;