-- ============================================================================
-- Migration v3: Support Portal reports, security, mess-portal upgrades, ad system
-- Run this ONCE on your existing database (phpMyAdmin > SQL tab).
-- Safe to run on a live database — only ADDs new tables/columns, never drops data.
-- ============================================================================

-- Safely add each column only if it doesn't already exist yet. This uses
-- MariaDB's native "ADD COLUMN IF NOT EXISTS" (supported since MariaDB 10.0.2)
-- instead of a stored procedure, because many shared hosts (like this one)
-- deny the "CREATE ROUTINE" privilege to normal database users.
ALTER TABLE mess_accounts
    ADD COLUMN IF NOT EXISTS district VARCHAR(100) DEFAULT NULL AFTER address,
    ADD COLUMN IF NOT EXISTS monthly_house_rent_payable DECIMAL(10,2) DEFAULT 0 AFTER district;

ALTER TABLE market_records
    ADD COLUMN IF NOT EXISTS entry_type ENUM('itemized','total') NOT NULL DEFAULT 'total' AFTER market_date;

-- Activity logs (peak-usage-hour report, per-user activity, district report source)
CREATE TABLE IF NOT EXISTS activity_logs (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_type ENUM('super_admin','mess') NOT NULL,
    user_id INT NOT NULL,
    mess_name VARCHAR(200) DEFAULT NULL,
    page VARCHAR(255) DEFAULT NULL,
    ip_address VARCHAR(45) DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    KEY idx_user (user_type, user_id),
    KEY idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Itemized market/bazar product lines (used when entry_type = 'itemized')
CREATE TABLE IF NOT EXISTS market_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    market_record_id INT NOT NULL,
    product_name VARCHAR(200) NOT NULL,
    amount DECIMAL(10,2) NOT NULL DEFAULT 0,
    FOREIGN KEY (market_record_id) REFERENCES market_records(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Extra / guest meals
CREATE TABLE IF NOT EXISTS extra_meals (
    id INT AUTO_INCREMENT PRIMARY KEY,
    mess_id INT NOT NULL,
    meal_date DATE NOT NULL,
    meal_type ENUM('breakfast','lunch','dinner') NOT NULL,
    member_id INT DEFAULT NULL,
    person_name VARCHAR(150) DEFAULT NULL,
    quantity INT NOT NULL DEFAULT 1,
    remarks VARCHAR(255) 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 SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Ad system
CREATE TABLE IF NOT EXISTS ad_settings (
    id INT PRIMARY KEY DEFAULT 1,
    adsense_enabled TINYINT(1) DEFAULT 0,
    adsense_client_id VARCHAR(100) DEFAULT NULL,
    adsense_slot_id VARCHAR(100) DEFAULT NULL,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO ad_settings (id, adsense_enabled) VALUES (1, 0) ON DUPLICATE KEY UPDATE id = id;

CREATE TABLE IF NOT EXISTS ad_campaigns (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    content TEXT DEFAULT NULL,
    cta_text VARCHAR(100) DEFAULT NULL,
    target_url VARCHAR(500) DEFAULT NULL,
    bg_image VARCHAR(255) DEFAULT NULL,
    start_datetime DATETIME NOT NULL,
    end_datetime DATETIME NOT NULL,
    skip_after_seconds INT NOT NULL DEFAULT 5,
    frequency_cap INT NOT NULL DEFAULT 3,
    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 ad_impressions (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    campaign_id INT NOT NULL,
    mess_id INT DEFAULT NULL,
    shown_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (campaign_id) REFERENCES ad_campaigns(id) ON DELETE CASCADE,
    KEY idx_campaign_time (campaign_id, shown_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ad_clicks (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    campaign_id INT NOT NULL,
    mess_id INT DEFAULT NULL,
    clicked_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (campaign_id) REFERENCES ad_campaigns(id) ON DELETE CASCADE,
    KEY idx_campaign_time (campaign_id, clicked_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;