-- Script para criação do banco de dados e tabelas

-- USE `nome_do_seu_banco`; -- O instalador vai criar o banco e usá-lo

SET NAMES utf8mb4;
SET time_zone = '-03:00'; -- Ajuste conforme seu fuso horário ou use UTC

-- Tabela de Usuários
CREATE TABLE IF NOT EXISTS `usuarios` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `nome` VARCHAR(255) NOT NULL,
    `email` VARCHAR(255) NOT NULL UNIQUE,
    `senha_hash` VARCHAR(255) NOT NULL,
    `data_cadastro` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabela de Categorias
CREATE TABLE IF NOT EXISTS `categorias` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `usuario_id` INT NOT NULL,
    `nome` VARCHAR(100) NOT NULL,
    `tipo` ENUM('receita', 'despesa') NOT NULL,
    `criado_em` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `atualizado_em` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`usuario_id`) REFERENCES `usuarios`(`id`) ON DELETE CASCADE,
    INDEX `idx_categorias_usuario_id` (`usuario_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabela de Contas Financeiras (bancárias, carteiras)
CREATE TABLE IF NOT EXISTS `contas_financeiras` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `usuario_id` INT NOT NULL,
    `nome` VARCHAR(100) NOT NULL,
    `tipo_conta` VARCHAR(50) COMMENT 'ex: corrente, poupanca, investimento, dinheiro',
    `saldo_inicial` DECIMAL(15,2) DEFAULT 0.00,
    `cor_hex` VARCHAR(7) NULL COMMENT 'ex: #FF0000, para UI',
    `criado_em` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `atualizado_em` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`usuario_id`) REFERENCES `usuarios`(`id`) ON DELETE CASCADE,
    INDEX `idx_contas_financeiras_usuario_id` (`usuario_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabela de Cartões de Crédito
CREATE TABLE IF NOT EXISTS `cartoes_credito` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `usuario_id` INT NOT NULL,
    `nome` VARCHAR(100) NOT NULL COMMENT 'ex: Nubank Platinum',
    `limite` DECIMAL(10,2) NOT NULL,
    `dia_fechamento_fatura` TINYINT UNSIGNED NOT NULL COMMENT 'Dia do mês (1-31)',
    `dia_vencimento_fatura` TINYINT UNSIGNED NOT NULL COMMENT 'Dia do mês (1-31)',
    `bandeira` VARCHAR(50) NULL COMMENT 'ex: Visa, Mastercard',
    `conta_padrao_debito_fatura_id` INT NULL COMMENT 'Conta financeira para débito automático da fatura',
    `criado_em` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `atualizado_em` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`usuario_id`) REFERENCES `usuarios`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`conta_padrao_debito_fatura_id`) REFERENCES `contas_financeiras`(`id`) ON DELETE SET NULL,
    INDEX `idx_cartoes_credito_usuario_id` (`usuario_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabela de Transações (Entradas e Saídas)
CREATE TABLE IF NOT EXISTS `transacoes` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `usuario_id` INT NOT NULL,
    `descricao` VARCHAR(255) NOT NULL,
    `valor` DECIMAL(10,2) NOT NULL,
    `data_transacao` DATE NOT NULL,
    `tipo` ENUM('receita', 'despesa', 'transferencia') NOT NULL,
    `categoria_id` INT NULL,
    `conta_financeira_id` INT NULL COMMENT 'Conta de origem para despesa/transferencia, ou destino para receita',
    `conta_destino_id` INT NULL COMMENT 'Usado para transferências entre contas',
    `cartao_credito_id` INT NULL COMMENT 'Se for uma despesa de cartão de crédito',
    `fatura_id` INT NULL COMMENT 'Preenchido quando a despesa do cartão é associada a uma fatura',
    `pago_recebido` BOOLEAN DEFAULT 0 COMMENT '0=Pendente, 1=Realizado',
    `data_efetivacao` DATE NULL COMMENT 'Data em que a transação foi paga/recebida',
    `observacoes` TEXT NULL,
    `parcela_numero` TINYINT UNSIGNED NULL,
    `parcela_total` TINYINT UNSIGNED NULL,
    `transacao_pai_id` INT NULL COMMENT 'Para agrupar parcelas',
    `criado_em` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `atualizado_em` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`usuario_id`) REFERENCES `usuarios`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`categoria_id`) REFERENCES `categorias`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`conta_financeira_id`) REFERENCES `contas_financeiras`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`conta_destino_id`) REFERENCES `contas_financeiras`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`cartao_credito_id`) REFERENCES `cartoes_credito`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`transacao_pai_id`) REFERENCES `transacoes`(`id`) ON DELETE CASCADE,
    INDEX `idx_transacoes_usuario_id` (`usuario_id`),
    INDEX `idx_transacoes_categoria_id` (`categoria_id`),
    INDEX `idx_transacoes_conta_financeira_id` (`conta_financeira_id`),
    INDEX `idx_transacoes_cartao_credito_id` (`cartao_credito_id`),
    INDEX `idx_transacoes_transacao_pai_id` (`transacao_pai_id`),
    INDEX `idx_transacoes_fatura_id` (`fatura_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabela de Faturas de Cartão
CREATE TABLE IF NOT EXISTS `faturas_cartao` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `cartao_credito_id` INT NOT NULL,
    `mes_referencia` TINYINT UNSIGNED NOT NULL COMMENT 'Mês (1-12)',
    `ano_referencia` SMALLINT UNSIGNED NOT NULL COMMENT 'Ano (YYYY)',
    `data_vencimento` DATE NOT NULL,
    `data_fechamento` DATE NOT NULL,
    `valor_total` DECIMAL(10,2) DEFAULT 0.00,
    `paga` BOOLEAN DEFAULT 0,
    `data_pagamento` DATE NULL,
    `conta_pagamento_id` INT NULL COMMENT 'De onde saiu o dinheiro para pagar a fatura',
    `criado_em` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `atualizado_em` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`cartao_credito_id`) REFERENCES `cartoes_credito`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`conta_pagamento_id`) REFERENCES `contas_financeiras`(`id`) ON DELETE SET NULL,
    UNIQUE KEY `uq_fatura_cartao_mes_ano` (`cartao_credito_id`, `mes_referencia`, `ano_referencia`),
    INDEX `idx_faturas_conta_pagamento_id` (`conta_pagamento_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabela de Fontes Pagadoras (Opcional, mas solicitado)
CREATE TABLE IF NOT EXISTS `fontes_pagadoras` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `usuario_id` INT NOT NULL,
    `nome` VARCHAR(150) NOT NULL,
    `descricao` TEXT NULL,
    `criado_em` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `atualizado_em` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`usuario_id`) REFERENCES `usuarios`(`id`) ON DELETE CASCADE,
    INDEX `idx_fontes_pagadoras_usuario_id` (`usuario_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabela de Investimentos
CREATE TABLE IF NOT EXISTS `investimentos` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `usuario_id` INT NOT NULL,
    `tipo_investimento` ENUM('acao', 'fundo_imobiliario', 'renda_fixa', 'criptomoeda', 'outro') NOT NULL,
    `codigo_ativo` VARCHAR(20) NOT NULL COMMENT 'ex: PETR4, MXRF11, BTC',
    `nome_ativo` VARCHAR(100) NULL COMMENT 'ex: Petrobras PN, Maxi Renda FII',
    `corretora_plataforma` VARCHAR(100) NULL,
    `criado_em` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `atualizado_em` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`usuario_id`) REFERENCES `usuarios`(`id`) ON DELETE CASCADE,
    INDEX `idx_investimentos_usuario_id` (`usuario_id`),
    INDEX `idx_investimentos_codigo_ativo` (`codigo_ativo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabela para registrar operações de investimento (compra/venda)
CREATE TABLE IF NOT EXISTS `operacoes_investimento` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `investimento_id` INT NOT NULL,
    `usuario_id` INT NOT NULL,
    `tipo_operacao` ENUM('compra', 'venda') NOT NULL,
    `quantidade` DECIMAL(18,8) NOT NULL, -- Alta precisão para criptos/frações
    `preco_unitario` DECIMAL(15,5) NOT NULL,
    `data_operacao` DATETIME NOT NULL,
    `taxas` DECIMAL(10,2) DEFAULT 0.00,
    `conta_financeira_id` INT NULL COMMENT 'Conta usada na compra/venda',
    `transacao_id` INT NULL COMMENT 'Link para transação financeira (saída na compra, entrada na venda)',
    `observacoes` TEXT NULL,
    `criado_em` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`investimento_id`) REFERENCES `investimentos`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`usuario_id`) REFERENCES `usuarios`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`conta_financeira_id`) REFERENCES `contas_financeiras`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`transacao_id`) REFERENCES `transacoes`(`id`) ON DELETE SET NULL,
    INDEX `idx_operacoes_investimento_id` (`investimento_id`),
    INDEX `idx_operacoes_usuario_id` (`usuario_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Adicionando a coluna fatura_id na tabela transacoes (se não foi adicionada antes)
-- ALTER TABLE `transacoes` ADD COLUMN `fatura_id` INT NULL AFTER `cartao_credito_id`;
-- ALTER TABLE `transacoes` ADD CONSTRAINT `fk_transacoes_fatura` FOREIGN KEY (`fatura_id`) REFERENCES `faturas_cartao`(`id`) ON DELETE SET NULL;
-- INDEX `idx_transacoes_fatura_id` (`fatura_id`) já foi incluído na criação da tabela transacoes.

-- Ajustes finais e comentários:
-- - ON DELETE CASCADE: Usado criteriosamente. Se um usuário é deletado, seus dados relacionados são deletados.
-- - ON DELETE SET NULL: Usado quando a referência é opcional.
-- - Índices: Adicionados para colunas frequentemente usadas em buscas (FKs, IDs de usuário, etc.).
-- - DECIMAL(10,2) para valores monetários é um bom começo, pode precisar de DECIMAL(15,2) ou mais para grandes somas.
-- - `transacoes.tipo` agora inclui 'transferencia'.
-- - `transacoes.conta_destino_id` para transferências.
-- - `transacoes.data_efetivacao` e `transacoes.pago_recebido` para melhor controle de fluxo de caixa.
-- - `cartoes_credito.conta_padrao_debito_fatura_id` para facilitar o pagamento de faturas.
-- - `faturas_cartao` para um controle mais granular de gastos no cartão.
-- - `investimentos` e `operacoes_investimento` para gestão de portfólio.

-- O instalador deverá:
-- 1. Pedir dados do banco (host, user, pass, new_db_name).
-- 2. Criar o banco de dados `new_db_name`.
-- 3. Selecionar o banco `USE new_db_name;`.
-- 4. Executar este script SQL.
-- 5. Criar o usuário administrador inicial na tabela `usuarios`.
-- 6. Salvar os dados de conexão em `config/database.php`.

SELECT 'Schema SQL criado com sucesso!' AS status;

-- Fim do script
