-- ============================================================
-- Ingressos+ - Script de criação do banco de dados PostgreSQL
-- Execute este SQL no cPanel → PostgreSQL Databases → phpPgAdmin
-- OU via psql: psql -U usuario -d nome_banco -f database_setup.sql
-- ============================================================

-- ============================================================
-- TABELA: users
-- ============================================================
CREATE TABLE IF NOT EXISTS users (
  id               SERIAL PRIMARY KEY,
  name             TEXT NOT NULL,
  email            TEXT NOT NULL UNIQUE,
  password_hash    TEXT NOT NULL,
  role             TEXT NOT NULL DEFAULT 'buyer',
  commission_rate  DECIMAL(5,2) DEFAULT 10.00,
  payment_gateway  TEXT,
  gateway_public_key  TEXT,
  gateway_secret_key  TEXT,
  contact_email    TEXT,
  contact_whatsapp TEXT,
  cpf              TEXT,
  birth_date       TEXT,
  phone            TEXT,
  company_name     TEXT,
  cnpj             TEXT,
  company_address  TEXT,
  bank_name        TEXT,
  bank_agency      TEXT,
  bank_account     TEXT,
  bank_account_type TEXT,
  pix_key          TEXT,
  approval_status  TEXT,
  approval_rejection_reason TEXT,
  created_at       TIMESTAMP NOT NULL DEFAULT NOW()
);

-- ============================================================
-- TABELA: events
-- ============================================================
CREATE TABLE IF NOT EXISTS events (
  id              SERIAL PRIMARY KEY,
  title           TEXT NOT NULL,
  description     TEXT NOT NULL,
  category        TEXT NOT NULL,
  venue           TEXT NOT NULL,
  city            TEXT NOT NULL,
  state           TEXT NOT NULL,
  date            TEXT NOT NULL,
  time            TEXT NOT NULL,
  image_url       TEXT,
  banner_image_url TEXT,
  kit_image_url   TEXT,
  latitude        DECIMAL(10,7),
  longitude       DECIMAL(10,7),
  status          TEXT NOT NULL DEFAULT 'published',
  total_capacity  INTEGER NOT NULL,
  organizer_name  TEXT NOT NULL,
  organizer_id    INTEGER REFERENCES users(id),
  kit_pickup_info TEXT,
  regulation      TEXT,
  rejection_reason TEXT,
  created_at      TIMESTAMP NOT NULL DEFAULT NOW()
);

-- ============================================================
-- TABELA: ticket_types
-- ============================================================
CREATE TABLE IF NOT EXISTS ticket_types (
  id            SERIAL PRIMARY KEY,
  event_id      INTEGER NOT NULL REFERENCES events(id) ON DELETE CASCADE,
  name          TEXT NOT NULL,
  description   TEXT,
  price         DECIMAL(10,2) NOT NULL,
  quantity      INTEGER NOT NULL,
  sold          INTEGER NOT NULL DEFAULT 0,
  is_half_price BOOLEAN NOT NULL DEFAULT FALSE,
  sort_order    INTEGER NOT NULL DEFAULT 0
);

-- ============================================================
-- TABELA: orders
-- ============================================================
CREATE TABLE IF NOT EXISTS orders (
  id                SERIAL PRIMARY KEY,
  event_id          INTEGER NOT NULL REFERENCES events(id),
  buyer_name        TEXT NOT NULL,
  buyer_email       TEXT NOT NULL,
  buyer_cpf         TEXT NOT NULL,
  status            TEXT NOT NULL DEFAULT 'confirmed',
  total_amount      DECIMAL(10,2) NOT NULL,
  custom_fields_data JSONB,
  qr_code           TEXT,
  checked_in        BOOLEAN NOT NULL DEFAULT FALSE,
  checked_in_at     TIMESTAMP,
  kit_delivered     BOOLEAN NOT NULL DEFAULT FALSE,
  kit_delivered_at  TIMESTAMP,
  notes             TEXT,
  deleted_at        TIMESTAMP,
  created_at        TIMESTAMP NOT NULL DEFAULT NOW()
);

-- ============================================================
-- TABELA: order_items
-- ============================================================
CREATE TABLE IF NOT EXISTS order_items (
  id             SERIAL PRIMARY KEY,
  order_id       INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
  ticket_type_id INTEGER NOT NULL REFERENCES ticket_types(id),
  quantity       INTEGER NOT NULL,
  unit_price     DECIMAL(10,2) NOT NULL,
  subtotal       DECIMAL(10,2) NOT NULL
);

-- ============================================================
-- TABELA: event_custom_fields
-- ============================================================
CREATE TABLE IF NOT EXISTS event_custom_fields (
  id         SERIAL PRIMARY KEY,
  event_id   INTEGER NOT NULL REFERENCES events(id) ON DELETE CASCADE,
  label      TEXT NOT NULL,
  field_type TEXT NOT NULL,
  required   BOOLEAN NOT NULL DEFAULT FALSE,
  options    JSONB,
  sort_order INTEGER NOT NULL DEFAULT 0
);

-- ============================================================
-- TABELA: organizer_documents
-- ============================================================
CREATE TABLE IF NOT EXISTS organizer_documents (
  id            SERIAL PRIMARY KEY,
  organizer_id  INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  document_type TEXT NOT NULL,
  file_name     TEXT NOT NULL,
  original_name TEXT NOT NULL,
  file_size     INTEGER,
  object_path   TEXT NOT NULL,
  uploaded_at   TIMESTAMP NOT NULL DEFAULT NOW()
);

-- ============================================================
-- TABELA: event_documents
-- ============================================================
CREATE TABLE IF NOT EXISTS event_documents (
  id            SERIAL PRIMARY KEY,
  event_id      INTEGER NOT NULL REFERENCES events(id) ON DELETE CASCADE,
  file_name     TEXT NOT NULL,
  original_name TEXT NOT NULL,
  file_size     INTEGER,
  object_path   TEXT NOT NULL,
  uploaded_at   TIMESTAMP NOT NULL DEFAULT NOW()
);

-- ============================================================
-- TABELA: platform_settings
-- ============================================================
CREATE TABLE IF NOT EXISTS platform_settings (
  id                   SERIAL PRIMARY KEY,
  mp_client_id         TEXT,
  mp_client_secret     TEXT,
  mp_access_token      TEXT,
  asaas_master_api_key TEXT,
  asaas_webhook_token  TEXT,
  hero_line1           TEXT,
  hero_line2           TEXT,
  hero_line3           TEXT,
  hero_subtitle        TEXT,
  hero_bg_image_url    TEXT,
  site_name            TEXT,
  site_logo_url        TEXT,
  updated_at           TIMESTAMP NOT NULL DEFAULT NOW()
);

-- Adiciona colunas novas em bancos já existentes (seguro, ignora se já existir)
ALTER TABLE platform_settings ADD COLUMN IF NOT EXISTS site_name     TEXT;
ALTER TABLE platform_settings ADD COLUMN IF NOT EXISTS site_logo_url TEXT;

-- Insere configurações padrão (primeira linha)
INSERT INTO platform_settings (
  hero_line1, hero_line2, hero_line3, hero_subtitle, hero_bg_image_url
) SELECT
  'CORRA.', 'COMPITA.', 'VENÇA.', 'Os maiores eventos esportivos em um só lugar.', ''
WHERE NOT EXISTS (SELECT 1 FROM platform_settings);

-- ============================================================
-- USUÁRIO ADMIN PADRÃO
-- Email: maria@teste.com  |  Senha: 123456
-- ============================================================
INSERT INTO users (name, email, password_hash, role, approval_status)
VALUES (
  'Maria Admin',
  'maria@teste.com',
  '$2b$10$1fSX0AIItXibQQ3ATmpRwua0Q8oqWEygol6G94AV7E3whhgO7bWKW',
  'admin',
  'approved'
) ON CONFLICT (email) DO NOTHING;

-- ============================================================
-- FIM DO SCRIPT
-- Login padrão: maria@teste.com / 123456
-- TROQUE A SENHA DO ADMIN APÓS O PRIMEIRO LOGIN!
-- ============================================================