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

CREATE TABLE IF NOT EXISTS users (
    id SERIAL PRIMARY KEY,
    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 TRUE,
    credits INTEGER DEFAULT 0,
    plan TEXT DEFAULT 'free',
    daily_credits INTEGER DEFAULT 5,
    credits_reset_date DATE 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 TIMESTAMP,
    last_login TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

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

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

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

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

CREATE TABLE IF NOT EXISTS leads (
    id SERIAL PRIMARY KEY,
    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 FALSE,
    is_active BOOLEAN DEFAULT TRUE,
    posted_date TIMESTAMP,
    discovered_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_checked_at TIMESTAMP,
    times_displayed INTEGER DEFAULT 0,
    times_contacted INTEGER DEFAULT 0
);

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

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

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

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

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

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

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

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

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

INSERT INTO users (email, password_hash, full_name, role, is_active, plan, daily_credits) 
SELECT 'admin@afriintel.com', '$2b$10$YourHashedPasswordHere', 'System Administrator', 'super_admin', TRUE, 'pro', 999999
WHERE NOT EXISTS (SELECT 1 FROM users WHERE email = 'admin@afriintel.com');

INSERT 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')
ON CONFLICT (setting_key) DO NOTHING;