-- ============================================
-- 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 1: Page tokens
-- ----------------------------------------
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') DEFAULT 'long',
    expires_at      DATETIME      NULL,
    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;

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

-- ----------------------------------------
-- Insert your existing 3 pages as sample data
-- (Update tokens and expiry dates accordingly)
-- ----------------------------------------
INSERT IGNORE INTO fb_page_tokens (page_id, page_name, access_token, token_type, expires_at) VALUES
('1015271311660553', 'BTS DMXD',          'EAAX1yOqyzxoBRKO...', 'long', DATE_ADD(NOW(), INTERVAL 8 DAY)),
('114394455048565',  'Digital Marketing XD','EAAX1yOqyzxoBRGl...', 'long', DATE_ADD(NOW(), INTERVAL 52 DAY)),
('1116790168394635', 'Nirala Trio',        'EAAX1yOqyzxoBREf...', 'long', DATE_ADD(NOW(), INTERVAL 44 DAY));