-- ================================================================
-- DB UPDATE: System User Token support add karo
-- Agar pehle se db_schema.sql run kar chuke ho toh
-- sirf yeh ALTER query run karo phpMyAdmin mein
-- ================================================================

USE fb_token_manager;

-- token_type mein 'system_user' add karo
ALTER TABLE fb_page_tokens
MODIFY COLUMN token_type ENUM('short','long','system_user') DEFAULT 'long';

-- expires_at ko NULL allow karo (system user = never expires)
ALTER TABLE fb_page_tokens
MODIFY COLUMN expires_at DATETIME NULL DEFAULT NULL;

-- ================================================================
-- FRESH INSTALL ke liye pura schema (pehli baar chal rahe ho toh)
-- ================================================================

CREATE TABLE IF NOT EXISTS fb_page_tokens (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    page_id         VARCHAR(50)   NOT NULL UNIQUE,
    page_name       VARCHAR(255)  NOT NULL,
    access_token    TEXT          NOT NULL,
    token_type      ENUM('short','long','system_user') DEFAULT 'long',
    expires_at      DATETIME      NULL DEFAULT NULL,   -- NULL = never expires
    last_refreshed  DATETIME      DEFAULT CURRENT_TIMESTAMP,
    is_active       TINYINT(1)    DEFAULT 1,
    created_at      DATETIME      DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME      DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS fb_alert_log (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    page_id     VARCHAR(50)  NOT NULL,
    alert_type  ENUM('email','whatsapp','both') DEFAULT 'both',
    message     TEXT,
    sent_at     DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS fb_token_history (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    page_id         VARCHAR(50)  NOT NULL,
    old_expires_at  DATETIME     NULL,
    new_expires_at  DATETIME     NULL,
    refresh_method  ENUM('manual','cron','oauth','system_user') DEFAULT 'manual',
    refreshed_at    DATETIME     DEFAULT CURRENT_TIMESTAMP,
    status          ENUM('success','failed') DEFAULT 'success',
    notes           TEXT
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS fb_settings (
    setting_key     VARCHAR(100) PRIMARY KEY,
    setting_value   TEXT,
    updated_at      DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

INSERT IGNORE INTO fb_settings (setting_key, setting_value) VALUES
('alert_email',         'admin@yourdomain.com'),
('alert_whatsapp',      '9999999999'),
('alert_days_before',   '15'),
('whatsapp_provider',   'fast2sms'),
('fast2sms_api_key',    ''),
('fb_app_id',           ''),
('fb_app_secret',       '');

CREATE TABLE IF NOT EXISTS fb_activity_log (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    action      VARCHAR(255),
    page_id     VARCHAR(50) NULL,
    details     TEXT,
    status      ENUM('success','warning','error','info') DEFAULT 'info',
    created_at  DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;