-- ============================================================================
-- Migration v7: New Requirements backlog
--   1. Member-level login (with next-day-only meal entry cutoff)
--   2. Ad targeting by Division/District/Thana/specific Mess ID
--   3. Support messaging (mess <-> admin)
--   4. Admin sub-user roles with configurable menu access
--   5. Common uploadable site logo
--   6. Mobile number login + SMS OTP config
-- Run this ONCE on your existing database (phpMyAdmin > SQL tab). Safe to
-- run more than once.
-- ============================================================================

-- ---------------------------------------------------------------------------
-- 1. Member-level login
-- ---------------------------------------------------------------------------
ALTER TABLE members
    ADD COLUMN IF NOT EXISTS password VARCHAR(255) DEFAULT NULL AFTER mobile;
-- Member logs in with mobile number (must be unique per mess) + this password.
-- Mess admin sets/resets it from সদস্য তালিকা. No separate "username" column
-- needed — mobile is already required & validated (11 digits, starts with 01).

-- ---------------------------------------------------------------------------
-- 2. Ad targeting by location / specific Mess ID
-- ---------------------------------------------------------------------------
ALTER TABLE ad_campaigns
    ADD COLUMN IF NOT EXISTS target_type ENUM('all','location','mess_ids') NOT NULL DEFAULT 'all' AFTER status,
    ADD COLUMN IF NOT EXISTS target_division_id INT DEFAULT NULL AFTER target_type,
    ADD COLUMN IF NOT EXISTS target_district_id INT DEFAULT NULL AFTER target_division_id,
    ADD COLUMN IF NOT EXISTS target_thana_id INT DEFAULT NULL AFTER target_district_id,
    ADD COLUMN IF NOT EXISTS target_mess_ids TEXT DEFAULT NULL AFTER target_thana_id;

-- ---------------------------------------------------------------------------
-- 3. 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,
    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;

-- ---------------------------------------------------------------------------
-- 4. 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;

-- ---------------------------------------------------------------------------
-- 5 & 6. 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);

-- NOTE: mobile-based login uniqueness is enforced at the application layer
-- (checked on registration and login lookup) rather than a hard DB UNIQUE
-- constraint here — some existing mess_accounts rows may already share a
-- blank/duplicate mobile value, and a UNIQUE constraint would fail the whole
-- migration in that case.