-- =====================================================================
-- Phase 11 — NOC Dashboard & Monitoring — Migration
-- আগের DB-তে রান করুন: mysql -u isp_app -p ispbdcom_isp_billing < database/migration_phase11.sql
-- নতুন ইনস্টলে schema.sql-এর সাথে এটা ইতিমধ্যে merge করা আছে।
--
-- এই ফেজে যোগ হলো:
--   router_status_log  — প্রতিটা পোলের রেজাল্ট (আপ/ডাউন + লেটেন্সি) টাইম-সিরিজ হিসেবে জমা
--   router_outages     — অনলাইন→অফলাইন ট্রানজিশন থেকে outage window ট্র্যাক করে,
--                        রিকভারি হলে duration হিসাব করে বন্ধ হয়
--   notification_templates — router_offline/router_online টেলিগ্রাম অ্যালার্ট
--   system_settings     — NOC পোলিং ইন্টারভ্যাল (মিনিট)
-- =====================================================================

USE ispbdcom_isp_billing;

CREATE TABLE IF NOT EXISTS router_status_log (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    router_id   INT UNSIGNED NOT NULL,
    status      ENUM('online','offline') NOT NULL,
    latency_ms  INT UNSIGNED DEFAULT NULL COMMENT 'RouterOS API লগইন+identity রাউন্ড-ট্রিপ টাইম',
    error_note  VARCHAR(255) DEFAULT NULL COMMENT 'অফলাইন হলে সংক্ষিপ্ত কারণ (connection refused ইত্যাদি)',
    checked_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (router_id) REFERENCES mikrotik_routers(id) ON DELETE CASCADE,
    INDEX idx_router_time (router_id, checked_at)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS router_outages (
    id                   BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    router_id            INT UNSIGNED NOT NULL,
    started_at           DATETIME NOT NULL,
    ended_at             DATETIME DEFAULT NULL COMMENT 'NULL = এখনো চলমান আউটেজ',
    duration_seconds     INT UNSIGNED DEFAULT NULL,
    alert_sent           TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'ডাউন হওয়ার টেলিগ্রাম অ্যালার্ট পাঠানো হয়েছে কিনা',
    recovery_alert_sent  TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'রিকভারির টেলিগ্রাম অ্যালার্ট পাঠানো হয়েছে কিনা',
    created_at           DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (router_id) REFERENCES mikrotik_routers(id) ON DELETE CASCADE,
    INDEX idx_router_open (router_id, ended_at)
) ENGINE=InnoDB;

INSERT IGNORE INTO notification_templates (template_key, channel, subject, body_template) VALUES
    ('router_offline', 'telegram', NULL,
        '🔴 রাউটার ডাউন — {{router_name}}\nHost: {{host}}\nএরিয়া: {{location}}\nসময়: {{detected_at}}\nকারণ: {{error_note}}'),
    ('router_online', 'telegram', NULL,
        '🟢 রাউটার পুনরায় সচল — {{router_name}}\nHost: {{host}}\nএরিয়া: {{location}}\nডাউনটাইম স্থায়ী ছিল: {{downtime_readable}}');

INSERT IGNORE INTO system_settings (setting_key, setting_value) VALUES
    ('noc_poll_interval_minutes', '5');