-- ============================================
-- 🎰 RIFA MANAGER PRO v3 - BANCO DE DADOS
-- ============================================

-- OFERTAS (cada uma com suas configurações)
CREATE TABLE IF NOT EXISTS offers (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    
    -- Identificação
    slug VARCHAR(50) NOT NULL UNIQUE,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    emoji VARCHAR(10) DEFAULT '🎁',
    status VARCHAR(20) DEFAULT 'active',
    
    -- URLs
    real_page_url VARCHAR(500) NOT NULL,
    white_page_url VARCHAR(500) DEFAULT 'https://google.com/',
    
    -- Preço
    price_per_number DECIMAL(10,2) DEFAULT 0.00,
    
    -- ========== CLOAKER ==========
    cloaker_enabled BOOLEAN DEFAULT 1,
    traffic_token VARCHAR(100) DEFAULT 'cm2025pro',
    token_param VARCHAR(20) DEFAULT 'ck',
    require_token BOOLEAN DEFAULT 1,
    min_time_on_page INTEGER DEFAULT 1,
    
    -- Filtros Proteção
    block_vpn BOOLEAN DEFAULT 1,
    block_datacenter BOOLEAN DEFAULT 1,
    block_tor BOOLEAN DEFAULT 1,
    use_fingerprint BOOLEAN DEFAULT 1,
    use_honeypot BOOLEAN DEFAULT 1,
    
    -- Filtros Geo
    allowed_countries TEXT DEFAULT '["BR"]',
    allow_mobile BOOLEAN DEFAULT 1,
    allow_desktop BOOLEAN DEFAULT 1,
    allow_tablet BOOLEAN DEFAULT 1,
    
    -- ========== GATEWAY ==========
    gateway_id INTEGER,
    gateway_custom_key TEXT,
    gateway_custom_company TEXT,
    
    -- ========== FACEBOOK PIXEL ==========
    fb_pixel_id VARCHAR(50),
    fb_access_token TEXT,
    fb_test_event_code VARCHAR(50),
    fb_capi_enabled BOOLEAN DEFAULT 0,
    
    -- ========== UTMIFY ==========
    utmify_enabled BOOLEAN DEFAULT 0,
    utmify_token VARCHAR(255),
    utmify_project_id VARCHAR(100),
    
    -- ========== GOOGLE ADS ==========
    gads_enabled BOOLEAN DEFAULT 0,
    gads_conversion_id VARCHAR(100),
    gads_conversion_label VARCHAR(100),
    
    -- ========== TIKTOK ==========
    tiktok_pixel_id VARCHAR(100),
    tiktok_access_token TEXT,
    
    -- Stats
    stats_total_access INTEGER DEFAULT 0,
    stats_passed INTEGER DEFAULT 0,
    stats_blocked INTEGER DEFAULT 0,
    stats_conversions INTEGER DEFAULT 0,
    stats_revenue INTEGER DEFAULT 0,
    
    -- Timestamps
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    files_generated_at DATETIME
);

-- DOMÍNIOS (vários domínios podem apontar para uma oferta)
CREATE TABLE IF NOT EXISTS domains (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    offer_id INTEGER NOT NULL,
    domain VARCHAR(255) NOT NULL,
    is_primary BOOLEAN DEFAULT 0,
    is_active BOOLEAN DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (offer_id) REFERENCES offers(id) ON DELETE CASCADE,
    UNIQUE(domain)
);

-- GATEWAYS
CREATE TABLE IF NOT EXISTS gateways (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    slug VARCHAR(50) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    api_url VARCHAR(500) NOT NULL,
    api_key TEXT,
    company_id VARCHAR(255),
    auth_type VARCHAR(20) DEFAULT 'apikey',
    auth_header VARCHAR(100) DEFAULT 'x-api-key',
    auth_prefix VARCHAR(50) DEFAULT '',
    endpoint_create VARCHAR(255) DEFAULT '/api/transactions',
    endpoint_status VARCHAR(255) DEFAULT '/api/transactions',
    status_field VARCHAR(100) DEFAULT 'status',
    qrcode_field VARCHAR(100) DEFAULT 'pix.qrcode',
    copypaste_field VARCHAR(100) DEFAULT 'pix.copiaECola',
    txid_field VARCHAR(100) DEFAULT 'id',
    valor_em_reais BOOLEAN DEFAULT 0,
    is_default BOOLEAN DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- TRANSAÇÕES
CREATE TABLE IF NOT EXISTS transactions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    offer_id INTEGER NOT NULL,
    domain VARCHAR(255),
    tx_ref VARCHAR(100) UNIQUE NOT NULL,
    gateway_tx_id VARCHAR(255),
    customer_name VARCHAR(255),
    customer_email VARCHAR(255),
    customer_phone VARCHAR(50),
    customer_cpf VARCHAR(20),
    amount_cents INTEGER NOT NULL,
    quantity INTEGER DEFAULT 1,
    status VARCHAR(20) DEFAULT 'pending',
    utm_source VARCHAR(100),
    utm_medium VARCHAR(100),
    utm_campaign VARCHAR(255),
    utm_content VARCHAR(255),
    utm_term VARCHAR(255),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    paid_at DATETIME,
    FOREIGN KEY (offer_id) REFERENCES offers(id)
);

-- LOGS DE ACESSO
CREATE TABLE IF NOT EXISTS access_logs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    offer_id INTEGER NOT NULL,
    domain VARCHAR(255),
    ip_address VARCHAR(45),
    user_agent TEXT,
    referer TEXT,
    country VARCHAR(10),
    device_type VARCHAR(20),
    passed BOOLEAN DEFAULT 0,
    block_reason VARCHAR(255),
    score INTEGER DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (offer_id) REFERENCES offers(id) ON DELETE CASCADE
);

-- WEBHOOK LOGS
CREATE TABLE IF NOT EXISTS webhook_logs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    offer_id INTEGER,
    gateway VARCHAR(50),
    payload TEXT,
    processed BOOLEAN DEFAULT 0,
    result TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- CONFIG
CREATE TABLE IF NOT EXISTS config (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    config_key VARCHAR(100) UNIQUE NOT NULL,
    config_value TEXT
);

INSERT OR IGNORE INTO config (config_key, config_value) VALUES 
('admin_user', 'admin'),
('admin_pass', 'rifa@2026'),
('site_name', 'RifaManager Pro'),
('api_secret', 'RM_SECRET_2025');

-- ÍNDICES
CREATE INDEX IF NOT EXISTS idx_domains_offer ON domains(offer_id);
CREATE INDEX IF NOT EXISTS idx_transactions_offer ON transactions(offer_id);
CREATE INDEX IF NOT EXISTS idx_transactions_status ON transactions(status);
CREATE INDEX IF NOT EXISTS idx_logs_offer ON access_logs(offer_id);
CREATE INDEX IF NOT EXISTS idx_logs_created ON access_logs(created_at);
