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

USE ispbdcom_isp_billing;

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