-- =====================================================================
-- Phase 9 — Customer Self-service Portal — Migration
-- আগের schema.sql-এর উপর এই ফাইলটা আলাদাভাবে রান করুন (existing DB-তে):
--   mysql -u isp_app -p ispbdcom_isp_billing < database/migration_phase9.sql
-- নতুন ইনস্টলে schema.sql-এর সাথে এটা ইতিমধ্যে merge করা আছে।
-- =====================================================================

USE ispbdcom_isp_billing;

-- ---------------------------------------------------------------------
-- 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}}');