schema.sql

-- ============================================================
-- schema.sql — LOJA DE ROUPAS (MySQL 8+)
-- Banco de Dados I | CRUD — Parte 1
-- ============================================================

CREATE DATABASE IF NOT EXISTS loja_roupas
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

USE loja_roupas;

SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS pagamento;
DROP TABLE IF EXISTS item_venda;
DROP TABLE IF EXISTS venda;
DROP TABLE IF EXISTS produto;
DROP TABLE IF EXISTS categoria;
DROP TABLE IF EXISTS vendedor;
DROP TABLE IF EXISTS cliente;
SET FOREIGN_KEY_CHECKS = 1;

-- ============================
-- CLIENTE
-- ============================
CREATE TABLE cliente (
    id                  INT           NOT NULL AUTO_INCREMENT PRIMARY KEY,
    nome                VARCHAR(100)  NOT NULL,
    cpf                 VARCHAR(14)   NOT NULL UNIQUE,
    email               VARCHAR(100)  NOT NULL,
    telefone            VARCHAR(20)   NOT NULL,
    torce_flamengo      BOOLEAN       NOT NULL DEFAULT FALSE,
    assiste_one_piece   BOOLEAN       NOT NULL DEFAULT FALSE,
    eh_de_sousa         BOOLEAN       NOT NULL DEFAULT FALSE,
    criado_em           DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_cliente_nome (nome)
);

-- ============================
-- VENDEDOR
-- ============================
CREATE TABLE vendedor (
    id         INT          NOT NULL AUTO_INCREMENT PRIMARY KEY,
    nome       VARCHAR(100) NOT NULL,
    cpf        VARCHAR(14)  NOT NULL UNIQUE,
    matricula  VARCHAR(20)  NOT NULL UNIQUE,
    ativo      BOOLEAN      NOT NULL DEFAULT TRUE,
    INDEX idx_vendedor_nome (nome)
);

-- ============================
-- CATEGORIA
-- ============================
CREATE TABLE categoria (
    id   INT          NOT NULL AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(100) NOT NULL UNIQUE
);

-- ============================
-- PRODUTO
-- ============================
CREATE TABLE produto (
    id                  INT            NOT NULL AUTO_INCREMENT PRIMARY KEY,
    categoria_id        INT            NOT NULL,
    nome                VARCHAR(100)   NOT NULL,
    marca               VARCHAR(100)   NOT NULL,
    preco               DECIMAL(10,2)  NOT NULL CHECK (preco > 0),
    quantidade_estoque  INT            NOT NULL DEFAULT 0 CHECK (quantidade_estoque >= 0),
    tamanho             VARCHAR(10),
    fabricado_em_mari   BOOLEAN        NOT NULL DEFAULT FALSE,
    FOREIGN KEY (categoria_id) REFERENCES categoria(id) ON DELETE RESTRICT,
    INDEX idx_produto_nome      (nome),
    INDEX idx_produto_marca     (marca),
    INDEX idx_produto_categoria (categoria_id)
);

-- ============================
-- VENDA
-- ============================
CREATE TABLE venda (
    id               INT            NOT NULL AUTO_INCREMENT PRIMARY KEY,
    cliente_id       INT            NOT NULL,
    vendedor_id      INT            NOT NULL,
    data_venda       DATETIME       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    desconto_percent DECIMAL(5,2)   NOT NULL DEFAULT 0
                         CHECK (desconto_percent >= 0 AND desconto_percent <= 100),
    total_bruto      DECIMAL(12,2)  NOT NULL DEFAULT 0 CHECK (total_bruto  >= 0),
    total_liquido    DECIMAL(12,2)  NOT NULL DEFAULT 0 CHECK (total_liquido >= 0),
    status           ENUM('CONCLUIDA','CANCELADA') NOT NULL DEFAULT 'CONCLUIDA',
    FOREIGN KEY (cliente_id)  REFERENCES cliente(id)  ON DELETE RESTRICT,
    FOREIGN KEY (vendedor_id) REFERENCES vendedor(id) ON DELETE RESTRICT,
    INDEX idx_venda_data   (data_venda),
    INDEX idx_venda_status (status)
);

-- ============================
-- ITEM VENDA
-- ============================
CREATE TABLE item_venda (
    id             INT           NOT NULL AUTO_INCREMENT PRIMARY KEY,
    venda_id       INT           NOT NULL,
    produto_id     INT           NOT NULL,
    quantidade     INT           NOT NULL CHECK (quantidade > 0),
    preco_unitario DECIMAL(10,2) NOT NULL CHECK (preco_unitario > 0),
    UNIQUE KEY uq_venda_produto (venda_id, produto_id),
    FOREIGN KEY (venda_id)   REFERENCES venda(id)   ON DELETE CASCADE,
    FOREIGN KEY (produto_id) REFERENCES produto(id) ON DELETE RESTRICT
);

-- ============================
-- PAGAMENTO
-- ============================
CREATE TABLE pagamento (
    id                  INT          NOT NULL AUTO_INCREMENT PRIMARY KEY,
    venda_id            INT          NOT NULL UNIQUE,
    tipo                ENUM('CARTAO','BOLETO','PIX','DINHEIRO','BERRIES') NOT NULL,
    status_confirmacao  ENUM('PENDENTE','CONFIRMADO','RECUSADO','CANCELADO') NOT NULL DEFAULT 'PENDENTE',
    codigo_transacao    VARCHAR(100),
    criado_em           DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (venda_id) REFERENCES venda(id) ON DELETE CASCADE,
    INDEX idx_pagamento_status (status_confirmacao)
);

-- ============================
-- VIEW: relatório mensal de vendedores
-- ============================
CREATE OR REPLACE VIEW vw_relatorio_vendedor AS
SELECT
    v.id                                        AS vendedor_id,
    v.nome                                      AS vendedor,
    DATE_FORMAT(vd.data_venda, '%Y-%m-01')      AS mes,
    COUNT(vd.id)                                AS total_vendas,
    SUM(vd.total_liquido)                       AS faturamento,
    AVG(vd.total_liquido)                       AS ticket_medio
FROM vendedor v
LEFT JOIN venda vd ON vd.vendedor_id = v.id AND vd.status = 'CONCLUIDA'
GROUP BY v.id, v.nome, DATE_FORMAT(vd.data_venda, '%Y-%m-01')
ORDER BY mes DESC, faturamento DESC;

-- ============================
-- STORED PROCEDURE: confirmar pagamento
-- ============================
DELIMITER $$
CREATE PROCEDURE confirmar_pagamento(IN p_venda_id INT, IN p_codigo VARCHAR(100))
BEGIN
    DECLARE linhas_afetadas INT DEFAULT 0;

    UPDATE pagamento
       SET status_confirmacao = 'CONFIRMADO',
           codigo_transacao   = p_codigo
     WHERE venda_id = p_venda_id
       AND status_confirmacao = 'PENDENTE';

    SET linhas_afetadas = ROW_COUNT();

    IF linhas_afetadas = 0 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Pagamento não encontrado ou já processado.';
    END IF;
END$$
DELIMITER ;