-- Schema PostgreSQL — Alemão Conveniências
-- Fase 1: migração do sql.js para PostgreSQL

-- Extensões
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

-- ─────────────────────────────────────────────────────────────────
-- ADMINS
-- ─────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS admins (
  id            SERIAL PRIMARY KEY,
  username      TEXT UNIQUE NOT NULL,
  password_hash TEXT NOT NULL,
  nome          TEXT,
  email         TEXT,
  telefone      TEXT,
  created_at    TIMESTAMPTZ DEFAULT NOW()
);

-- ─────────────────────────────────────────────────────────────────
-- USERS (compradores / leads)
-- ─────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS users (
  id         SERIAL PRIMARY KEY,
  nome       TEXT NOT NULL,
  email      TEXT UNIQUE,
  cpf        TEXT,
  telefone   TEXT,
  ip         TEXT,
  source     TEXT DEFAULT 'hotspot',
  password_hash TEXT,
  timestamp  TIMESTAMPTZ DEFAULT NOW()
);

CREATE UNIQUE INDEX IF NOT EXISTS idx_users_email ON users(email) WHERE email IS NOT NULL;

-- ─────────────────────────────────────────────────────────────────
-- RAFFLES (sorteios)
-- ─────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS raffles (
  id            SERIAL PRIMARY KEY,
  titulo        TEXT NOT NULL,
  descricao     TEXT,
  preco_numero  NUMERIC(10,2) DEFAULT 5.00,
  total_numeros INTEGER DEFAULT 500,
  status        TEXT DEFAULT 'active' CHECK (status IN ('draft','active','finished')),
  ativo         INTEGER DEFAULT 1,       -- compatibilidade com código existente
  data_criacao  TIMESTAMPTZ DEFAULT NOW()
);

-- ─────────────────────────────────────────────────────────────────
-- TICKETS (números comprados)
-- ─────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS tickets (
  id               SERIAL PRIMARY KEY,
  raffle_id        INTEGER NOT NULL REFERENCES raffles(id) ON DELETE CASCADE,
  numero           INTEGER NOT NULL,
  usuario_nome     TEXT NOT NULL,
  usuario_telefone TEXT,
  usuario_cpf      TEXT,
  data_compra      TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE (raffle_id, numero)
);

CREATE INDEX IF NOT EXISTS idx_tickets_raffle ON tickets(raffle_id);
CREATE INDEX IF NOT EXISTS idx_tickets_cpf    ON tickets(usuario_cpf);

-- ─────────────────────────────────────────────────────────────────
-- PROMOTIONS
-- ─────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS promotions (
  id                SERIAL PRIMARY KEY,
  nome              TEXT NOT NULL,
  descricao         TEXT,
  preco_original    NUMERIC(10,2),
  preco_promocional NUMERIC(10,2) NOT NULL,
  imagem            TEXT DEFAULT '',
  ativo             INTEGER DEFAULT 1,
  data_criacao      TIMESTAMPTZ DEFAULT NOW()
);

-- ─────────────────────────────────────────────────────────────────
-- PLUGINS
-- ─────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS plugins (
  id            TEXT PRIMARY KEY,
  active        INTEGER DEFAULT 0,
  installed_at  TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS plugin_configs (
  plugin_id   TEXT NOT NULL,
  key         TEXT NOT NULL,
  value       TEXT,
  PRIMARY KEY (plugin_id, key)
);

-- ─────────────────────────────────────────────────────────────────
-- RAFFLE RESULTS & LOGS (Fase 2)
-- ─────────────────────────────────────────────────────────────────
ALTER TABLE raffles ADD COLUMN IF NOT EXISTS start_at TIMESTAMPTZ;
ALTER TABLE raffles ADD COLUMN IF NOT EXISTS end_at TIMESTAMPTZ;

CREATE TABLE IF NOT EXISTS raffle_results (
  id                SERIAL PRIMARY KEY,
  raffle_id         INTEGER NOT NULL REFERENCES raffles(id) ON DELETE CASCADE UNIQUE,
  winner_ticket_id  INTEGER NOT NULL REFERENCES tickets(id),
  seed              TEXT NOT NULL,
  algorithm_version TEXT DEFAULT '1.0',
  drawn_at          TIMESTAMPTZ DEFAULT NOW(),
  created_at        TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS raffle_draw_logs (
  id             SERIAL PRIMARY KEY,
  raffle_id      INTEGER NOT NULL REFERENCES raffles(id) ON DELETE CASCADE,
  seed           TEXT,
  result_number  INTEGER,
  executed_at    TIMESTAMPTZ DEFAULT NOW(),
  ip             TEXT,
  created_at     TIMESTAMPTZ DEFAULT NOW()
);