-- =====================================================================
-- FUTURE LEADERS SCHOOLS — AI COMMUNICATIONS & MARKETING CONTROL CENTER
-- Database schema (MySQL 5.7+/8.0, InnoDB, utf8mb4)
-- =====================================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ---------------------------------------------------------------------
-- admins
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS admins (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(100) NOT NULL UNIQUE,
    pin_hash VARCHAR(255) NOT NULL,
    email VARCHAR(190) NULL,
    failed_attempts INT UNSIGNED NOT NULL DEFAULT 0,
    locked_until DATETIME NULL,
    last_login_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- settings  (key/value general settings store)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS settings (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) NOT NULL UNIQUE,
    setting_value TEXT NULL,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- api_credentials (Gemini / Facebook / BulkSMS Nigeria — encrypted secrets)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS api_credentials (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    provider VARCHAR(50) NOT NULL UNIQUE, -- gemini | facebook | bulksmsnigeria
    credentials_json TEXT NOT NULL,       -- encrypted JSON blob
    is_enabled TINYINT(1) NOT NULL DEFAULT 1,
    last_tested_at DATETIME NULL,
    last_test_result VARCHAR(20) NULL,    -- CONNECTED | FAILED | NOT_CONFIGURED
    last_test_message VARCHAR(255) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- parent_numbers
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS parent_numbers (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    phone_number VARCHAR(20) NOT NULL UNIQUE,
    country_code VARCHAR(5) NOT NULL DEFAULT '234',
    status ENUM('active','inactive','invalid') NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    last_sms_sent_at DATETIME NULL,
    sms_count INT UNSIGNED NOT NULL DEFAULT 0,
    INDEX idx_phone_number (phone_number),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- sms_campaigns
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS sms_campaigns (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    message TEXT NOT NULL,
    media_path VARCHAR(255) NULL,
    media_type VARCHAR(50) NULL, -- image|video|audio|document (stored only; not sent via SMS)
    frequency ENUM('once','hourly','every_3_days','weekly','monthly','custom') NOT NULL DEFAULT 'once',
    custom_interval_minutes INT UNSIGNED NULL,
    start_at DATETIME NOT NULL,
    end_at DATETIME NULL,
    next_run_at DATETIME NULL,
    status ENUM('active','paused','completed','cancelled') NOT NULL DEFAULT 'active',
    created_by INT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    last_run_at DATETIME NULL,
    last_execution_id VARCHAR(64) NULL,
    total_recipients INT UNSIGNED NOT NULL DEFAULT 0,
    total_sent INT UNSIGNED NOT NULL DEFAULT 0,
    total_failed INT UNSIGNED NOT NULL DEFAULT 0,
    INDEX idx_next_run_at (next_run_at),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- sms_logs
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS sms_logs (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    campaign_id INT UNSIGNED NULL,
    phone_number VARCHAR(20) NOT NULL,
    message TEXT NOT NULL,
    provider VARCHAR(50) NOT NULL DEFAULT 'bulksmsnigeria',
    provider_message_id VARCHAR(100) NULL,
    status ENUM('sent','failed','pending') NOT NULL DEFAULT 'pending',
    error_message VARCHAR(255) NULL,
    sent_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_campaign_id (campaign_id),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at),
    CONSTRAINT fk_sms_logs_campaign FOREIGN KEY (campaign_id) REFERENCES sms_campaigns(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- facebook_posts
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS facebook_posts (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    facebook_post_id VARCHAR(100) NULL,
    caption TEXT NOT NULL,
    media_path VARCHAR(255) NULL,
    content_category VARCHAR(100) NULL,
    ai_reasoning TEXT NULL,
    source ENUM('ai_general','custom') NOT NULL DEFAULT 'ai_general',
    ai_run_id INT UNSIGNED NULL,
    custom_post_id INT UNSIGNED NULL,
    scheduled_at DATETIME NULL,
    published_at DATETIME NULL,
    status ENUM('pending','scheduled','published','failed','cancelled') NOT NULL DEFAULT 'pending',
    error_message VARCHAR(255) NULL,
    performance_score DECIMAL(10,4) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_status (status),
    INDEX idx_scheduled_at (scheduled_at),
    INDEX idx_facebook_post_id (facebook_post_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- facebook_metrics
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS facebook_metrics (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    facebook_post_id_ref INT UNSIGNED NOT NULL,
    reach INT UNSIGNED NULL,
    impressions INT UNSIGNED NULL,
    likes INT UNSIGNED NULL,
    comments INT UNSIGNED NULL,
    shares INT UNSIGNED NULL,
    clicks INT UNSIGNED NULL,
    engagement_rate DECIMAL(6,3) NULL,
    fetched_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_fb_metrics_post FOREIGN KEY (facebook_post_id_ref) REFERENCES facebook_posts(id) ON DELETE CASCADE,
    INDEX idx_fb_post_ref (facebook_post_id_ref)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- scheduled_tasks (generic scheduler queue: facebook_post | sms_campaign)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS scheduled_tasks (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    task_type ENUM('facebook_post','sms_campaign','facebook_metrics_refresh') NOT NULL,
    reference_id INT UNSIGNED NOT NULL,
    scheduled_at DATETIME NOT NULL,
    status ENUM('pending','processing','completed','failed','cancelled') NOT NULL DEFAULT 'pending',
    attempts INT UNSIGNED NOT NULL DEFAULT 0,
    last_error VARCHAR(255) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_scheduled_at (scheduled_at),
    INDEX idx_status (status),
    INDEX idx_task_type (task_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- ai_instructions (permanent general instruction, versioned)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS ai_instructions (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    instruction_text MEDIUMTEXT NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- ai_runs
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS ai_runs (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    started_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ended_at DATETIME NULL,
    status ENUM('running','completed','failed') NOT NULL DEFAULT 'running',
    instruction_used MEDIUMTEXT NULL,
    posts_generated INT UNSIGNED NOT NULL DEFAULT 0,
    posts_published INT UNSIGNED NOT NULL DEFAULT 0,
    posts_scheduled INT UNSIGNED NOT NULL DEFAULT 0,
    errors TEXT NULL,
    fallback_used TINYINT(1) NOT NULL DEFAULT 0,
    ai_summary MEDIUMTEXT NULL,
    triggered_by INT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_status (status),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- custom_posts
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS custom_posts (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    instruction TEXT NOT NULL,
    generated_caption MEDIUMTEXT NULL,
    generated_media_path VARCHAR(255) NULL,
    status ENUM('draft','approved','published','cancelled') NOT NULL DEFAULT 'draft',
    facebook_post_id_ref INT UNSIGNED NULL,
    created_by INT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- activity_logs
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS activity_logs (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    action VARCHAR(100) NOT NULL,
    description TEXT NULL,
    admin_id INT UNSIGNED NULL,
    ip_address VARCHAR(45) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_action (action),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS = 1;

-- =====================================================================
-- SEED DATA
-- =====================================================================

-- Default admin — username: admin / PIN: 2010 (hashed at install time by install.php,
-- this literal hash below is a placeholder and gets overwritten by install.php).
INSERT INTO admins (username, pin_hash, email) VALUES
('admin', '$2y$10$PLACEHOLDERPLACEHOLDERPLACEHOLDERPLACEHOLDERPLACEHOLD', 'admin@futureleadersschools.example')
ON DUPLICATE KEY UPDATE username = username;

-- Default general settings
INSERT INTO settings (setting_key, setting_value) VALUES
('school_name', 'Future Leaders Schools'),
('school_location', 'Are, Ekiti State, Nigeria'),
('brand_color', '#1E7B34'),
('timezone', 'Africa/Lagos'),
('admin_email', 'admin@futureleadersschools.example'),
('default_sms_sender_id', 'FLSchools'),
('default_gemini_text_model', 'gemini-3.1-flash-lite'),
('default_gemini_image_model', 'gemini-3.1-flash-image'),
('max_ai_posts_per_trigger', '4'),
('max_retry_count', '3'),
('ai_enabled', '1'),
('sms_enabled', '1'),
('facebook_enabled', '1'),
('bus_routes', 'Afao Ekiti, Uso Ekiti, Iworoko Ekiti'),
('classes_offered', 'Creche - SSS3')
ON DUPLICATE KEY UPDATE setting_key = setting_key;

-- Default permanent AI instruction
INSERT INTO ai_instructions (instruction_text, is_active) VALUES
('You are the autonomous social media marketing manager for Future Leaders Schools, a trusted Nigerian school in Are, Ekiti State offering education from Creche through SSS3, with a school bus service covering Afao Ekiti, Uso Ekiti and Iworoko Ekiti. Your job is to plan and publish Facebook content that builds parent trust, showcases academic excellence and student development, and generates admissions enquiries — without ever inventing facts, awards, results, testimonials or statistics that are not verified. Balance educational tips, parent advice, school life, exam preparation, admissions, character development and trust-building content. Do not make every post an advertisement. Depict Nigerian children and staff respectfully, realistically and age-appropriately.', 1)
ON DUPLICATE KEY UPDATE instruction_text = instruction_text;