-- =====================================
-- GATEWAY DE PAGAMENTOS - SCHEMA SQL
-- =====================================

-- Usuários da plataforma (vendedores)
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    password TEXT NOT NULL,
    cpf_cnpj TEXT UNIQUE,
    phone TEXT,
    pix_key TEXT,
    bank_info TEXT, -- JSON com dados bancários
    webhook_url TEXT,
    webhook_enabled BOOLEAN DEFAULT 1,
    auto_approve BOOLEAN DEFAULT 0, -- Aprovar automaticamente pedidos pagos
    status INTEGER DEFAULT 1, -- 0: inativo, 1: ativo, 2: suspenso
    is_admin BOOLEAN DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- API Keys dos usuários
CREATE TABLE IF NOT EXISTS api_keys (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    api_key TEXT UNIQUE NOT NULL,
    api_secret TEXT NOT NULL,
    name TEXT,
    status INTEGER DEFAULT 1,
    last_used_at DATETIME,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Transações/Pedidos
CREATE TABLE IF NOT EXISTS transactions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    api_key_id INTEGER,
    external_id TEXT, -- ID da transação na Wintech ou outra gateway
    order_id TEXT UNIQUE NOT NULL,
    
    -- Dados do pagamento
    amount DECIMAL(10,2) NOT NULL,
    payment_method TEXT NOT NULL, -- pix, credit_card, boleto
    status TEXT DEFAULT 'pending', -- pending, paid, failed, cancelled, refunded, chargeback
    
    -- Dados do cliente
    customer_name TEXT,
    customer_email TEXT,
    customer_phone TEXT,
    customer_document TEXT,
    
    -- Dados específicos por método
    pix_code TEXT,
    pix_qr_code TEXT,
    pix_expires_at DATETIME,
    boleto_url TEXT,
    boleto_barcode TEXT,
    card_last_digits TEXT,
    card_brand TEXT,
    
    -- Cálculos financeiros
    platform_fee DECIMAL(10,2) DEFAULT 0, -- Taxa da plataforma
    gateway_fee DECIMAL(10,2) DEFAULT 0, -- Taxa da gateway (Wintech)
    reserve_amount DECIMAL(10,2) DEFAULT 0, -- 5% reserva
    net_amount DECIMAL(10,2) DEFAULT 0, -- Valor líquido para o vendedor
    
    -- Datas
    paid_at DATETIME,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    -- Webhook
    webhook_sent BOOLEAN DEFAULT 0,
    webhook_response TEXT,
    webhook_retries INTEGER DEFAULT 0,
    
    -- Metadados
    metadata TEXT, -- JSON
    ip_address TEXT,
    user_agent TEXT,
    
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (api_key_id) REFERENCES api_keys(id)
);

-- Histórico de status das transações
CREATE TABLE IF NOT EXISTS transaction_logs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    transaction_id INTEGER NOT NULL,
    status_from TEXT,
    status_to TEXT NOT NULL,
    notes TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (transaction_id) REFERENCES transactions(id) ON DELETE CASCADE
);

-- Saques
CREATE TABLE IF NOT EXISTS withdrawals (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    fee DECIMAL(10,2) DEFAULT 10.00,
    net_amount DECIMAL(10,2) NOT NULL,
    
    -- Dados bancários para saque
    pix_key TEXT,
    bank_account TEXT, -- JSON
    
    status TEXT DEFAULT 'pending', -- pending, processing, completed, cancelled, failed
    processed_at DATETIME,
    notes TEXT,
    
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Leads (dados dos clientes que pagaram)
CREATE TABLE IF NOT EXISTS leads (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    transaction_id INTEGER,
    name TEXT,
    email TEXT,
    phone TEXT,
    document TEXT,
    amount DECIMAL(10,2),
    payment_method TEXT,
    status TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (transaction_id) REFERENCES transactions(id)
);

-- Configurações da plataforma
CREATE TABLE IF NOT EXISTS settings (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    key TEXT UNIQUE NOT NULL,
    value TEXT,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Logs de webhook recebidos da Wintech
CREATE TABLE IF NOT EXISTS webhook_logs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    provider TEXT NOT NULL, -- wintech
    payload TEXT NOT NULL,
    headers TEXT,
    processed BOOLEAN DEFAULT 0,
    error_message TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Índices para performance
CREATE INDEX IF NOT EXISTS idx_transactions_user_id ON transactions(user_id);
CREATE INDEX IF NOT EXISTS idx_transactions_status ON transactions(status);
CREATE INDEX IF NOT EXISTS idx_transactions_order_id ON transactions(order_id);
CREATE INDEX IF NOT EXISTS idx_transactions_created_at ON transactions(created_at);
CREATE INDEX IF NOT EXISTS idx_api_keys_user_id ON api_keys(user_id);
CREATE INDEX IF NOT EXISTS idx_api_keys_key ON api_keys(api_key);
CREATE INDEX IF NOT EXISTS idx_leads_user_id ON leads(user_id);
CREATE INDEX IF NOT EXISTS idx_withdrawals_user_id ON withdrawals(user_id);

-- Inserir configurações padrão
INSERT OR IGNORE INTO settings (key, value) VALUES
('platform_name', 'PayGateway'),
('pix_fee_percent', '8.99'),
('pix_fee_fixed', '4.99'),
('credit_fee_percent', '4.89'),
('credit_fee_fixed', '4.49'),
('boleto_fee_percent', '5.99'),
('boleto_fee_fixed', '3.99'),
('reserve_percent', '5.00'),
('withdrawal_min', '20.00'),
('withdrawal_fee', '10.00'),
('wintech_api_url', 'https://app.wintechpagamentos.com/api/v1/gateway/pix/receive');
