-- =====================================================
-- Bachelor Mess Management System - Database Schema
-- =====================================================
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- 1. Super Admin
CREATE TABLE IF NOT EXISTS super_admins (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. Mess / House Accounts (tenant)
CREATE TABLE IF NOT EXISTS mess_accounts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(150) NOT NULL UNIQUE,      -- login id (gmail)
    password VARCHAR(255) NOT NULL,
    mess_name VARCHAR(200) DEFAULT NULL,
    owner_name VARCHAR(150) DEFAULT NULL,
    manager_name VARCHAR(150) DEFAULT NULL,
    mobile VARCHAR(20) DEFAULT NULL,
    address TEXT DEFAULT NULL,
    district VARCHAR(100) DEFAULT NULL,
    division_id INT DEFAULT NULL,
    district_id INT DEFAULT NULL,
    thana_id INT DEFAULT NULL,
    monthly_house_rent_payable DECIMAL(10,2) DEFAULT 0,
    referral_code VARCHAR(20) DEFAULT NULL,
    mess_code VARCHAR(12) DEFAULT NULL,
    referred_by_mess_id INT DEFAULT NULL,
    house_no VARCHAR(50) DEFAULT NULL,
    total_floors INT DEFAULT 0,
    total_rooms INT DEFAULT 0,
    total_seats INT DEFAULT 0,
    vacant_seats INT DEFAULT 0,
    mess_type ENUM('male','female','student','job_holder','mixed') DEFAULT 'male',
    status ENUM('active','inactive') DEFAULT 'active',
    profile_completed TINYINT(1) DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_referral_code (referral_code),
    UNIQUE KEY uniq_mess_code (mess_code),
    KEY idx_referred_by (referred_by_mess_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2b. Division / District / Thana reference data (Bangladesh). Thana list is
-- extensible from the Support Portal (এলাকা ব্যবস্থাপনা page).
CREATE TABLE IF NOT EXISTS bd_divisions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS bd_districts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    division_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    FOREIGN KEY (division_id) REFERENCES bd_divisions(id) ON DELETE CASCADE,
    KEY idx_division (division_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS bd_thanas (
    id INT AUTO_INCREMENT PRIMARY KEY,
    district_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    FOREIGN KEY (district_id) REFERENCES bd_districts(id) ON DELETE CASCADE,
    KEY idx_district (district_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. OTP codes (password reset / email change / signup verify)
CREATE TABLE IF NOT EXISTS otp_codes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(150) NOT NULL,
    otp VARCHAR(10) NOT NULL,
    purpose ENUM('password_reset','email_change','signup_verify') NOT NULL,
    new_email VARCHAR(150) DEFAULT NULL,     -- used for email_change purpose
    expires_at DATETIME NOT NULL,
    used TINYINT(1) DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_email_purpose (email, purpose)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4. Rooms
CREATE TABLE IF NOT EXISTS rooms (
    id INT AUTO_INCREMENT PRIMARY KEY,
    mess_id INT NOT NULL,
    room_no VARCHAR(50) NOT NULL,
    floor_no VARCHAR(20) DEFAULT NULL,
    room_type VARCHAR(100) DEFAULT NULL,
    total_seats INT NOT NULL DEFAULT 1,
    rent_per_seat DECIMAL(10,2) DEFAULT 0,
    meter_no VARCHAR(100) DEFAULT NULL,
    facilities TEXT DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (mess_id) REFERENCES mess_accounts(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 5. Members
CREATE TABLE IF NOT EXISTS members (
    id INT AUTO_INCREMENT PRIMARY KEY,
    mess_id INT NOT NULL,
    member_code VARCHAR(14) DEFAULT NULL,
    room_id INT DEFAULT NULL,
    seat_no VARCHAR(20) DEFAULT NULL,
    name VARCHAR(150) NOT NULL,
    photo VARCHAR(255) DEFAULT NULL,
    parent_name VARCHAR(150) DEFAULT NULL,
    mobile VARCHAR(20) DEFAULT NULL,
    password VARCHAR(255) DEFAULT NULL,       -- member-level login (with mobile as username)
    nid VARCHAR(50) DEFAULT NULL,
    permanent_address TEXT DEFAULT NULL,
    current_address TEXT DEFAULT NULL,
    profession VARCHAR(150) DEFAULT NULL,
    workplace VARCHAR(200) DEFAULT NULL,
    emergency_contact_name VARCHAR(150) DEFAULT NULL,
    emergency_contact_mobile VARCHAR(20) DEFAULT NULL,
    joining_date DATE DEFAULT NULL,
    leaving_date DATE DEFAULT NULL,
    monthly_rent DECIMAL(10,2) DEFAULT 0,
    advance_amount DECIMAL(10,2) DEFAULT 0,
    advance_date DATE DEFAULT NULL,
    advance_refundable TINYINT(1) DEFAULT 1,
    status ENUM('active','inactive') DEFAULT 'active',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (mess_id) REFERENCES mess_accounts(id) ON DELETE CASCADE,
    FOREIGN KEY (room_id) REFERENCES rooms(id) ON DELETE SET NULL,
    UNIQUE KEY uniq_member_code (member_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 6. Rent payments (monthly, per member)
CREATE TABLE IF NOT EXISTS rent_payments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    mess_id INT NOT NULL,
    member_id INT NOT NULL,
    bill_month VARCHAR(7) NOT NULL,           -- format YYYY-MM
    monthly_rent DECIMAL(10,2) DEFAULT 0,
    due_date DATE DEFAULT NULL,
    paid_amount DECIMAL(10,2) DEFAULT 0,
    due_amount DECIMAL(10,2) 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,
    UNIQUE KEY uniq_member_month (member_id, bill_month)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 6b. Individual rent payment transactions. A member can pay rent more than
-- once in a month (partial payments) — each one is its own row here.
-- rent_payments.paid_amount/due_amount/payment_date/payment_method/receipt_no
-- stay in sync as a running aggregate (sum/latest) of these entries, so
-- existing dashboard/report queries keep working unchanged.
CREATE TABLE IF NOT EXISTS rent_payment_entries (
    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;

-- 7. Utility bills (mess-wide, monthly)
CREATE TABLE IF NOT EXISTS utility_bills (
    id INT AUTO_INCREMENT PRIMARY KEY,
    mess_id INT NOT NULL,
    bill_month VARCHAR(7) NOT NULL,
    electricity DECIMAL(10,2) DEFAULT 0,
    gas DECIMAL(10,2) DEFAULT 0,
    water DECIMAL(10,2) DEFAULT 0,
    internet DECIMAL(10,2) DEFAULT 0,
    service_charge DECIMAL(10,2) DEFAULT 0,
    cleaning DECIMAL(10,2) DEFAULT 0,
    maid_bill DECIMAL(10,2) DEFAULT 0,
    generator DECIMAL(10,2) DEFAULT 0,
    others DECIMAL(10,2) DEFAULT 0,
    total_bill DECIMAL(10,2) GENERATED ALWAYS AS
        (electricity+gas+water+internet+service_charge+cleaning+maid_bill+generator+others) STORED,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (mess_id) REFERENCES mess_accounts(id) ON DELETE CASCADE,
    UNIQUE KEY uniq_mess_month (mess_id, bill_month)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 8. Meals (daily/monthly per member)
-- Daily meal on/off chart: one row per member per day, with breakfast/lunch/dinner
-- toggled independently. A member is only charged for meals actually taken.
CREATE TABLE IF NOT EXISTS meal_entries (
    id INT AUTO_INCREMENT PRIMARY KEY,
    mess_id INT NOT NULL,
    member_id INT NOT NULL,
    meal_date DATE NOT NULL,
    breakfast TINYINT(1) NOT NULL DEFAULT 0,
    lunch TINYINT(1) NOT NULL DEFAULT 0,
    dinner TINYINT(1) NOT NULL DEFAULT 0,
    total_meals TINYINT GENERATED ALWAYS AS (breakfast+lunch+dinner) STORED,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (mess_id) REFERENCES mess_accounts(id) ON DELETE CASCADE,
    FOREIGN KEY (member_id) REFERENCES members(id) ON DELETE CASCADE,
    UNIQUE KEY uniq_member_meal_date (member_id, meal_date),
    KEY idx_mess_date (mess_id, meal_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 9. Market / bazar records (feeds meal rate calculation)
CREATE TABLE IF NOT EXISTS market_records (
    id INT AUTO_INCREMENT PRIMARY KEY,
    mess_id INT NOT NULL,
    market_date DATE NOT NULL,
    entry_type ENUM('itemized','total') NOT NULL DEFAULT 'total',
    done_by VARCHAR(150) DEFAULT NULL,
    amount DECIMAL(10,2) NOT NULL DEFAULT 0,
    remarks TEXT DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (mess_id) REFERENCES mess_accounts(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 9b. Itemized product lines for market_records entered in "itemized" mode
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,
    quantity DECIMAL(10,2) DEFAULT NULL,
    unit VARCHAR(30) DEFAULT 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;

-- 10. General expenses (repair, cleaning supplies etc, non-market non-utility)
CREATE TABLE IF NOT EXISTS expenses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    mess_id INT NOT NULL,
    expense_date DATE NOT NULL,
    expense_type VARCHAR(100) DEFAULT NULL,
    description TEXT DEFAULT NULL,
    amount DECIMAL(10,2) NOT NULL DEFAULT 0,
    paid_by VARCHAR(150) DEFAULT NULL,
    payment_method VARCHAR(50) DEFAULT NULL,
    receipt_no VARCHAR(100) DEFAULT NULL,
    remarks TEXT DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (mess_id) REFERENCES mess_accounts(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 11. Member exit / settlement history
CREATE TABLE IF NOT EXISTS member_settlements (
    id INT AUTO_INCREMENT PRIMARY KEY,
    mess_id INT NOT NULL,
    member_id INT NOT NULL,
    leaving_date DATE NOT NULL,
    days_stayed_last_month INT DEFAULT 0,
    last_month_rent DECIMAL(10,2) DEFAULT 0,
    last_month_bill DECIMAL(10,2) DEFAULT 0,
    due_amount DECIMAL(10,2) DEFAULT 0,
    security_deposit DECIMAL(10,2) DEFAULT 0,
    refund_amount DECIMAL(10,2) DEFAULT 0,
    exit_clearance ENUM('cleared','pending') DEFAULT 'pending',
    remarks TEXT 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
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 12. House owner account (if mess operator rents the house)
CREATE TABLE IF NOT EXISTS owner_accounts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    mess_id INT NOT NULL,
    owner_name VARCHAR(150) DEFAULT NULL,
    monthly_house_rent DECIMAL(10,2) DEFAULT 0,
    contract_start DATE DEFAULT NULL,
    contract_end DATE DEFAULT NULL,
    advance_amount DECIMAL(10,2) DEFAULT 0,
    utility_responsibility VARCHAR(255) DEFAULT NULL,
    service_charge DECIMAL(10,2) DEFAULT 0,
    maintenance DECIMAL(10,2) DEFAULT 0,
    notice_period_days INT DEFAULT 30,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (mess_id) REFERENCES mess_accounts(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 13. Owner monthly payments
CREATE TABLE IF NOT EXISTS owner_payments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    owner_account_id INT NOT NULL,
    pay_month VARCHAR(7) NOT NULL,
    amount_due DECIMAL(10,2) DEFAULT 0,
    amount_paid DECIMAL(10,2) DEFAULT 0,
    payment_date DATE DEFAULT NULL,
    remarks TEXT DEFAULT NULL,
    FOREIGN KEY (owner_account_id) REFERENCES owner_accounts(id) ON DELETE CASCADE,
    UNIQUE KEY uniq_owner_month (owner_account_id, pay_month)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;

-- NOTE: no default super admin account is created here on purpose. A hardcoded
-- well-known email/password baked into every install of this codebase would be
-- a security risk. Instead, after importing this schema, visit
-- setup_super_admin.php once (see README.md) to create YOUR OWN unique super
-- admin account, then delete that file from the server.

-- =====================================================
-- v3 additions: activity logging, district, itemized market,
-- extra meals, ad system
-- =====================================================

-- (district and monthly_house_rent_payable are defined directly in the
-- mess_accounts CREATE TABLE above, so nothing further is needed here.)

-- 14. Activity logs: every page view by every logged-in user (mess or super admin),
-- used for "which hours is the portal used most" and per-user activity reports.
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;

-- 15. (entry_type + market_items are defined directly in the market_records /
-- market_items CREATE TABLE statements above, so nothing further is needed here.)

-- 16. Extra / guest meals (not part of a member's normal daily on/off chart) —
-- e.g. a guest ate, or a member had an extra plate. Adds to the mess's total
-- meal count for the meal-rate calculation, and optionally bills a member.
-- Meal & utility/other expense collection: separate payment ledgers from
-- rent, so "মিল ও অন্যান্য খরচ আদায়" can track its own paid/due per member.
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;

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,          -- bill this member, if applicable
    person_name VARCHAR(150) DEFAULT NULL, -- guest name, if not a member
    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;

-- 17. Google AdSense settings (single row, id=1)
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,   -- ca-pub-xxxxxxxxxxxx
    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;

-- 18. Custom ad campaigns (managed entirely from the Super Admin / Support portal)
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,
    daily_start_time TIME DEFAULT NULL,       -- e.g. only show between 9:00-21:00 each day; NULL = all day
    daily_end_time TIME DEFAULT NULL,
    repeat_interval_seconds INT NOT NULL DEFAULT 0, -- min gap between two shows to the same mess; 0 = no extra gap
    skip_after_seconds INT NOT NULL DEFAULT 5,
    frequency_cap INT NOT NULL DEFAULT 3,   -- max times shown to the same mess per day
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    target_type ENUM('all','location','mess_ids') NOT NULL DEFAULT 'all',
    target_division_id INT DEFAULT NULL,
    target_district_id INT DEFAULT NULL,
    target_thana_id INT DEFAULT NULL,
    target_mess_ids TEXT DEFAULT NULL,       -- comma-separated mess IDs, used when target_type='mess_ids'
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 19. Ad impressions (views) and clicks, for the campaign report
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;

-- ---------------------------------------------------------------------------
-- Seed data: 8 Divisions, 64 Districts (standard Bangladesh administrative
-- geography), and a starter Thana/Upazila list (full detail for Dhaka and
-- Chandpur, at least a Sadar entry for every other district). Extend anytime
-- from Support Portal > এলাকা ব্যবস্থাপনা.
-- ---------------------------------------------------------------------------
INSERT IGNORE INTO bd_divisions (id, name) VALUES
(1,'Dhaka'), (2,'Chattogram'), (3,'Rajshahi'), (4,'Khulna'),
(5,'Barishal'), (6,'Sylhet'), (7,'Rangpur'), (8,'Mymensingh');

INSERT IGNORE INTO bd_districts (division_id, name) VALUES
(1,'Dhaka'), (1,'Faridpur'), (1,'Gazipur'), (1,'Gopalganj'), (1,'Kishoreganj'),
(1,'Madaripur'), (1,'Manikganj'), (1,'Munshiganj'), (1,'Narayanganj'), (1,'Narsingdi'),
(1,'Rajbari'), (1,'Shariatpur'), (1,'Tangail'),
(2,'Bandarban'), (2,'Brahmanbaria'), (2,'Chandpur'), (2,'Chattogram'), (2,'Cumilla'),
(2,'Cox\'s Bazar'), (2,'Feni'), (2,'Khagrachhari'), (2,'Lakshmipur'), (2,'Noakhali'), (2,'Rangamati'),
(3,'Bogura'), (3,'Joypurhat'), (3,'Naogaon'), (3,'Natore'), (3,'Chapainawabganj'),
(3,'Pabna'), (3,'Rajshahi'), (3,'Sirajganj'),
(4,'Bagerhat'), (4,'Chuadanga'), (4,'Jashore'), (4,'Jhenaidah'), (4,'Khulna'),
(4,'Kushtia'), (4,'Magura'), (4,'Meherpur'), (4,'Narail'), (4,'Satkhira'),
(5,'Barguna'), (5,'Barishal'), (5,'Bhola'), (5,'Jhalokati'), (5,'Patuakhali'), (5,'Pirojpur'),
(6,'Habiganj'), (6,'Moulvibazar'), (6,'Sunamganj'), (6,'Sylhet'),
(7,'Dinajpur'), (7,'Gaibandha'), (7,'Kurigram'), (7,'Lalmonirhat'), (7,'Nilphamari'),
(7,'Panchagarh'), (7,'Rangpur'), (7,'Thakurgaon'),
(8,'Jamalpur'), (8,'Mymensingh'), (8,'Netrokona'), (8,'Sherpur');

INSERT IGNORE INTO bd_thanas (district_id, name)
SELECT id, CONCAT(name, ' Sadar') FROM bd_districts;

INSERT IGNORE INTO bd_thanas (district_id, name)
SELECT id, t.name FROM bd_districts d
JOIN (
    SELECT 'Dhamrai' AS name UNION ALL SELECT 'Dohar' UNION ALL SELECT 'Keraniganj' UNION ALL
    SELECT 'Nawabganj' UNION ALL SELECT 'Savar' UNION ALL SELECT 'Adabor' UNION ALL
    SELECT 'Badda' UNION ALL SELECT 'Bangshal' UNION ALL SELECT 'Bimanbandar' UNION ALL
    SELECT 'Cantonment' UNION ALL SELECT 'Chackbazar' UNION ALL SELECT 'Dakshinkhan' UNION ALL
    SELECT 'Darus Salam' UNION ALL SELECT 'Demra' UNION ALL SELECT 'Dhanmondi' UNION ALL
    SELECT 'Gendaria' UNION ALL SELECT 'Gulshan' UNION ALL SELECT 'Hazaribagh' UNION ALL
    SELECT 'Jatrabari' UNION ALL SELECT 'Kafrul' UNION ALL SELECT 'Kalabagan' UNION ALL
    SELECT 'Kamrangirchar' UNION ALL SELECT 'Khilgaon' UNION ALL SELECT 'Khilkhet' UNION ALL
    SELECT 'Kotwali' UNION ALL SELECT 'Lalbagh' UNION ALL SELECT 'Mirpur' UNION ALL
    SELECT 'Mohammadpur' UNION ALL SELECT 'Motijheel' UNION ALL SELECT 'Mugda' UNION ALL
    SELECT 'New Market' UNION ALL SELECT 'Pallabi' UNION ALL SELECT 'Paltan' UNION ALL
    SELECT 'Ramna' UNION ALL SELECT 'Rampura' UNION ALL SELECT 'Sabujbagh' UNION ALL
    SELECT 'Shah Ali' UNION ALL SELECT 'Shahbagh' UNION ALL SELECT 'Sher-e-Bangla Nagar' UNION ALL
    SELECT 'Shyampur' UNION ALL SELECT 'Sutrapur' UNION ALL SELECT 'Tejgaon' UNION ALL
    SELECT 'Turag' UNION ALL SELECT 'Uttara' UNION ALL SELECT 'Uttar Khan' UNION ALL
    SELECT 'Vatara' UNION ALL SELECT 'Wari'
) t ON 1=1
WHERE d.name = 'Dhaka' AND d.division_id = 1;

INSERT IGNORE INTO bd_thanas (district_id, name)
SELECT id, t.name FROM bd_districts d
JOIN (
    SELECT 'Faridganj' AS name UNION ALL SELECT 'Haimchar' UNION ALL SELECT 'Hajiganj' UNION ALL
    SELECT 'Kachua' UNION ALL SELECT 'Matlab Dakshin' UNION ALL SELECT 'Matlab Uttar' UNION ALL
    SELECT 'Shahrasti'
) t ON 1=1
WHERE d.name = 'Chandpur' AND d.division_id = 2;

-- 26. Support messaging (mess <-> Support Portal)
CREATE TABLE IF NOT EXISTS support_messages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    mess_id INT NOT NULL,
    sender_type ENUM('mess','admin') NOT NULL,
    sender_name VARCHAR(150) DEFAULT NULL,
    message TEXT NOT NULL,
    attachment_path VARCHAR(255) DEFAULT NULL,
    is_read TINYINT(1) NOT NULL DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (mess_id) REFERENCES mess_accounts(id) ON DELETE CASCADE,
    KEY idx_mess_time (mess_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 27. Admin sub-user roles (separate from the root super_admins table)
CREATE TABLE IF NOT EXISTS admin_users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    password VARCHAR(255) NOT 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 admin_permissions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    admin_user_id INT NOT NULL,
    permission_key VARCHAR(80) NOT NULL,
    FOREIGN KEY (admin_user_id) REFERENCES admin_users(id) ON DELETE CASCADE,
    UNIQUE KEY uniq_admin_perm (admin_user_id, permission_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 27b. Mess-portal menu management: reorderable sidebar with optional dropdown groups
CREATE TABLE IF NOT EXISTS menu_groups (
    id INT AUTO_INCREMENT PRIMARY KEY,
    label VARCHAR(100) NOT NULL,
    icon VARCHAR(10) DEFAULT '📁',
    sort_order INT NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS menu_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    page_key VARCHAR(100) NOT NULL UNIQUE,
    label VARCHAR(150) NOT NULL,
    icon VARCHAR(10) DEFAULT '📄',
    url VARCHAR(255) NOT NULL,
    group_id INT DEFAULT NULL,
    sort_order INT NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    FOREIGN KEY (group_id) REFERENCES menu_groups(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO menu_items (page_key, label, icon, url, sort_order) VALUES
('dashboard', 'ড্যাশবোর্ড', '📊', '/mess/dashboard.php', 10),
('house_info', 'মেসের তথ্য', '🏘️', '/mess/house_info.php', 20),
('rooms', 'রুম ব্যবস্থাপনা', '🚪', '/mess/rooms.php', 30),
('members', 'সদস্য তালিকা', '👥', '/mess/members.php', 40),
('rent', 'ভাড়া হিসাব', '💰', '/mess/rent.php', 50),
('utility_bills', 'ইউটিলিটি বিল', '💡', '/mess/utility_bills.php', 60),
('meals', 'দৈনিক মিল চার্ট', '🍽️', '/mess/meals.php', 70),
('today_meal', 'আজকের মিল', '🔔', '/mess/today_meal.php', 80),
('meal_chart', 'মাসিক মিল চার্ট', '📅', '/mess/meal_chart.php', 90),
('extra_meals', 'অতিরিক্ত/গেস্ট মিল', '➕', '/mess/extra_meals.php', 100),
('collections', 'মিল ও অন্যান্য খরচ আদায়', '💵', '/mess/collections.php', 105),
('market', 'বাজার খরচ', '🛒', '/mess/market.php', 110),
('expenses', 'অন্যান্য খরচ', '🧾', '/mess/expenses.php', 120),
('owner_account', 'বাড়ির মালিক হিসাব', '🏠', '/mess/owner_account.php', 130),
('settlements', 'সদস্য এক্সিট/সেটেলমেন্ট', '🚪', '/mess/settlements.php', 140),
('reports', 'রিপোর্ট', '📑', '/mess/reports.php', 150),
('member_statement', 'সদস্যের মাসিক বিল (ব্রেকডাউন)', '🧮', '/mess/member_statement.php', 160),
('dues_report', 'বকেয়ার বিস্তারিত রিপোর্ট', '📛', '/mess/dues_report.php', 170),
('support_inbox', 'সাপোর্ট মেসেজ', '💬', '/mess/support_inbox.php', 180),
('referrals', 'রেফারেল', '🔗', '/mess/referrals.php', 190),
('subscription', 'সাবস্ক্রিপশন', '📅', '/mess/subscription.php', 200),
('account_settings', 'একাউন্ট সেটিংস', '⚙️', '/mess/account_settings.php', 210);

-- 28. Site-wide settings: common logo, mobile login toggle, SMS OTP config
CREATE TABLE IF NOT EXISTS site_settings (
    id INT PRIMARY KEY DEFAULT 1,
    site_logo VARCHAR(255) DEFAULT NULL,
    mobile_login_enabled TINYINT(1) NOT NULL DEFAULT 0,
    sms_otp_enabled TINYINT(1) NOT NULL DEFAULT 0,
    sms_api_url VARCHAR(500) DEFAULT NULL,
    sms_api_key VARCHAR(255) DEFAULT NULL,
    sms_sender_id VARCHAR(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO site_settings (id) VALUES (1);

-- 29. Adsterra banner ads
CREATE TABLE IF NOT EXISTS adsterra_ads (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    banner_script TEXT NOT NULL,
    width INT NOT NULL DEFAULT 300,
    height INT NOT NULL DEFAULT 250,
    placement VARCHAR(30) NOT NULL DEFAULT 'above_bottom_menu',  -- after_topbar | after_section_1..3 | above_bottom_menu
    position_top INT DEFAULT NULL,      -- legacy (unused since v16)
    position_bottom INT DEFAULT NULL,   -- legacy (unused since v16)
    position_left INT DEFAULT NULL,     -- legacy (unused since v16)
    position_right INT DEFAULT NULL,    -- legacy (unused since v16)
    show_pages TEXT DEFAULT NULL,        -- comma-separated page filenames, or 'all'
    show_on_login TINYINT(1) NOT NULL DEFAULT 0,
    show_on_register TINYINT(1) NOT NULL DEFAULT 0,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 30. Subscription / billing system
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,
    free_trial_months INT NOT NULL DEFAULT 0,
    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,
    qr_code_image 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','free_trial') 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,
    reject_note TEXT DEFAULT NULL,
    activated_subscription_id INT 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;