-- ============================================================================
-- Migration v15: Collection ledgers (meal/utility/other), QR code payments,
-- payment reject notes.
-- Run this ONCE on your existing database (phpMyAdmin > SQL tab).
-- Safe to run more than once.
-- ============================================================================

CREATE TABLE IF NOT EXISTS meal_collections (
    id INT AUTO_INCREMENT PRIMARY KEY,
    mess_id INT NOT NULL,
    member_id INT NOT NULL,
    bill_month VARCHAR(7) NOT NULL,
    amount DECIMAL(10,2) NOT NULL DEFAULT 0,
    payment_date DATE DEFAULT NULL,
    payment_method VARCHAR(50) DEFAULT NULL,
    receipt_no VARCHAR(100) DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (mess_id) REFERENCES mess_accounts(id) ON DELETE CASCADE,
    FOREIGN KEY (member_id) REFERENCES members(id) ON DELETE CASCADE,
    KEY idx_member_month (member_id, bill_month)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS other_collections (
    id INT AUTO_INCREMENT PRIMARY KEY,
    mess_id INT NOT NULL,
    member_id INT NOT NULL,
    bill_month VARCHAR(7) NOT NULL,
    amount DECIMAL(10,2) NOT NULL DEFAULT 0,
    payment_date DATE DEFAULT NULL,
    payment_method VARCHAR(50) DEFAULT NULL,
    receipt_no VARCHAR(100) DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (mess_id) REFERENCES mess_accounts(id) ON DELETE CASCADE,
    FOREIGN KEY (member_id) REFERENCES members(id) ON DELETE CASCADE,
    KEY idx_member_month (member_id, bill_month)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE payment_gateways
    ADD COLUMN IF NOT EXISTS qr_code_image VARCHAR(255) DEFAULT NULL AFTER logo;

ALTER TABLE subscription_payments
    ADD COLUMN IF NOT EXISTS reject_note TEXT DEFAULT NULL AFTER reviewed_by;

-- New "মিল ও অন্যান্য খরচ আদায়" menu item, for anyone who already ran
-- migration_v14_menu_management.sql (safe to re-run; skips if already present).
INSERT IGNORE INTO menu_items (page_key, label, icon, url, sort_order) VALUES
('collections', 'মিল ও অন্যান্য খরচ আদায়', '💵', '/mess/collections.php', 105);