-- Migration: Tithe write-offs + join date boundary
-- Date: 2026-07-28

-- Add written_off status to tithes
ALTER TABLE tithes MODIFY COLUMN status ENUM('paid','unpaid','written_off') DEFAULT 'paid';

-- Tithe write-off audit log
CREATE TABLE IF NOT EXISTS tithe_writeoffs (
    id              BIGINT PRIMARY KEY AUTO_INCREMENT,
    tenant_id       BIGINT NOT NULL,
    member_id       BIGINT NOT NULL,
    tithe_id        BIGINT NULL,
    month           TINYINT NOT NULL,
    year            SMALLINT NOT NULL,
    reason          TEXT,
    written_off_by  BIGINT,
    written_off_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_wo_tenant  (tenant_id),
    INDEX idx_wo_member  (member_id),
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    FOREIGN KEY (member_id) REFERENCES members(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;