-- packages/db/schema.sql

-- ==========================================
-- 1. USER MANAGEMENT & AUTHENTICATION
-- ==========================================

CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    email TEXT UNIQUE NOT NULL,
    password_hash TEXT NOT NULL,
    full_name TEXT NOT NULL,
    company TEXT,
    phone TEXT,
    role TEXT DEFAULT 'user',
    is_active BOOLEAN DEFAULT 1,
    credits INTEGER DEFAULT 0,
    plan TEXT DEFAULT 'free',
    daily_credits INTEGER DEFAULT 5,
    credits_reset_date TEXT DEFAULT CURRENT_DATE,
    searches_count INTEGER DEFAULT 0,
    leads_viewed INTEGER DEFAULT 0,
    bookmarks_count INTEGER DEFAULT 0,
    stripe_customer_id TEXT,
    subscription_id TEXT,
    subscription_status TEXT DEFAULT 'inactive',
    current_period_end TEXT,
    last_login TEXT NULL,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
    updated_at TEXT DEFAULT CURRENT_TIMESTAMP
);

-- ==========================================
-- 2. DEMAND CHANNELS & SCALING ENGINE
-- ==========================================

CREATE TABLE IF NOT EXISTS pure_demand_channels (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    engine_type TEXT NOT NULL,
    target_url TEXT NOT NULL,
    extraction_config TEXT NULL,
    interval_minutes INTEGER DEFAULT 60,
    is_active BOOLEAN DEFAULT 1,
    failure_count INTEGER DEFAULT 0,
    last_run_at TEXT NULL,
    next_run_at TEXT DEFAULT CURRENT_TIMESTAMP,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
    updated_at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS channel_execution_logs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    channel_id INTEGER NOT NULL,
    execution_status TEXT NOT NULL,
    leads_found INTEGER DEFAULT 0,
    error_message TEXT NULL,
    execution_duration_ms INTEGER DEFAULT 0,
    executed_at TEXT DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (channel_id) REFERENCES pure_demand_channels(id) ON DELETE CASCADE
);

-- ==========================================
-- 3. CORE INGESTION & INTENT TRACKING
-- ==========================================

CREATE TABLE IF NOT EXISTS leads (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    description TEXT,
    service_needed TEXT,
    location TEXT,
    contact_phone TEXT,
    contact_email TEXT,
    contact_whatsapp TEXT,
    source_url TEXT UNIQUE,
    source_type TEXT,
    urgency_level TEXT DEFAULT 'medium',
    urgency_keywords TEXT,
    budget_hint TEXT,
    confidence_score INTEGER DEFAULT 50,
    score INTEGER DEFAULT 0,
    score_category TEXT DEFAULT 'warm',
    is_verified BOOLEAN DEFAULT 0,
    is_active BOOLEAN DEFAULT 1,
    posted_date TEXT,
    discovered_at TEXT DEFAULT CURRENT_TIMESTAMP,
    last_checked_at TEXT NULL,
    times_displayed INTEGER DEFAULT 0,
    times_contacted INTEGER DEFAULT 0
);

-- ==========================================
-- 4. BOOKMARKS & SEARCH AUDITS
-- ==========================================

CREATE TABLE IF NOT EXISTS bookmark_folders (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    name TEXT NOT NULL,
    color TEXT DEFAULT '#6B7280',
    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE(user_id, name)
);

CREATE TABLE IF NOT EXISTS bookmarks (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    lead_id INTEGER NOT NULL,
    notes TEXT,
    folder TEXT DEFAULT 'default',
    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
    updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (lead_id) REFERENCES leads(id) ON DELETE CASCADE,
    UNIQUE(user_id, lead_id)
);

CREATE TABLE IF NOT EXISTS search_history (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    query TEXT NOT NULL,
    results_count INTEGER DEFAULT 0,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- ==========================================
-- 5. ADMINISTRATIVE & FEEDBACK SYSTEMS
-- ==========================================

CREATE TABLE IF NOT EXISTS system_settings (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    setting_key TEXT UNIQUE NOT NULL,
    setting_value TEXT,
    setting_type TEXT DEFAULT 'string',
    description TEXT,
    updated_by INTEGER,
    updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (updated_by) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS admin_activity_log (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    admin_id INTEGER NOT NULL,
    action TEXT NOT NULL,
    target_type TEXT,
    target_id INTEGER,
    details TEXT,
    ip_address TEXT,
    user_agent TEXT,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (admin_id) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS signal_feedback (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    lead_id INTEGER NOT NULL,
    user_id INTEGER NOT NULL,
    quality_score INTEGER CHECK (quality_score BETWEEN 1 AND 5),
    is_relevant BOOLEAN,
    notes TEXT,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (lead_id) REFERENCES leads(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE(user_id, lead_id)
);

-- ==========================================
-- 6. SYSTEM SEED CONFIGURATIONS
-- ==========================================

INSERT OR IGNORE INTO users (email, password_hash, full_name, role, is_active, plan, daily_credits) VALUES 
('admin@afriintel.com', '$2b$10$YourHashedPasswordHere', 'System Administrator', 'super_admin', 1, 'pro', 999999);

INSERT OR IGNORE INTO system_settings (setting_key, setting_value, setting_type, description) VALUES
('scrape_enabled', 'true', 'boolean', 'Enable/disable scraping'),
('max_daily_scrapes', '500', 'integer', 'Maximum scrapes per day allowed across channels'),
('score_freshness_weight', '30', 'integer', 'Weight for freshness in scoring'),
('score_clarity_weight', '25', 'integer', 'Weight for clarity in scoring'),
('score_contact_weight', '25', 'integer', 'Weight for contact availability'),
('score_urgency_weight', '20', 'integer', 'Weight for urgency in scoring'),
('free_daily_limit', '5', 'integer', 'Daily signal limit for free users'),
('pro_monthly_price', '10', 'integer', 'Monthly price for Pro plan in USD');

-- ==========================================
-- 7. PURE DEMAND CHANNEL BLUEPRINTS (500+ CAP)
-- ==========================================

INSERT OR IGNORE INTO pure_demand_channels (name, engine_type, target_url, extraction_config, interval_minutes) VALUES 
('REST API Ingestion Blueprint', 'rest_json', 'https://api.example.com/v1/jobs', 
 '{"root_key": "data", "title_key": "title", "desc_key": "description", "url_key": "url", "fallback_category": "general_services"}', 30),

('RSS/XML Feed Ingestion Blueprint', 'rss_xml', 'https://example.com/services/feed.xml', 
 '{"fallback_category": "business_leads"}', 45),

('Google Advanced Footprint Blueprint', 'google_footprint', 'Google Engine Route', 
 '{"search_query": "site:linkedin.com/jobs/ \"urgent\" \"cleaner\" hiring", "location": "Nairobi"}', 120),

('Reddit Subreddit Monitoring Blueprint', 'reddit_sub', 'https://www.reddit.com/r/forhire/new.json', 
 '{"search_query": "r/forhire", "fallback_category": "freelance_gigs"}', 60);