/* =====================================================================
   SISTEMA DE VENDA DE COTAS - SCHEMA SQL SERVER
   Stack: PHP + HTML/CSS/JS + SQL Server
   Recursos: Cadastro, Recuperação de senha via e-mail, Venda de cotas,
             Pagamento PIX (Mercado Pago), Painel admin, Extrato PDF/Excel
   ===================================================================== */

CREATE DATABASE VendaCotasDB;
GO

USE VendaCotasDB;
GO

/* =====================================================================
   1. USUÁRIOS
   ===================================================================== */
CREATE TABLE usuarios (
    id                  INT IDENTITY(1,1) PRIMARY KEY,
    nome                VARCHAR(150)      NOT NULL,
    email               VARCHAR(150)      NOT NULL UNIQUE,
    senha_hash          VARCHAR(255)      NOT NULL,       -- password_hash() do PHP (bcrypt/argon2)
    cpf                 VARCHAR(14)       NULL UNIQUE,
    telefone            VARCHAR(20)       NULL,
    tipo_usuario        VARCHAR(20)       NOT NULL DEFAULT 'cliente'
                            CONSTRAINT CK_usuarios_tipo CHECK (tipo_usuario IN ('cliente','admin')),
    ativo               BIT               NOT NULL DEFAULT 1,
    email_verificado    BIT               NOT NULL DEFAULT 0,
    data_cadastro       DATETIME2         NOT NULL DEFAULT SYSDATETIME(),
    data_atualizacao    DATETIME2         NULL,
    ultimo_login        DATETIME2         NULL
);
GO

CREATE INDEX IX_usuarios_email ON usuarios(email);
GO

/* =====================================================================
   2. RECUPERAÇÃO DE SENHA (tokens enviados por e-mail)
   ===================================================================== */
CREATE TABLE recuperacao_senha (
    id                  INT IDENTITY(1,1) PRIMARY KEY,
    usuario_id          INT               NOT NULL REFERENCES usuarios(id) ON DELETE CASCADE,
    token               VARCHAR(255)      NOT NULL UNIQUE,  -- token aleatório (hash) enviado por e-mail
    data_criacao        DATETIME2         NOT NULL DEFAULT SYSDATETIME(),
    data_expiracao      DATETIME2         NOT NULL,          -- ex: +1 hora
    utilizado           BIT               NOT NULL DEFAULT 0,
    ip_solicitacao      VARCHAR(45)       NULL
);
GO

CREATE INDEX IX_recuperacao_token ON recuperacao_senha(token);
GO

/* =====================================================================
   3. COTAS (produto vendido)
   ===================================================================== */
CREATE TABLE cotas (
    id                  INT IDENTITY(1,1) PRIMARY KEY,
    nome                VARCHAR(150)      NOT NULL,
    descricao           VARCHAR(MAX)      NULL,
    valor_unitario      DECIMAL(14,2)     NOT NULL,
    quantidade_total    INT               NOT NULL,
    quantidade_vendida  INT               NOT NULL DEFAULT 0,
    ativo               BIT               NOT NULL DEFAULT 1,
    data_criacao        DATETIME2         NOT NULL DEFAULT SYSDATETIME(),
    data_atualizacao    DATETIME2         NULL,
    CONSTRAINT CK_cotas_quantidade CHECK (quantidade_vendida <= quantidade_total)
);
GO

/* Coluna calculada de disponibilidade */
ALTER TABLE cotas ADD quantidade_disponivel AS (quantidade_total - quantidade_vendida);
GO

/* =====================================================================
   4. VENDAS (pedidos de compra de cotas)
   ===================================================================== */
CREATE TABLE vendas (
    id                  INT IDENTITY(1,1) PRIMARY KEY,
    usuario_id          INT               NOT NULL REFERENCES usuarios(id),
    cota_id             INT               NOT NULL REFERENCES cotas(id),
    quantidade          INT               NOT NULL,
    valor_unitario      DECIMAL(14,2)     NOT NULL,     -- valor no momento da compra
    valor_total         DECIMAL(14,2)     NOT NULL,
    status              VARCHAR(20)       NOT NULL DEFAULT 'pendente'
                            CONSTRAINT CK_vendas_status CHECK (status IN ('pendente','pago','cancelado','expirado','reembolsado')),
    data_venda          DATETIME2         NOT NULL DEFAULT SYSDATETIME(),
    data_atualizacao    DATETIME2         NULL
);
GO

CREATE INDEX IX_vendas_usuario ON vendas(usuario_id);
CREATE INDEX IX_vendas_status  ON vendas(status);
GO

/* =====================================================================
   5. PAGAMENTOS (integração PIX - Mercado Pago)
   ===================================================================== */
CREATE TABLE pagamentos (
    id                      INT IDENTITY(1,1) PRIMARY KEY,
    venda_id                INT               NOT NULL REFERENCES vendas(id),
    mp_payment_id           VARCHAR(50)       NULL,        -- id retornado pelo Mercado Pago
    mp_preference_id        VARCHAR(100)      NULL,
    forma_pagamento         VARCHAR(20)       NOT NULL DEFAULT 'pix',
    valor                   DECIMAL(14,2)     NOT NULL,
    status                  VARCHAR(20)       NOT NULL DEFAULT 'pendente'
                                CONSTRAINT CK_pagamentos_status CHECK (
                                    status IN ('pendente','aprovado','rejeitado','cancelado','estornado','expirado')
                                ),
    qr_code                 VARCHAR(MAX)      NULL,        -- copia e cola do PIX
    qr_code_base64          VARCHAR(MAX)      NULL,        -- imagem do QR Code
    data_criacao             DATETIME2        NOT NULL DEFAULT SYSDATETIME(),
    data_expiracao           DATETIME2        NULL,
    data_pagamento           DATETIME2        NULL,
    data_atualizacao         DATETIME2        NULL
);
GO

CREATE INDEX IX_pagamentos_venda ON pagamentos(venda_id);
CREATE INDEX IX_pagamentos_mp_id ON pagamentos(mp_payment_id);
GO

/* =====================================================================
   6. LOG DE WEBHOOKS DO MERCADO PAGO (auditoria/depuração de notificações)
   ===================================================================== */
CREATE TABLE pagamento_webhook_log (
    id                  INT IDENTITY(1,1) PRIMARY KEY,
    pagamento_id        INT               NULL REFERENCES pagamentos(id),
    payload             VARCHAR(MAX)      NOT NULL,    -- JSON recebido do MP
    tipo_evento         VARCHAR(50)       NULL,
    processado          BIT               NOT NULL DEFAULT 0,
    data_recebimento    DATETIME2         NOT NULL DEFAULT SYSDATETIME()
);
GO

/* =====================================================================
   7. LOG DE AÇÕES DO ADMIN (auditoria do painel administrativo)
   ===================================================================== */
CREATE TABLE admin_logs (
    id                  INT IDENTITY(1,1) PRIMARY KEY,
    admin_id            INT               NOT NULL REFERENCES usuarios(id),
    acao                VARCHAR(100)      NOT NULL,      -- ex: 'editou_cota', 'cancelou_venda'
    tabela_afetada      VARCHAR(50)       NULL,
    registro_id         INT               NULL,
    detalhes            VARCHAR(MAX)      NULL,
    ip_origem           VARCHAR(45)       NULL,
    data_acao           DATETIME2         NOT NULL DEFAULT SYSDATETIME()
);
GO

/* =====================================================================
   8. EXTRATOS GERADOS (histórico de exportações PDF/Excel)
   ===================================================================== */
CREATE TABLE extratos_gerados (
    id                  INT IDENTITY(1,1) PRIMARY KEY,
    usuario_id          INT               NOT NULL REFERENCES usuarios(id),
    tipo_arquivo        VARCHAR(10)       NOT NULL CHECK (tipo_arquivo IN ('pdf','excel')),
    data_inicio         DATE              NULL,
    data_fim            DATE              NULL,
    caminho_arquivo     VARCHAR(255)      NULL,
    data_geracao        DATETIME2         NOT NULL DEFAULT SYSDATETIME()
);
GO

/* =====================================================================
   9. CONFIGURAÇÕES GERAIS DO SISTEMA (parametrizável via painel admin)
   ===================================================================== */
CREATE TABLE configuracoes (
    id                  INT IDENTITY(1,1) PRIMARY KEY,
    chave               VARCHAR(100)      NOT NULL UNIQUE,   -- ex: 'smtp_host', 'mp_public_key'
    valor               VARCHAR(MAX)      NULL,
    descricao           VARCHAR(255)      NULL,
    data_atualizacao    DATETIME2         NULL
);
GO

/* =====================================================================
   TRIGGERS: manter quantidade_vendida em cotas sincronizada com vendas pagas
   ===================================================================== */
CREATE TRIGGER TRG_vendas_atualiza_cota
ON vendas
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    -- Quando uma venda muda de status para 'pago', soma a quantidade na cota
    UPDATE c
    SET c.quantidade_vendida = c.quantidade_vendida + i.quantidade
    FROM cotas c
    INNER JOIN inserted i ON i.cota_id = c.id
    INNER JOIN deleted d ON d.id = i.id
    WHERE i.status = 'pago' AND d.status <> 'pago';

    -- Quando uma venda paga é cancelada/reembolsada, devolve a quantidade
    UPDATE c
    SET c.quantidade_vendida = c.quantidade_vendida - i.quantidade
    FROM cotas c
    INNER JOIN inserted i ON i.cota_id = c.id
    INNER JOIN deleted d ON d.id = i.id
    WHERE i.status IN ('cancelado','reembolsado') AND d.status = 'pago';
END;
GO

/* =====================================================================
   VIEW: extrato do usuário (base para exportação em PDF/Excel)
   ===================================================================== */
CREATE VIEW vw_extrato_usuario AS
SELECT
    v.id            AS venda_id,
    u.id            AS usuario_id,
    u.nome          AS usuario_nome,
    c.nome          AS cota_nome,
    v.quantidade,
    v.valor_unitario,
    v.valor_total,
    v.status        AS status_venda,
    p.status        AS status_pagamento,
    p.forma_pagamento,
    p.data_pagamento,
    v.data_venda
FROM vendas v
INNER JOIN usuarios u ON u.id = v.usuario_id
INNER JOIN cotas c ON c.id = v.cota_id
LEFT JOIN pagamentos p ON p.venda_id = v.id;
GO

/* =====================================================================
   DADOS INICIAIS (opcional - usuário admin padrão)
   Observação: gerar o hash da senha no PHP com password_hash() antes de inserir.
   ===================================================================== */
-- INSERT INTO usuarios (nome, email, senha_hash, tipo_usuario, ativo, email_verificado)
-- VALUES ('Administrador', 'admin@seudominio.com', '<hash_gerado_no_php>', 'admin', 1, 1);