-- ============================================
-- FB TOKEN MANAGER — DATABASE SCHEMA
-- Run this in phpMyAdmin or MySQL terminal
-- ============================================

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

-- ----------------------------------------
-- TABLE 2: Alert log (kab alert gaya)
-- ----------------------------------------
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;

-- ----------------------------------------
-- TABLE 3: Token refresh history
-- ----------------------------------------
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') DEFAULT 'manual',
    refreshed_at    DATETIME     DEFAULT CURRENT_TIMESTAMP,
    status          ENUM('success','failed') DEFAULT 'success',
    notes           TEXT
) ENGINE=InnoDB;

-- ----------------------------------------
-- TABLE 4: App settings (email, WhatsApp)
-- ----------------------------------------
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;

-- Default settings
INSERT IGNORE INTO fb_settings (setting_key, setting_value) VALUES
('alert_email',         'admin@yourdomain.com'),
('alert_whatsapp',      '+91-9999999999'),
('alert_days_before',   '15'),
('whatsapp_provider',   'fast2sms'),
('fast2sms_api_key',    'YOUR_FAST2SMS_KEY'),
('twilio_sid',          ''),
('twilio_token',        ''),
('twilio_from',         ''),
('fb_app_id',           'YOUR_FB_APP_ID'),
('fb_app_secret',       'YOUR_FB_APP_SECRET'),
('admin_password',      '$2y$10$examplehashedpassword'),
('admin_username',      'admin');

-- ----------------------------------------
-- TABLE 5: Activity log (cron, refresh, etc.)
-- ----------------------------------------
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;
