-- ============================================================================
-- Migration v10: Subscription/billing system
-- Run this ONCE on your existing database (phpMyAdmin > SQL tab).
-- Safe to run more than once.
-- ============================================================================

CREATE TABLE IF NOT EXISTS subscription_settings (
    id INT PRIMARY KEY DEFAULT 1,
    enforcement_enabled TINYINT(1) NOT NULL DEFAULT 0,
    enforcement_start_date DATE DEFAULT NULL,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT IGNORE INTO subscription_settings (id) VALUES (1);

CREATE TABLE IF NOT EXISTS subscription_plans (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    price DECIMAL(10,2) NOT NULL DEFAULT 0,
    duration_months INT NOT NULL DEFAULT 1,
    description TEXT DEFAULT NULL,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS payment_gateways (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    receiver_number VARCHAR(50) DEFAULT NULL,
    account_type VARCHAR(50) DEFAULT NULL,
    instructions TEXT DEFAULT NULL,
    logo VARCHAR(255) DEFAULT NULL,
    enabled TINYINT(1) NOT NULL DEFAULT 1,
    sort_order INT NOT NULL DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS mess_subscriptions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    mess_id INT NOT NULL,
    plan_id INT DEFAULT NULL,
    start_datetime DATETIME NOT NULL,
    end_datetime DATETIME NOT NULL,
    source ENUM('payment','admin_grant') NOT NULL DEFAULT 'payment',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (mess_id) REFERENCES mess_accounts(id) ON DELETE CASCADE,
    FOREIGN KEY (plan_id) REFERENCES subscription_plans(id) ON DELETE SET NULL,
    KEY idx_mess (mess_id, end_datetime)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS subscription_payments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    mess_id INT NOT NULL,
    plan_id INT NOT NULL,
    gateway_id INT DEFAULT NULL,
    amount DECIMAL(10,2) NOT NULL DEFAULT 0,
    payer_number VARCHAR(50) DEFAULT NULL,
    trx_id VARCHAR(100) DEFAULT NULL,
    status ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
    invoice_no VARCHAR(30) DEFAULT NULL,
    submitted_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    reviewed_at DATETIME DEFAULT NULL,
    reviewed_by VARCHAR(150) DEFAULT NULL,
    FOREIGN KEY (mess_id) REFERENCES mess_accounts(id) ON DELETE CASCADE,
    FOREIGN KEY (plan_id) REFERENCES subscription_plans(id) ON DELETE CASCADE,
    FOREIGN KEY (gateway_id) REFERENCES payment_gateways(id) ON DELETE SET NULL,
    KEY idx_mess_status (mess_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;