-- Facebook Social Media Database Schema
-- Separate tables for credentials, tokens, pages, and Instagram accounts

-- 1. Facebook App Credentials Table
CREATE TABLE facebook_credentials (
    id INT AUTO_INCREMENT PRIMARY KEY,
    app_id VARCHAR(50) NOT NULL UNIQUE,
    app_secret VARCHAR(100) NOT NULL,
    bootstrap_token TEXT NULL,
    has_bootstrap_token BOOLEAN DEFAULT FALSE,
    config_file VARCHAR(100) DEFAULT 'credentials.php',
    status ENUM('active', 'inactive', 'suspended') DEFAULT 'active',
    data JSON NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_app_id (app_id),
    INDEX idx_status (status)
);

-- 2. Facebook User Tokens Table
CREATE TABLE facebook_user_tokens (
    id INT AUTO_INCREMENT PRIMARY KEY,
    app_id VARCHAR(50) NOT NULL,
    user_token TEXT NULL,
    token_expires_at INT NULL,
    token_expires_date DATETIME NULL,
    is_expired BOOLEAN DEFAULT FALSE,
    days_until_expiry INT NULL,
    has_token BOOLEAN DEFAULT FALSE,
    token_type ENUM('short_lived', 'long_lived') DEFAULT 'long_lived',
    status ENUM('active', 'expired', 'no_token') DEFAULT 'no_token',
    data JSON NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (app_id) REFERENCES facebook_credentials(app_id) ON DELETE CASCADE,
    INDEX idx_app_id (app_id),
    INDEX idx_expires_at (token_expires_at),
    INDEX idx_status (status)
);

-- 3. Facebook Pages Table
CREATE TABLE facebook_pages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    app_id VARCHAR(50) NOT NULL,
    page_id VARCHAR(50) NOT NULL,
    page_name VARCHAR(255) NOT NULL,
    page_category VARCHAR(100) NULL,
    business_id VARCHAR(50) NULL,
    has_access_token BOOLEAN DEFAULT FALSE,
    updated_at INT NULL,
    updated_date DATETIME NULL,
    status ENUM('active', 'no_token', 'inactive') DEFAULT 'no_token',
    data JSON NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (app_id) REFERENCES facebook_credentials(app_id) ON DELETE CASCADE,
    UNIQUE KEY unique_page (app_id, page_id),
    INDEX idx_app_id (app_id),
    INDEX idx_page_id (page_id),
    INDEX idx_status (status)
);

-- 4. Instagram Accounts Table
CREATE TABLE instagram_accounts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    app_id VARCHAR(50) NOT NULL,
    instagram_id VARCHAR(50) NOT NULL,
    username VARCHAR(100) NOT NULL,
    account_type ENUM('PERSONAL', 'BUSINESS', 'CREATOR') DEFAULT 'BUSINESS',
    followers_count INT DEFAULT 0,
    media_count INT DEFAULT 0,
    linked_page_id VARCHAR(50) NULL,
    linked_page_name VARCHAR(255) NULL,
    linked_page_category VARCHAR(100) NULL,
    business_id VARCHAR(50) NULL,
    has_access_token BOOLEAN DEFAULT FALSE,
    updated_at INT NULL,
    updated_date DATETIME NULL,
    status ENUM('active', 'no_token', 'inactive') DEFAULT 'no_token',
    data JSON NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (app_id) REFERENCES facebook_credentials(app_id) ON DELETE CASCADE,
    UNIQUE KEY unique_instagram (app_id, instagram_id),
    INDEX idx_app_id (app_id),
    INDEX idx_instagram_id (instagram_id),
    INDEX idx_username (username),
    INDEX idx_status (status)
);

-- 5. Facebook Connection Status Table
CREATE TABLE facebook_status (
    id INT AUTO_INCREMENT PRIMARY KEY,
    app_id VARCHAR(50) NOT NULL,
    connected BOOLEAN DEFAULT FALSE,
    has_user_token BOOLEAN DEFAULT FALSE,
    is_expired BOOLEAN DEFAULT FALSE,
    pages_count INT DEFAULT 0,
    instagram_count INT DEFAULT 0,
    token_expires_at INT NULL,
    token_expires_date DATETIME NULL,
    days_until_expiry INT NULL,
    status ENUM('ok', 'needs_refresh', 'no_token', 'expired', 'no_pages') DEFAULT 'needs_refresh',
    message TEXT NULL,
    data JSON NULL,
    checked_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (app_id) REFERENCES facebook_credentials(app_id) ON DELETE CASCADE,
    INDEX idx_app_id (app_id),
    INDEX idx_status (status),
    INDEX idx_checked_at (checked_at)
);

-- 6. Facebook Posting Log Table (optional)
CREATE TABLE facebook_posting_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    app_id VARCHAR(50) NOT NULL,
    platform ENUM('facebook', 'instagram') NOT NULL,
    target_id VARCHAR(50) NOT NULL,
    target_name VARCHAR(255) NULL,
    message TEXT NULL,
    image_url VARCHAR(500) NULL,
    post_id VARCHAR(100) NULL,
    status ENUM('success', 'failed', 'pending') DEFAULT 'pending',
    error_message TEXT NULL,
    response_data JSON NULL,
    posted_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (app_id) REFERENCES facebook_credentials(app_id) ON DELETE CASCADE,
    INDEX idx_app_id (app_id),
    INDEX idx_platform (platform),
    INDEX idx_status (status),
    INDEX idx_posted_at (posted_at)
);

-- Example queries for data insertion:

-- Insert credentials
/*
INSERT INTO facebook_credentials (app_id, app_secret, bootstrap_token, has_bootstrap_token, data) 
VALUES (?, ?, ?, ?, ?)
*/

-- Insert user tokens
/*
INSERT INTO facebook_user_tokens (app_id, user_token, token_expires_at, token_expires_date, is_expired, days_until_expiry, has_token, status, data) 
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
*/

-- Insert pages
/*
INSERT INTO facebook_pages (app_id, page_id, page_name, page_category, business_id, has_access_token, updated_at, updated_date, status, data) 
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
*/

-- Insert Instagram accounts
/*
INSERT INTO instagram_accounts (app_id, instagram_id, username, account_type, followers_count, media_count, linked_page_id, linked_page_name, linked_page_category, business_id, has_access_token, updated_at, updated_date, status, data) 
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
*/

-- Insert status
/*
INSERT INTO facebook_status (app_id, connected, has_user_token, is_expired, pages_count, instagram_count, token_expires_at, token_expires_date, days_until_expiry, status, message, data) 
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
*/

-- Insert posting log
/*
INSERT INTO facebook_posting_log (app_id, platform, target_id, target_name, message, image_url, post_id, status, error_message, response_data, posted_at) 
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
*/
