-- Script completo para corrigir schema na VPS
-- Execute: sudo -u postgres psql -d maktubia_points -f fix-schema-vps-completo.sql

-- 1. Adicionar colunas faltantes em users
ALTER TABLE users ADD COLUMN IF NOT EXISTS bi VARCHAR(50);
ALTER TABLE users ADD COLUMN IF NOT EXISTS user_code VARCHAR(50) UNIQUE;

-- 2. Adicionar colunas faltantes em establishments
ALTER TABLE establishments ADD COLUMN IF NOT EXISTS created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE establishments ADD COLUMN IF NOT EXISTS updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

-- 3. Criar tabela purchases se não existir
CREATE TABLE IF NOT EXISTS purchases (
    purchase_id SERIAL PRIMARY KEY,
    user_id VARCHAR(64) NOT NULL,
    establishment_id VARCHAR(64),
    qr_code VARCHAR(100),
    purchase_amount NUMERIC(14,2) NOT NULL,
    conversion_rate NUMERIC(10,4) DEFAULT 0.2,
    bonus_multiplier NUMERIC(10,2) DEFAULT 1.0,
    points_earned BIGINT NOT NULL DEFAULT 0,
    status VARCHAR(20) DEFAULT 'pending' CHECK (status IN ('pending', 'confirmed', 'rejected', 'cancelled')),
    purchase_code VARCHAR(50) UNIQUE,
    purchase_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    confirmed_at TIMESTAMP,
    confirmed_by INTEGER REFERENCES users(user_id),
    notes TEXT,
    receipt_photo TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 4. Criar índices para purchases
CREATE INDEX IF NOT EXISTS idx_purchases_user_id ON purchases(user_id);
CREATE INDEX IF NOT EXISTS idx_purchases_establishment_id ON purchases(establishment_id);
CREATE INDEX IF NOT EXISTS idx_purchases_qr_code ON purchases(qr_code);
CREATE INDEX IF NOT EXISTS idx_purchases_purchase_date ON purchases(purchase_date);
CREATE INDEX IF NOT EXISTS idx_purchases_status ON purchases(status);

-- 5. Verificar resultado
SELECT 'users columns' as table_name, column_name 
FROM information_schema.columns 
WHERE table_name = 'users' AND column_name IN ('bi', 'user_code')
UNION ALL
SELECT 'establishments columns' as table_name, column_name 
FROM information_schema.columns 
WHERE table_name = 'establishments' AND column_name IN ('created_at', 'updated_at')
UNION ALL
SELECT 'purchases table' as table_name, CASE WHEN EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'purchases') THEN 'exists' ELSE 'missing' END as column_name;