DELIMITER $$

-- ===================================================================================
-- PROCEDURE 1: Matricular um novo aluno e gerar sua primeira mensalidade.
-- Versão refinada com mais parâmetros e lógica de vencimento aprimorada.
-- ===================================================================================
DROP PROCEDURE IF EXISTS sp_matricular_aluno$$

CREATE PROCEDURE sp_matricular_aluno(
    IN p_nome_completo VARCHAR(255),
    IN p_cpf VARCHAR(11),
    IN p_email VARCHAR(100),
    IN p_telefone VARCHAR(20),
    IN p_data_nascimento DATE, -- Parâmetro adicionado para o cadastro completo
    IN p_valor_mensal DECIMAL(10,2),
    IN p_dia_vencimento INT -- Dia do mês para o vencimento (ex: 10)
)
BEGIN
    DECLARE v_id_aluno INT;
    DECLARE v_data_vencimento DATE;

    -- Se algo der errado, desfaz todas as alterações.
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Falha na matrícula. Nenhuma alteração foi aplicada.';
    END;

    START TRANSACTION;

        -- 1. Cria o aluno
        INSERT INTO alunos (nome_completo, cpf, email, telefone, data_nascimento, ativo)
        VALUES (p_nome_completo, p_cpf, p_email, p_telefone, p_data_nascimento, TRUE);

        SET v_id_aluno = LAST_INSERT_ID();

        -- 2. Calcula a data do primeiro vencimento de forma inteligente
        SET v_data_vencimento = STR_TO_DATE(CONCAT(YEAR(CURDATE()), '-', MONTH(CURDATE()), '-', p_dia_vencimento), '%Y-%m-%d');
        -- Se o dia do vencimento já passou neste mês, a primeira cobrança é para o mês seguinte.
        IF v_data_vencimento < CURDATE() THEN
            SET v_data_vencimento = v_data_vencimento + INTERVAL 1 MONTH;
        END IF;

        -- 3. Gera a primeira mensalidade
        INSERT INTO mensalidades (id_aluno, mes_referencia, valor_devido, data_vencimento, status)
        VALUES (
            v_id_aluno,
            DATE_FORMAT(v_data_vencimento, '%Y-%m-01'), -- O mês de referência é o mesmo do vencimento
            p_valor_mensal,
            v_data_vencimento,
            'Pendente'
        );

    COMMIT;
END$$


-- ===================================================================================
-- PROCEDURE 2: Registrar um pagamento (funciona em conjunto com o trigger).
-- ===================================================================================
DROP PROCEDURE IF EXISTS sp_registrar_pagamento$$

CREATE PROCEDURE sp_registrar_pagamento(
    IN p_id_mensalidade INT,
    IN p_valor_pago DECIMAL(10,2),
    IN p_forma_pgto VARCHAR(50),
    IN p_obs TEXT
)
BEGIN
    -- Rollback automático se der erro.
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Falha ao registrar pagamento. Nenhuma alteração foi aplicada.';
    END;

    START TRANSACTION;

        -- 1. Insere o pagamento.
        INSERT INTO Pagamentos (id_mensalidade, valor_pago, data_pagamento, forma_pagamento, observacao)
        VALUES (p_id_mensalidade, p_valor_pago, CURDATE(), p_forma_pgto, p_obs);

        -- 2. O trigger 'tg_after_insert_pagamento' fará o resto do trabalho.

    COMMIT;
END$$

DELIMITER ;


DELIMITER $$

-- ===================================================================================
-- PROCEDURE: sp_estornar_pagamento
-- Objetivo: Desfaz um registro de pagamento e recalcula o status da mensalidade
--           associada de forma segura e atômica (ACID).
-- ===================================================================================
DROP PROCEDURE IF EXISTS sp_estornar_pagamento$$

CREATE PROCEDURE sp_estornar_pagamento(
    IN p_id_pagamento INT
)
BEGIN
    -- Variáveis para armazenar dados intermediários
    DECLARE v_id_mensalidade INT;
    DECLARE v_total_pago DECIMAL(10,2);
    DECLARE v_data_vencimento DATE;

    -- Handler para garantir que, se qualquer erro ocorrer, a transação
    -- seja desfeita (ROLLBACK) e uma mensagem clara seja retornada.
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Falha ao estornar pagamento. Nenhuma alteração foi aplicada.';
    END;

    -- Inicia a transação para garantir que todas as operações sejam um bloco único.
    START TRANSACTION;

        -- 1. Encontra a mensalidade associada ao pagamento e bloqueia a linha
        --    com 'FOR UPDATE' para evitar que outra transação a modifique ao mesmo tempo.
        SELECT id_mensalidade
          INTO v_id_mensalidade
          FROM Pagamentos
         WHERE id_pagamento = p_id_pagamento
           FOR UPDATE;

        -- 2. Valida se o pagamento realmente existe.
        IF v_id_mensalidade IS NULL THEN
            -- Força um erro para ser capturado pelo EXIT HANDLER.
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Pagamento não encontrado.';
        END IF;

        -- 3. Exclui o registro do pagamento da tabela.
        DELETE FROM Pagamentos WHERE id_pagamento = p_id_pagamento;

        -- 4. Recalcula o valor total que ainda resta pago para a mensalidade.
        SELECT IFNULL(SUM(valor_pago), 0)
          INTO v_total_pago
          FROM Pagamentos
         WHERE id_mensalidade = v_id_mensalidade;

        -- 5. Busca a data de vencimento para definir o status corretamente.
        SELECT data_vencimento
          INTO v_data_vencimento
          FROM Mensalidades
         WHERE id_mensalidade = v_id_mensalidade;

        -- 6. Atualiza o status da mensalidade com base na nova situação financeira.
        UPDATE Mensalidades
           SET status = CASE
                            -- Se ainda resta algum valor pago, é parcial.
                            WHEN v_total_pago > 0 THEN 'Pago Parcialmente'
                            -- Se não resta nada pago E a data já passou, está vencido.
                            WHEN v_data_vencimento < CURDATE() THEN 'Vencido'
                            -- Se não, volta a ser pendente.
                            ELSE 'Pendente'
                        END
         WHERE id_mensalidade = v_id_mensalidade;

    -- Se todos os passos foram executados com sucesso, confirma as alterações.
    COMMIT;
END$$

DELIMITER ;