-- =============================================================
-- YBYRÁ / ICJ - Módulo Projetos e Ações
-- Instalação incremental: não altera as tabelas antigas do Ybyrá.
-- Prefixo pa_ = Projetos e Ações
-- =============================================================

SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS pa_tipos_financiador (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(120) NOT NULL,
    ativo TINYINT(1) NOT NULL DEFAULT 1,
    ordem INT NOT NULL DEFAULT 0,
    criado_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    atualizado_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_pa_tipos_financiador_nome (nome),
    KEY idx_pa_tipos_financiador_ativo (ativo, ordem)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pa_eixos (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(150) NOT NULL,
    ativo TINYINT(1) NOT NULL DEFAULT 1,
    ordem INT NOT NULL DEFAULT 0,
    criado_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    atualizado_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_pa_eixos_nome (nome),
    KEY idx_pa_eixos_ativo (ativo, ordem)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pa_tipos_acao (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(120) NOT NULL,
    ativo TINYINT(1) NOT NULL DEFAULT 1,
    ordem INT NOT NULL DEFAULT 0,
    criado_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    atualizado_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_pa_tipos_acao_nome (nome),
    KEY idx_pa_tipos_acao_ativo (ativo, ordem)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pa_setores (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(120) NOT NULL,
    ativo TINYINT(1) NOT NULL DEFAULT 1,
    ordem INT NOT NULL DEFAULT 0,
    criado_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    atualizado_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_pa_setores_nome (nome),
    KEY idx_pa_setores_ativo (ativo, ordem)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pa_complexidades (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(80) NOT NULL,
    ativo TINYINT(1) NOT NULL DEFAULT 1,
    ordem INT NOT NULL DEFAULT 0,
    criado_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    atualizado_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_pa_complexidades_nome (nome),
    KEY idx_pa_complexidades_ativo (ativo, ordem)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pa_situacoes_prestacao (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(100) NOT NULL,
    ativo TINYINT(1) NOT NULL DEFAULT 1,
    ordem INT NOT NULL DEFAULT 0,
    criado_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    atualizado_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_pa_situacoes_prestacao_nome (nome),
    KEY idx_pa_situacoes_prestacao_ativo (ativo, ordem)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pa_financiadores (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nome_abreviado VARCHAR(120) NOT NULL,
    nome_completo VARCHAR(255) NOT NULL,
    pais_codigo CHAR(2) NOT NULL DEFAULT 'BR',
    tipo_financiador_id INT UNSIGNED DEFAULT NULL,
    site VARCHAR(255) DEFAULT NULL,
    rede_social VARCHAR(255) DEFAULT NULL,
    pessoa_contato VARCHAR(180) DEFAULT NULL,
    telefone VARCHAR(80) DEFAULT NULL,
    email VARCHAR(180) DEFAULT NULL,
    ativo TINYINT(1) NOT NULL DEFAULT 1,
    criado_por INT UNSIGNED DEFAULT NULL COMMENT 'ID do usuário central icjorgbr_sis.usuarios',
    atualizado_por INT UNSIGNED DEFAULT NULL COMMENT 'ID do usuário central icjorgbr_sis.usuarios',
    criado_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    atualizado_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_pa_financiadores_tipo FOREIGN KEY (tipo_financiador_id) REFERENCES pa_tipos_financiador(id) ON DELETE SET NULL,
    KEY idx_pa_financiadores_nome (nome_abreviado, nome_completo),
    KEY idx_pa_financiadores_pais (pais_codigo),
    KEY idx_pa_financiadores_ativo (ativo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pa_projetos (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(255) NOT NULL,
    numero_referencia VARCHAR(120) DEFAULT NULL,
    situacao VARCHAR(30) NOT NULL DEFAULT 'a_iniciar',
    data_aprovacao DATE DEFAULT NULL,
    data_credito DATE DEFAULT NULL,
    data_inicio DATE DEFAULT NULL,
    data_fim DATE DEFAULT NULL,
    prazo_prestacao_contas DATE DEFAULT NULL,
    situacao_prestacao_id INT UNSIGNED DEFAULT NULL,
    valor_aprovado DECIMAL(15,2) DEFAULT NULL,
    moeda CHAR(3) NOT NULL DEFAULT 'BRL',
    valor_brl DECIMAL(15,2) DEFAULT NULL COMMENT 'Valor equivalente em reais quando moeda <> BRL',
    observacoes TEXT DEFAULT NULL,
    criado_por INT UNSIGNED DEFAULT NULL COMMENT 'ID do usuário central icjorgbr_sis.usuarios',
    atualizado_por INT UNSIGNED DEFAULT NULL COMMENT 'ID do usuário central icjorgbr_sis.usuarios',
    criado_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    atualizado_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_pa_projetos_situacao_prestacao FOREIGN KEY (situacao_prestacao_id) REFERENCES pa_situacoes_prestacao(id) ON DELETE SET NULL,
    KEY idx_pa_projetos_situacao (situacao),
    KEY idx_pa_projetos_vigencia (data_inicio, data_fim),
    KEY idx_pa_projetos_prestacao (prazo_prestacao_contas, situacao_prestacao_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pa_projeto_financiadores (
    projeto_id INT UNSIGNED NOT NULL,
    financiador_id INT UNSIGNED NOT NULL,
    PRIMARY KEY (projeto_id, financiador_id),
    CONSTRAINT fk_pa_pf_projeto FOREIGN KEY (projeto_id) REFERENCES pa_projetos(id) ON DELETE CASCADE,
    CONSTRAINT fk_pa_pf_financiador FOREIGN KEY (financiador_id) REFERENCES pa_financiadores(id) ON DELETE RESTRICT,
    KEY idx_pa_pf_financiador (financiador_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pa_hashtags (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(80) NOT NULL,
    ativo TINYINT(1) NOT NULL DEFAULT 1,
    criado_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_pa_hashtags_nome (nome),
    KEY idx_pa_hashtags_ativo (ativo, nome)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pa_acoes (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(255) NOT NULL,
    responsavel_associado_id INT UNSIGNED NOT NULL COMMENT 'ID de icjorgbr_apoena.associados_icj; sem FK para permitir banco externo',
    complexidade_id INT UNSIGNED DEFAULT NULL,
    inicio_mes CHAR(7) DEFAULT NULL COMMENT 'YYYY-MM',
    fim_mes CHAR(7) DEFAULT NULL COMMENT 'YYYY-MM',
    situacao VARCHAR(30) NOT NULL DEFAULT 'a_iniciar',
    atrasado TINYINT(1) NOT NULL DEFAULT 0,
    criado_por INT UNSIGNED DEFAULT NULL COMMENT 'ID do usuário central icjorgbr_sis.usuarios',
    atualizado_por INT UNSIGNED DEFAULT NULL COMMENT 'ID do usuário central icjorgbr_sis.usuarios',
    criado_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    atualizado_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_pa_acoes_complexidade FOREIGN KEY (complexidade_id) REFERENCES pa_complexidades(id) ON DELETE SET NULL,
    KEY idx_pa_acoes_responsavel (responsavel_associado_id),
    KEY idx_pa_acoes_situacao (situacao, atrasado),
    KEY idx_pa_acoes_periodo (inicio_mes, fim_mes)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pa_acao_responsaveis_secundarios (
    acao_id INT UNSIGNED NOT NULL,
    associado_id INT UNSIGNED NOT NULL COMMENT 'ID de icjorgbr_apoena.associados_icj',
    PRIMARY KEY (acao_id, associado_id),
    CONSTRAINT fk_pa_ars_acao FOREIGN KEY (acao_id) REFERENCES pa_acoes(id) ON DELETE CASCADE,
    KEY idx_pa_ars_associado (associado_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pa_acao_eixos (
    acao_id INT UNSIGNED NOT NULL,
    eixo_id INT UNSIGNED NOT NULL,
    PRIMARY KEY (acao_id, eixo_id),
    CONSTRAINT fk_pa_ae_acao FOREIGN KEY (acao_id) REFERENCES pa_acoes(id) ON DELETE CASCADE,
    CONSTRAINT fk_pa_ae_eixo FOREIGN KEY (eixo_id) REFERENCES pa_eixos(id) ON DELETE RESTRICT,
    KEY idx_pa_ae_eixo (eixo_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pa_acao_tipos (
    acao_id INT UNSIGNED NOT NULL,
    tipo_acao_id INT UNSIGNED NOT NULL,
    PRIMARY KEY (acao_id, tipo_acao_id),
    CONSTRAINT fk_pa_at_acao FOREIGN KEY (acao_id) REFERENCES pa_acoes(id) ON DELETE CASCADE,
    CONSTRAINT fk_pa_at_tipo FOREIGN KEY (tipo_acao_id) REFERENCES pa_tipos_acao(id) ON DELETE RESTRICT,
    KEY idx_pa_at_tipo (tipo_acao_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pa_acao_setores (
    acao_id INT UNSIGNED NOT NULL,
    setor_id INT UNSIGNED NOT NULL,
    PRIMARY KEY (acao_id, setor_id),
    CONSTRAINT fk_pa_as_acao FOREIGN KEY (acao_id) REFERENCES pa_acoes(id) ON DELETE CASCADE,
    CONSTRAINT fk_pa_as_setor FOREIGN KEY (setor_id) REFERENCES pa_setores(id) ON DELETE RESTRICT,
    KEY idx_pa_as_setor (setor_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pa_acao_hashtags (
    acao_id INT UNSIGNED NOT NULL,
    hashtag_id INT UNSIGNED NOT NULL,
    PRIMARY KEY (acao_id, hashtag_id),
    CONSTRAINT fk_pa_ah_acao FOREIGN KEY (acao_id) REFERENCES pa_acoes(id) ON DELETE CASCADE,
    CONSTRAINT fk_pa_ah_hashtag FOREIGN KEY (hashtag_id) REFERENCES pa_hashtags(id) ON DELETE RESTRICT,
    KEY idx_pa_ah_hashtag (hashtag_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pa_acao_financiamentos (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    acao_id INT UNSIGNED NOT NULL,
    projeto_id INT UNSIGNED NOT NULL,
    financiador_id INT UNSIGNED NOT NULL,
    criado_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_pa_af_acao FOREIGN KEY (acao_id) REFERENCES pa_acoes(id) ON DELETE CASCADE,
    CONSTRAINT fk_pa_af_projeto FOREIGN KEY (projeto_id) REFERENCES pa_projetos(id) ON DELETE RESTRICT,
    CONSTRAINT fk_pa_af_financiador FOREIGN KEY (financiador_id) REFERENCES pa_financiadores(id) ON DELETE RESTRICT,
    UNIQUE KEY uk_pa_af_tripla (acao_id, projeto_id, financiador_id),
    KEY idx_pa_af_projeto (projeto_id),
    KEY idx_pa_af_financiador (financiador_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pa_acao_anotacoes (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    acao_id INT UNSIGNED NOT NULL,
    usuario_id INT UNSIGNED NOT NULL COMMENT 'ID do usuário central icjorgbr_sis.usuarios',
    texto VARCHAR(1200) NOT NULL,
    editado TINYINT(1) NOT NULL DEFAULT 0,
    criado_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    atualizado_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_pa_anotacoes_acao FOREIGN KEY (acao_id) REFERENCES pa_acoes(id) ON DELETE CASCADE,
    KEY idx_pa_anotacoes_acao_data (acao_id, criado_em),
    KEY idx_pa_anotacoes_usuario (usuario_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pa_acao_anotacoes_historico (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    anotacao_id INT UNSIGNED NOT NULL,
    usuario_editor_id INT UNSIGNED NOT NULL COMMENT 'ID do usuário central icjorgbr_sis.usuarios',
    texto_anterior VARCHAR(1200) NOT NULL,
    texto_novo VARCHAR(1200) NOT NULL,
    editado_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_pa_aah_anotacao FOREIGN KEY (anotacao_id) REFERENCES pa_acao_anotacoes(id) ON DELETE CASCADE,
    KEY idx_pa_aah_anotacao_data (anotacao_id, editado_em)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pa_acao_log (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    acao_id INT UNSIGNED NOT NULL,
    usuario_id INT UNSIGNED NOT NULL COMMENT 'ID do usuário central icjorgbr_sis.usuarios',
    tipo VARCHAR(40) NOT NULL DEFAULT 'edicao',
    campo VARCHAR(100) DEFAULT NULL,
    valor_anterior TEXT DEFAULT NULL,
    valor_novo TEXT DEFAULT NULL,
    descricao VARCHAR(500) DEFAULT NULL,
    criado_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_pa_log_acao FOREIGN KEY (acao_id) REFERENCES pa_acoes(id) ON DELETE CASCADE,
    KEY idx_pa_log_acao_data (acao_id, criado_em),
    KEY idx_pa_log_usuario (usuario_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Cadastros iniciais
INSERT INTO pa_tipos_financiador (nome, ativo, ordem) VALUES
('Entidade católica nacional', 1, 10),
('Entidade católica internacional', 1, 20),
('Governo federal', 1, 30),
('Governo estadual', 1, 40),
('Governo municipal', 1, 50),
('Empresa privada', 1, 60),
('Banco ou cooperativa de crédito', 1, 70),
('Outros', 1, 80)
ON DUPLICATE KEY UPDATE nome = VALUES(nome);

INSERT INTO pa_eixos (nome, ativo, ordem) VALUES
('Saúde Mental', 1, 10),
('Comunicação e Participação Social', 1, 20),
('Ecologia e Justiça Climática', 1, 30)
ON DUPLICATE KEY UPDATE nome = VALUES(nome);

INSERT INTO pa_tipos_acao (nome, ativo, ordem) VALUES
('Pesquisa', 1, 10),
('Podcast', 1, 20),
('Documentário', 1, 30)
ON DUPLICATE KEY UPDATE nome = VALUES(nome);

INSERT INTO pa_setores (nome, ativo, ordem) VALUES
('Comunicação Institucional', 1, 10),
('Administrativo', 1, 20),
('Contábil Financeiro', 1, 30)
ON DUPLICATE KEY UPDATE nome = VALUES(nome);

INSERT INTO pa_complexidades (nome, ativo, ordem) VALUES
('Simples', 1, 10),
('Médio', 1, 20),
('Complexo', 1, 30)
ON DUPLICATE KEY UPDATE nome = VALUES(nome);

INSERT INTO pa_situacoes_prestacao (nome, ativo, ordem) VALUES
('A iniciar', 1, 10),
('Enviada', 1, 20),
('Em atraso', 1, 30),
('Concluída', 1, 40),
('Outros', 1, 50)
ON DUPLICATE KEY UPDATE nome = VALUES(nome);