-- =====================================================================
-- One Step ISP Billing System — Core Database Schema (Phase 0)
-- Engine: InnoDB | Charset: utf8mb4
-- Designed for: 5000+ subscribers, multiple Mikrotik routers, multi-level resellers
-- =====================================================================

SET FOREIGN_KEY_CHECKS = 0;
SET NAMES utf8mb4;

CREATE DATABASE IF NOT EXISTS ispbdcom_isp_billing CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE ispbdcom_isp_billing;

-- ---------------------------------------------------------------------
-- 1. ADMIN / RESELLER / STAFF (unified account table with roles)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS users (
    id                  BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    parent_id           BIGINT UNSIGNED DEFAULT NULL COMMENT 'Reseller hierarchy: sub-reseller -> reseller -> admin',
    role                ENUM('super_admin','admin','reseller','sub_reseller','support') NOT NULL DEFAULT 'reseller',
    username            VARCHAR(50) NOT NULL UNIQUE,
    password_hash       VARCHAR(255) NOT NULL,
    full_name           VARCHAR(100) NOT NULL,
    phone               VARCHAR(20) DEFAULT NULL,
    email               VARCHAR(100) DEFAULT NULL,
    company_name        VARCHAR(150) DEFAULT NULL,
    address             VARCHAR(255) DEFAULT NULL,
    balance             DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT 'Reseller wallet/credit balance',
    commission_percent  DECIMAL(5,2) NOT NULL DEFAULT 0.00,
    two_fa_enabled      TINYINT(1) NOT NULL DEFAULT 0,
    two_fa_secret       VARCHAR(64) DEFAULT NULL,
    status              ENUM('active','inactive','suspended') NOT NULL DEFAULT 'active',
    last_login_at       DATETIME DEFAULT NULL,
    last_login_ip       VARCHAR(45) DEFAULT NULL,
    created_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (parent_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_role (role),
    INDEX idx_parent (parent_id)
) ENGINE=InnoDB;

-- Role-based permission matrix (fine-grained access control)
CREATE TABLE IF NOT EXISTS permissions (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    role        ENUM('super_admin','admin','reseller','sub_reseller','support') NOT NULL,
    module_key  VARCHAR(60) NOT NULL COMMENT 'e.g. customers.create, routers.manage, reports.btrc',
    allowed     TINYINT(1) NOT NULL DEFAULT 0,
    UNIQUE KEY uniq_role_module (role, module_key)
) ENGINE=InnoDB;

-- Session tracking (supports multi-device sync/logout)
CREATE TABLE IF NOT EXISTS user_sessions (
    id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id         BIGINT UNSIGNED NOT NULL,
    session_token   VARCHAR(128) NOT NULL UNIQUE,
    device_info     VARCHAR(255) DEFAULT NULL,
    ip_address      VARCHAR(45) DEFAULT NULL,
    last_active_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    expires_at      DATETIME NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user (user_id)
) ENGINE=InnoDB;

-- Full audit log (every add/edit/remove across the system)
CREATE TABLE IF NOT EXISTS audit_logs (
    id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id      BIGINT UNSIGNED DEFAULT NULL,
    action       VARCHAR(50) NOT NULL COMMENT 'create, update, delete, login, login_failed...',
    module       VARCHAR(50) NOT NULL COMMENT 'customers, packages, routers, invoices...',
    record_id    BIGINT UNSIGNED DEFAULT NULL,
    old_value    JSON DEFAULT NULL,
    new_value    JSON DEFAULT NULL,
    ip_address   VARCHAR(45) DEFAULT NULL,
    created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_module (module),
    INDEX idx_created (created_at)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- 2. MIKROTIK ROUTERS (multi-router support)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS mikrotik_routers (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    reseller_id     BIGINT UNSIGNED DEFAULT NULL COMMENT 'NULL = owned directly by admin/NOC',
    name            VARCHAR(100) NOT NULL,
    host            VARCHAR(100) NOT NULL COMMENT 'IP or hostname',
    api_port        INT UNSIGNED NOT NULL DEFAULT 8728,
    api_ssl_port    INT UNSIGNED DEFAULT 8729,
    use_ssl         TINYINT(1) NOT NULL DEFAULT 0,
    username        VARCHAR(50) NOT NULL,
    password_enc    VARCHAR(255) NOT NULL COMMENT 'Encrypted (reversible) — needed for live API calls',
    location        VARCHAR(150) DEFAULT NULL,
    connection_type ENUM('pppoe','hotspot','static','mixed') NOT NULL DEFAULT 'pppoe',
    status          ENUM('online','offline','unknown') NOT NULL DEFAULT 'unknown',
    last_sync_at    DATETIME DEFAULT NULL,
    last_backup_at  DATETIME DEFAULT NULL,
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (reseller_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS router_config_backups (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    router_id   INT UNSIGNED NOT NULL,
    file_path   VARCHAR(255) NOT NULL,
    taken_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (router_id) REFERENCES mikrotik_routers(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- 3. PACKAGES (Lite / Regular / Premium — editable, reseller override)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS packages (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tier            ENUM('Lite','Regular','Premium') NOT NULL,
    name            VARCHAR(100) NOT NULL,
    speed_mbps      INT UNSIGNED NOT NULL,
    price           DECIMAL(10,2) NOT NULL,
    public_ip       TINYINT(1) NOT NULL DEFAULT 0,
    fup_limit_gb    INT UNSIGNED DEFAULT NULL COMMENT 'NULL = unlimited',
    status          ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Per-reseller custom pricing / commission override on a package
CREATE TABLE IF NOT EXISTS reseller_package_pricing (
    id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    reseller_id  BIGINT UNSIGNED NOT NULL,
    package_id   INT UNSIGNED NOT NULL,
    custom_price DECIMAL(10,2) NOT NULL,
    UNIQUE KEY uniq_reseller_package (reseller_id, package_id),
    FOREIGN KEY (reseller_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (package_id) REFERENCES packages(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- 4. CUSTOMERS / SUBSCRIBERS
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS customers (
    id                  BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    reseller_id         BIGINT UNSIGNED DEFAULT NULL,
    router_id           INT UNSIGNED DEFAULT NULL,
    package_id          INT UNSIGNED DEFAULT NULL,
    customer_code       VARCHAR(20) NOT NULL UNIQUE COMMENT 'Human-friendly ID e.g. CCN-000123',
    full_name           VARCHAR(100) NOT NULL,
    phone               VARCHAR(20) NOT NULL,
    email                VARCHAR(100) DEFAULT NULL,
    nid_number          VARCHAR(30) DEFAULT NULL,
    address             VARCHAR(255) DEFAULT NULL,
    area                VARCHAR(100) DEFAULT NULL,
    connection_type     ENUM('pppoe','hotspot','static') NOT NULL DEFAULT 'pppoe',
    pppoe_username      VARCHAR(60) DEFAULT NULL,
    pppoe_password_enc  VARCHAR(255) DEFAULT NULL,
    static_ip           VARCHAR(45) DEFAULT NULL,
    mac_address         VARCHAR(20) DEFAULT NULL,
    caller_id_bind      TINYINT(1) NOT NULL DEFAULT 0,
    connection_date     DATE DEFAULT NULL,
    billing_cycle_day   TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT 'Day of month bill generates',
    status              ENUM('active','inactive','suspended','left') NOT NULL DEFAULT 'active',
    left_at             DATETIME DEFAULT NULL,
    created_by          BIGINT UNSIGNED DEFAULT NULL,
    created_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (reseller_id) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (router_id) REFERENCES mikrotik_routers(id) ON DELETE SET NULL,
    FOREIGN KEY (package_id) REFERENCES packages(id) ON DELETE SET NULL,
    INDEX idx_status (status),
    INDEX idx_reseller (reseller_id),
    INDEX idx_phone (phone)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS customer_package_history (
    id             BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    customer_id    BIGINT UNSIGNED NOT NULL,
    old_package_id INT UNSIGNED DEFAULT NULL,
    new_package_id INT UNSIGNED DEFAULT NULL,
    changed_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    changed_by     BIGINT UNSIGNED DEFAULT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- 5. BILLING / INVOICES / PAYMENTS
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS invoices (
    id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    invoice_no      VARCHAR(30) NOT NULL UNIQUE,
    pay_token       VARCHAR(64) DEFAULT NULL UNIQUE COMMENT 'লগইনবিহীন অনলাইন পেমেন্ট লিংকের জন্য',
    customer_id     BIGINT UNSIGNED NOT NULL,
    package_id      INT UNSIGNED DEFAULT NULL,
    amount          DECIMAL(10,2) NOT NULL,
    discount        DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    payable_amount  DECIMAL(10,2) NOT NULL,
    billing_month   CHAR(7) NOT NULL COMMENT 'YYYY-MM',
    due_date        DATE NOT NULL,
    status          ENUM('unpaid','paid','partial','overdue','cancelled') NOT NULL DEFAULT 'unpaid',
    generated_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
    UNIQUE KEY uniq_customer_month (customer_id, billing_month)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS payments (
    id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    invoice_id      BIGINT UNSIGNED NOT NULL,
    customer_id     BIGINT UNSIGNED NOT NULL,
    amount          DECIMAL(10,2) NOT NULL,
    method          ENUM('cash','bkash','nagad','rocket','bank','gateway','wallet') NOT NULL DEFAULT 'cash',
    transaction_ref VARCHAR(100) DEFAULT NULL,
    collected_by    BIGINT UNSIGNED DEFAULT NULL,
    paid_at         DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS reseller_ledger (
    id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    reseller_id   BIGINT UNSIGNED NOT NULL,
    type          ENUM('debit','credit') NOT NULL,
    amount        DECIMAL(12,2) NOT NULL,
    reference     VARCHAR(150) DEFAULT NULL,
    balance_after DECIMAL(12,2) NOT NULL,
    created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (reseller_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS expenses (
    id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title         VARCHAR(150) NOT NULL,
    category      VARCHAR(80) DEFAULT NULL,
    amount        DECIMAL(12,2) NOT NULL,
    note          VARCHAR(255) DEFAULT NULL,
    spent_at      DATE NOT NULL,
    created_by    BIGINT UNSIGNED DEFAULT NULL,
    created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS vouchers (
    id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    code          VARCHAR(30) NOT NULL UNIQUE,
    package_id    INT UNSIGNED DEFAULT NULL,
    validity_days INT UNSIGNED NOT NULL DEFAULT 30,
    price         DECIMAL(10,2) NOT NULL,
    status        ENUM('unused','used','expired') NOT NULL DEFAULT 'unused',
    used_by       BIGINT UNSIGNED DEFAULT NULL,
    used_at       DATETIME DEFAULT NULL,
    created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (package_id) REFERENCES packages(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- 6. NOTIFICATIONS / SUPPORT
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS notifications_log (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    customer_id BIGINT UNSIGNED DEFAULT NULL,
    channel     ENUM('sms','whatsapp','email','telegram') NOT NULL,
    purpose     VARCHAR(50) DEFAULT NULL COMMENT 'bill_reminder, otp, welcome, alert...',
    message     TEXT NOT NULL,
    status      ENUM('sent','failed','pending') NOT NULL DEFAULT 'pending',
    sent_at     DATETIME DEFAULT NULL,
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS support_tickets (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    customer_id BIGINT UNSIGNED NOT NULL,
    subject     VARCHAR(150) NOT NULL,
    priority    ENUM('low','medium','high','urgent') NOT NULL DEFAULT 'medium',
    status      ENUM('open','in_progress','resolved','closed') NOT NULL DEFAULT 'open',
    assigned_to BIGINT UNSIGNED DEFAULT NULL,
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS ticket_replies (
    id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ticket_id  BIGINT UNSIGNED NOT NULL,
    sender_type ENUM('customer','staff') NOT NULL,
    sender_id  BIGINT UNSIGNED DEFAULT NULL,
    message    TEXT NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (ticket_id) REFERENCES support_tickets(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- 7. SYSTEM / SYNC / BACKUP
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS system_settings (
    setting_key   VARCHAR(80) PRIMARY KEY,
    setting_value TEXT DEFAULT NULL,
    updated_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- Phase 7 — Payment Gateway Integration
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS payment_gateways (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    gateway_key     VARCHAR(30) NOT NULL UNIQUE COMMENT 'bkash | sslcommerz',
    display_name    VARCHAR(60) NOT NULL,
    mode            ENUM('sandbox','live') NOT NULL DEFAULT 'sandbox',
    is_enabled      TINYINT(1) NOT NULL DEFAULT 0,
    credentials_enc TEXT DEFAULT NULL COMMENT 'AES-256-GCM encrypted JSON blob',
    updated_by      BIGINT UNSIGNED DEFAULT NULL,
    updated_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS gateway_transactions (
    id                BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    invoice_id        BIGINT UNSIGNED NOT NULL,
    customer_id       BIGINT UNSIGNED NOT NULL,
    gateway_key       VARCHAR(30) NOT NULL,
    merchant_txn_id   VARCHAR(64) NOT NULL UNIQUE COMMENT 'আমাদের নিজস্ব রেফারেন্স, গেটওয়েতে পাঠানো হয়',
    gateway_txn_id    VARCHAR(100) DEFAULT NULL COMMENT 'paymentID (bKash) / val_id-tran_id (SSLCommerz)',
    amount            DECIMAL(10,2) NOT NULL,
    currency          VARCHAR(10) NOT NULL DEFAULT 'BDT',
    status            ENUM('initiated','pending','completed','failed','cancelled') NOT NULL DEFAULT 'initiated',
    raw_response      JSON DEFAULT NULL,
    reconciled        TINYINT(1) NOT NULL DEFAULT 0 COMMENT '1 হলে payments টেবিলে এন্ট্রি বসে গেছে',
    payment_id        BIGINT UNSIGNED DEFAULT NULL,
    attempts          SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    created_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
    FOREIGN KEY (payment_id) REFERENCES payments(id) ON DELETE SET NULL,
    INDEX idx_status (status),
    INDEX idx_invoice (invoice_id),
    INDEX idx_gateway_txn (gateway_txn_id)
) ENGINE=InnoDB;

-- Phase 12-এ পূর্ণাঙ্গভাবে ব্যবহৃত হয়েছে (Auto Cloud/Hosting Backup)
CREATE TABLE IF NOT EXISTS backup_log (
    id                 BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    type               ENUM('database','files','full') NOT NULL,
    triggered_by       ENUM('cron','manual') NOT NULL DEFAULT 'cron',
    triggered_by_user  BIGINT UNSIGNED DEFAULT NULL,
    file_path          VARCHAR(255) NOT NULL,
    destination        ENUM('local','remote') NOT NULL DEFAULT 'local',
    size_bytes         BIGINT UNSIGNED DEFAULT NULL,
    status             ENUM('success','failed') NOT NULL DEFAULT 'success',
    note               TEXT DEFAULT NULL,
    created_at         DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (triggered_by_user) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_created (created_at)
) ENGINE=InnoDB;

-- =====================================================================
-- Phase 8 — Notification System (SMS/WhatsApp/Email/Telegram, OTP)
-- =====================================================================


-- ---------------------------------------------------------------------
-- Notification Channels — SMS / WhatsApp / Email / Telegram-এর প্রোভাইডার
-- কনফিগ ও ক্রেডেনশিয়াল (payment_gateways-এর মতোই প্যাটার্ন — credentials
-- সবসময় AES-256-GCM এনক্রিপ্টেড, Crypto::encrypt() দিয়ে)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS notification_channels (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    channel_key     VARCHAR(20) NOT NULL UNIQUE COMMENT 'sms | whatsapp | email | telegram',
    provider        VARCHAR(40) NOT NULL COMMENT 'যেমন: generic_http (SMS), whatsapp_cloud, smtp, telegram_bot',
    display_name    VARCHAR(60) NOT NULL,
    is_enabled      TINYINT(1) NOT NULL DEFAULT 0,
    credentials_enc TEXT DEFAULT NULL COMMENT 'AES-256-GCM এনক্রিপ্টেড JSON — API key/token/SMTP pass ইত্যাদি',
    config_json     TEXT DEFAULT NULL COMMENT 'নন-সিক্রেট কনফিগ (from-name, sender-id, admin chat-id ইত্যাদি), প্লেইন JSON',
    updated_by      BIGINT UNSIGNED DEFAULT NULL,
    updated_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

INSERT IGNORE INTO notification_channels (channel_key, provider, display_name, is_enabled) VALUES
    ('sms',      'generic_http',   'SMS গেটওয়ে (HTTP API)',            0),
    ('whatsapp', 'whatsapp_cloud', 'WhatsApp Business Cloud API',       0),
    ('email',    'smtp',           'ইমেইল (SMTP)',                      0),
    ('telegram', 'telegram_bot',   'Telegram Admin Bot',                0);

-- ---------------------------------------------------------------------
-- Notification Templates — প্রতিটা ইভেন্টের জন্য চ্যানেল-ভিত্তিক বার্তা
-- {{placeholder}} সিনট্যাক্স দিয়ে ডাইনামিক ভ্যালু বসে (renderTemplate())
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS notification_templates (
    id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    template_key  VARCHAR(40) NOT NULL COMMENT 'যেমন: bill_reminder, bill_overdue, payment_confirmation, otp, telegram_new_customer',
    channel       ENUM('sms','whatsapp','email','telegram') NOT NULL,
    subject       VARCHAR(150) DEFAULT NULL COMMENT 'শুধু email চ্যানেলের জন্য',
    body_template TEXT NOT NULL,
    is_enabled    TINYINT(1) NOT NULL DEFAULT 1,
    updated_by    BIGINT UNSIGNED DEFAULT NULL,
    updated_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_key_channel (template_key, channel),
    FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

INSERT IGNORE INTO notification_templates (template_key, channel, subject, body_template) VALUES
    ('bill_reminder', 'sms', NULL,
        'প্রিয় {{customer_name}}, আপনার {{billing_month}} মাসের বিল ৳{{payable_amount}} — শেষ তারিখ {{due_date}}। বিল দিতে: {{pay_link}} — {{company_name}}'),
    ('bill_reminder', 'whatsapp', NULL,
        'প্রিয় {{customer_name}},\nআপনার {{billing_month}} মাসের ইন্টারনেট বিল ৳{{payable_amount}} পরিশোধের শেষ তারিখ {{due_date}}।\nঅনলাইনে পরিশোধ করুন: {{pay_link}}\nধন্যবাদান্তে — {{company_name}}'),
    ('bill_reminder', 'email', 'আপনার {{billing_month}} মাসের বিল — {{company_name}}',
        'প্রিয় {{customer_name}},\n\nআপনার ইনভয়েস নং {{invoice_no}} ({{billing_month}}) — পরিশোধযোগ্য পরিমাণ ৳{{payable_amount}}, শেষ তারিখ {{due_date}}।\n\nঅনলাইনে পরিশোধ করতে: {{pay_link}}\n\nধন্যবাদান্তে,\n{{company_name}}'),

    ('bill_overdue', 'sms', NULL,
        'প্রিয় {{customer_name}}, আপনার {{billing_month}} মাসের বিল ৳{{payable_amount}} মেয়াদোত্তীর্ণ (শেষ তারিখ ছিল {{due_date}})। দ্রুত পরিশোধ করুন: {{pay_link}} — {{company_name}}'),
    ('bill_overdue', 'whatsapp', NULL,
        'প্রিয় {{customer_name}},\nআপনার {{billing_month}} মাসের বিল ৳{{payable_amount}} মেয়াদোত্তীর্ণ হয়ে গেছে (শেষ তারিখ ছিল {{due_date}})।\nসংযোগ বিচ্ছিন্ন এড়াতে দ্রুত পরিশোধ করুন: {{pay_link}}\n— {{company_name}}'),
    ('bill_overdue', 'email', 'জরুরি: আপনার বিল মেয়াদোত্তীর্ণ — {{company_name}}',
        'প্রিয় {{customer_name}},\n\nআপনার ইনভয়েস নং {{invoice_no}} ({{billing_month}}) — পরিমাণ ৳{{payable_amount}} মেয়াদোত্তীর্ণ হয়ে গেছে (শেষ তারিখ ছিল {{due_date}})।\n\nএখনই পরিশোধ করুন: {{pay_link}}\n\n{{company_name}}'),

    ('payment_confirmation', 'sms', NULL,
        'প্রিয় {{customer_name}}, আপনার ৳{{amount}} পেমেন্ট গৃহীত হয়েছে (ইনভয়েস {{invoice_no}})। ধন্যবাদ — {{company_name}}'),
    ('payment_confirmation', 'whatsapp', NULL,
        'প্রিয় {{customer_name}},\n✅ আপনার ৳{{amount}} পেমেন্ট সফলভাবে গৃহীত হয়েছে (ইনভয়েস {{invoice_no}}, মাধ্যম: {{method}})।\nধন্যবাদান্তে — {{company_name}}'),
    ('payment_confirmation', 'email', 'পেমেন্ট গৃহীত হয়েছে — {{invoice_no}}',
        'প্রিয় {{customer_name}},\n\nআপনার ৳{{amount}} পরিমাণের পেমেন্ট সফলভাবে গৃহীত হয়েছে।\nইনভয়েস: {{invoice_no}}\nমাধ্যম: {{method}}\n\nধন্যবাদান্তে,\n{{company_name}}'),

    ('otp', 'sms', NULL,
        'আপনার {{company_name}} ভেরিফিকেশন কোড: {{otp_code}} — {{expiry_minutes}} মিনিটের জন্য বৈধ। কারো সাথে শেয়ার করবেন না।'),
    ('otp', 'email', 'আপনার ভেরিফিকেশন কোড — {{company_name}}',
        'আপনার ভেরিফিকেশন কোড: {{otp_code}}\nএই কোডটি {{expiry_minutes}} মিনিটের জন্য বৈধ। কারো সাথে শেয়ার করবেন না।\n\n{{company_name}}'),

    ('telegram_new_customer', 'telegram', NULL,
        '🆕 নতুন গ্রাহক যোগ হয়েছে\nনাম: {{customer_name}}\nফোন: {{phone}}\nকোড: {{customer_code}}\nপ্যাকেজ: {{package_name}}'),
    ('telegram_payment_received', 'telegram', NULL,
        '💰 পেমেন্ট গৃহীত হয়েছে\nগ্রাহক: {{customer_name}} ({{customer_code}})\nপরিমাণ: ৳{{amount}}\nইনভয়েস: {{invoice_no}}\nমাধ্যম: {{method}}'),
    ('telegram_online_payment', 'telegram', NULL,
        '🌐 অনলাইন পেমেন্ট গৃহীত হয়েছে\nগ্রাহক: {{customer_name}} ({{customer_code}})\nপরিমাণ: ৳{{amount}}\nগেটওয়ে: {{gateway}}\nইনভয়েস: {{invoice_no}}');

-- ---------------------------------------------------------------------
-- Notification Queue — প্রতিটা বার্তা এখানে queue হয়, cron
-- (process_notification_queue.php) এসে প্রকৃত send করে। এই টেবিলটাই
-- সেন্ট/ফেইলড বার্তার লগ হিসেবেও কাজ করে (gateway_transactions প্যাটার্ন)।
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS notification_queue (
    id                BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    channel           ENUM('sms','whatsapp','email','telegram') NOT NULL,
    template_key      VARCHAR(40) DEFAULT NULL,
    recipient         VARCHAR(150) NOT NULL COMMENT 'ফোন নম্বর / ইমেইল / টেলিগ্রাম chat_id',
    subject           VARCHAR(150) DEFAULT NULL,
    message           TEXT NOT NULL COMMENT 'রেন্ডার হয়ে যাওয়া চূড়ান্ত বার্তা (রিসেন্ডে আবার রেন্ডার করতে হয় না)',
    related_type      VARCHAR(30) DEFAULT NULL COMMENT 'customer | invoice | payment | otp | system',
    related_id        BIGINT UNSIGNED DEFAULT NULL,
    status            ENUM('pending','sent','failed','cancelled') NOT NULL DEFAULT 'pending',
    attempts          SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    max_attempts      SMALLINT UNSIGNED NOT NULL DEFAULT 3,
    last_error        VARCHAR(500) DEFAULT NULL,
    provider_response TEXT DEFAULT NULL,
    scheduled_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    sent_at           DATETIME DEFAULT NULL,
    created_by        BIGINT UNSIGNED DEFAULT NULL COMMENT 'NULL মানে সিস্টেম/cron স্বয়ংক্রিয়ভাবে queue করেছে',
    created_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_status_sched (status, scheduled_at),
    INDEX idx_related (related_type, related_id),
    INDEX idx_channel (channel)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- OTP Codes — Customer Portal Login (Phase 9), Admin অ্যাকশন ভেরিফিকেশন
-- ইত্যাদির জন্য পুনঃব্যবহারযোগ্য OTP ইঞ্জিন
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS otp_codes (
    id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    purpose       VARCHAR(40) NOT NULL COMMENT 'যেমন: customer_portal_login, password_reset, admin_action',
    identifier    VARCHAR(150) NOT NULL COMMENT 'ফোন নম্বর বা ইমেইল',
    channel       ENUM('sms','email') NOT NULL DEFAULT 'sms',
    code_hash     VARCHAR(255) NOT NULL COMMENT 'OTP প্লেইনটেক্সটে কখনো সংরক্ষণ হয় না, শুধু hash',
    attempts      TINYINT UNSIGNED NOT NULL DEFAULT 0,
    max_attempts  TINYINT UNSIGNED NOT NULL DEFAULT 5,
    ip_address    VARCHAR(45) DEFAULT NULL,
    verified_at   DATETIME DEFAULT NULL,
    expires_at    DATETIME NOT NULL,
    created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_identifier_purpose (identifier, purpose),
    INDEX idx_expires (expires_at)
) ENGINE=InnoDB;

-- =====================================================================
-- Phase 9 — Customer Self-service Portal (package requests, tickets, speed test)
-- =====================================================================


-- ---------------------------------------------------------------------
-- Package Change Requests — গ্রাহক পোর্টাল থেকে প্যাকেজ আপগ্রেড/ডাউনগ্রেড
-- চাইলে এখানে জমা হয়, অ্যাডমিন Approve/Reject করে
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS package_change_requests (
    id                    BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    customer_id           BIGINT UNSIGNED NOT NULL,
    current_package_id    INT UNSIGNED DEFAULT NULL,
    requested_package_id  INT UNSIGNED NOT NULL,
    customer_note         VARCHAR(300) DEFAULT NULL,
    admin_note            VARCHAR(300) DEFAULT NULL,
    status                ENUM('pending','approved','rejected','cancelled') NOT NULL DEFAULT 'pending',
    reviewed_by           BIGINT UNSIGNED DEFAULT NULL,
    reviewed_at           DATETIME DEFAULT NULL,
    created_at            DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
    FOREIGN KEY (current_package_id) REFERENCES packages(id) ON DELETE SET NULL,
    FOREIGN KEY (requested_package_id) REFERENCES packages(id) ON DELETE CASCADE,
    FOREIGN KEY (reviewed_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_customer (customer_id),
    INDEX idx_status (status)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- Support Tickets — গ্রাহক পোর্টাল থেকে সাপোর্ট টিকেট (Live Chat-এর
-- সরল সংস্করণ হিসেবে থ্রেডেড মেসেজিং)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS support_tickets (
    id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ticket_no     VARCHAR(20) NOT NULL UNIQUE COMMENT 'যেমন: TKT-000123',
    customer_id   BIGINT UNSIGNED NOT NULL,
    subject       VARCHAR(150) NOT NULL,
    category      ENUM('connectivity','billing','technical','other') NOT NULL DEFAULT 'other',
    priority      ENUM('low','normal','high') NOT NULL DEFAULT 'normal',
    status        ENUM('open','in_progress','resolved','closed') NOT NULL DEFAULT 'open',
    assigned_to   BIGINT UNSIGNED DEFAULT NULL,
    created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    closed_at     DATETIME DEFAULT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
    FOREIGN KEY (assigned_to) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_customer (customer_id),
    INDEX idx_status (status)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS ticket_messages (
    id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ticket_id    BIGINT UNSIGNED NOT NULL,
    sender_type  ENUM('customer','admin') NOT NULL,
    sender_id    BIGINT UNSIGNED DEFAULT NULL COMMENT 'customer_id বা users.id, sender_type অনুযায়ী',
    message      TEXT NOT NULL,
    created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (ticket_id) REFERENCES support_tickets(id) ON DELETE CASCADE,
    INDEX idx_ticket (ticket_id)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- Speed Test Results — পোর্টাল থেকে চালানো রিয়েল-টাইম স্পিড টেস্টের ইতিহাস
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS speed_test_results (
    id             BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    customer_id    BIGINT UNSIGNED NOT NULL,
    download_mbps  DECIMAL(8,2) DEFAULT NULL,
    upload_mbps    DECIMAL(8,2) DEFAULT NULL,
    ping_ms        INT UNSIGNED DEFAULT NULL,
    ip_address     VARCHAR(45) DEFAULT NULL,
    tested_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
    INDEX idx_customer_time (customer_id, tested_at)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- Permissions — admin ভূমিকাতেও টিকেট ম্যানেজমেন্টের অ্যাক্সেস যোগ করা হলো
-- (support role-এর আগে থেকেই tickets.manage আছে, seed.sql দ্রষ্টব্য)
-- ---------------------------------------------------------------------
INSERT IGNORE INTO permissions (role, module_key, allowed) VALUES
    ('admin', 'tickets.manage', 1);

-- ---------------------------------------------------------------------
-- notification_templates — Phase 9-এর নতুন ইভেন্টের জন্য টেমপ্লেট যোগ
-- ---------------------------------------------------------------------
INSERT IGNORE INTO notification_templates (template_key, channel, subject, body_template) VALUES
    ('ticket_created', 'sms', NULL,
        'প্রিয় {{customer_name}}, আপনার সাপোর্ট টিকেট #{{ticket_no}} গৃহীত হয়েছে। শীঘ্রই আমাদের টিম যোগাযোগ করবে — {{company_name}}'),
    ('ticket_reply', 'sms', NULL,
        'প্রিয় {{customer_name}}, আপনার টিকেট #{{ticket_no}}-এ নতুন রিপ্লাই এসেছে। বিস্তারিত দেখতে পোর্টালে লগইন করুন — {{company_name}}'),
    ('ticket_reply', 'email', 'আপনার টিকেট #{{ticket_no}}-এ নতুন রিপ্লাই',
        'প্রিয় {{customer_name}},\n\nআপনার সাপোর্ট টিকেট #{{ticket_no}} ({{subject}})-এ নতুন রিপ্লাই এসেছে:\n\n"{{reply_preview}}"\n\nপুরো কথোপকথন দেখতে গ্রাহক পোর্টালে লগইন করুন।\n\n{{company_name}}'),
    ('package_change_approved', 'sms', NULL,
        'প্রিয় {{customer_name}}, আপনার প্যাকেজ পরিবর্তনের অনুরোধ ({{package_name}}) অনুমোদিত হয়েছে। ধন্যবাদ — {{company_name}}'),
    ('package_change_rejected', 'sms', NULL,
        'প্রিয় {{customer_name}}, দুঃখিত, আপনার প্যাকেজ পরিবর্তনের অনুরোধ অনুমোদিত হয়নি। কারণ: {{reason}} — {{company_name}}'),
    ('telegram_new_ticket', 'telegram', NULL,
        '🎫 নতুন সাপোর্ট টিকেট\nগ্রাহক: {{customer_name}} ({{customer_code}})\nবিষয়: {{subject}}\nক্যাটাগরি: {{category}}\nটিকেট: #{{ticket_no}}'),
    ('telegram_package_change_request', 'telegram', NULL,
        '📦 প্যাকেজ পরিবর্তনের অনুরোধ\nগ্রাহক: {{customer_name}} ({{customer_code}})\nবর্তমান: {{current_package}}\nচাহিদা: {{requested_package}}');

SET FOREIGN_KEY_CHECKS = 1;