-- =====================================================
-- SCRIPT DE CRIAÇÃO DAS TABELAS DO SISTEMA DE COBRANÇA
-- Sistema: SIGO Laravel - Módulo de Cobrança
-- Banco: Oracle
-- Schema: UNIOD
-- =====================================================

-- =====================================================
-- 1. SEQUENCES PARA AUTO-INCREMENT
-- =====================================================

-- Sequence para cob_processo_cobranca
CREATE SEQUENCE uniod.seq_cob_processo_cobranca
    START WITH 1
    INCREMENT BY 1
    NOCACHE
    NOCYCLE;

-- Sequence para cob_fatura_cobranca
CREATE SEQUENCE uniod.seq_cob_fatura_cobranca
    START WITH 1
    INCREMENT BY 1
    NOCACHE
    NOCYCLE;

-- Sequence para cob_acordo_cobranca
CREATE SEQUENCE uniod.seq_cob_acordo_cobranca
    START WITH 1
    INCREMENT BY 1
    NOCACHE
    NOCYCLE;

-- Sequence para cob_boleto_cobranca
CREATE SEQUENCE uniod.seq_cob_boleto_cobranca
    START WITH 1
    INCREMENT BY 1
    NOCACHE
    NOCYCLE;

-- Sequence para cob_historico_cobranca
CREATE SEQUENCE uniod.seq_cob_historico_cobranca
    START WITH 1
    INCREMENT BY 1
    NOCACHE
    NOCYCLE;

-- =====================================================
-- 2. TABELAS
-- =====================================================

-- Tabela: cob_processo_cobranca
-- Descrição: Armazena os processos de cobrança de empresas PJ inadimplentes
CREATE TABLE uniod.cob_processo_cobranca (
    prc_id NUMBER(10) NOT NULL,
    prc_pla_codigo VARCHAR2(20) NOT NULL,
    prc_status VARCHAR2(50) NOT NULL,
    prc_valor_total NUMBER(15,2) DEFAULT 0,
    prc_data_inicio DATE DEFAULT SYSDATE,
    prc_data_fechamento DATE,
    prc_usuario_criacao NUMBER(10),
    prc_observacoes VARCHAR2(4000),
    created_at TIMESTAMP DEFAULT SYSDATE,
    updated_at TIMESTAMP DEFAULT SYSDATE,
    CONSTRAINT pk_cob_processo_cobranca PRIMARY KEY (prc_id),
    CONSTRAINT fk_cob_processo_plano FOREIGN KEY (prc_pla_codigo) REFERENCES uniod.tb_plano(pla_codigo),
    CONSTRAINT fk_cob_processo_usuario FOREIGN KEY (prc_usuario_criacao) REFERENCES uniod.tb_usuario(usu_codigo),
    CONSTRAINT ck_processo_status CHECK (prc_status IN ('Aberta', 'Em Negociação', 'Acordo Fechado', 'Quitada', 'Cancelada', 'Incobrável'))
);

-- Tabela: cob_fatura_cobranca
-- Descrição: Faturas vinculadas aos processos de cobrança
CREATE TABLE uniod.cob_fatura_cobranca (
    fco_id NUMBER(10) NOT NULL,
    fco_prc_id NUMBER(10) NOT NULL,
    fco_fat_numero VARCHAR2(11) NOT NULL,
    fco_valor_original NUMBER(15,2) DEFAULT 0,
    fco_valor_juros_multa NUMBER(15,2) DEFAULT 0,
    fco_valor_atualizado NUMBER(15,2) DEFAULT 0,
    fco_status VARCHAR2(50) NOT NULL,
    fco_data_vinculacao TIMESTAMP DEFAULT SYSDATE,
    created_at TIMESTAMP DEFAULT SYSDATE,
    updated_at TIMESTAMP DEFAULT SYSDATE,
    CONSTRAINT pk_cob_fatura_cobranca PRIMARY KEY (fco_id),
    CONSTRAINT fk_cob_fatura_processo FOREIGN KEY (fco_prc_id) REFERENCES uniod.cob_processo_cobranca(prc_id) ON DELETE CASCADE,
    CONSTRAINT fk_cob_fatura_original FOREIGN KEY (fco_fat_numero) REFERENCES uniod.tb_fatura(fat_numero),
    CONSTRAINT ck_fatura_status CHECK (fco_status IN ('Aberta', 'Negociada', 'Quitada'))
);

-- Tabela: cob_acordo_cobranca
-- Descrição: Acordos de negociação criados para os processos
CREATE TABLE uniod.cob_acordo_cobranca (
    aco_id NUMBER(10) NOT NULL,
    aco_prc_id NUMBER(10) NOT NULL,
    aco_tipo VARCHAR2(20) NOT NULL,
    aco_valor_total NUMBER(15,2) DEFAULT 0,
    aco_valor_desconto NUMBER(15,2) DEFAULT 0,
    aco_valor_juros NUMBER(15,2) DEFAULT 0,
    aco_numero_parcelas NUMBER(3) DEFAULT 1,
    aco_status VARCHAR2(50) NOT NULL,
    aco_data_acordo TIMESTAMP DEFAULT SYSDATE,
    aco_data_vencimento_primeira DATE,
    aco_usuario_criacao NUMBER(10),
    aco_observacoes VARCHAR2(4000),
    created_at TIMESTAMP DEFAULT SYSDATE,
    updated_at TIMESTAMP DEFAULT SYSDATE,
    CONSTRAINT pk_cob_acordo_cobranca PRIMARY KEY (aco_id),
    CONSTRAINT fk_cob_acordo_processo FOREIGN KEY (aco_prc_id) REFERENCES uniod.cob_processo_cobranca(prc_id) ON DELETE CASCADE,
    CONSTRAINT fk_cob_acordo_usuario FOREIGN KEY (aco_usuario_criacao) REFERENCES uniod.tb_usuario(usu_codigo),
    CONSTRAINT ck_acordo_tipo CHECK (aco_tipo IN ('Vista', 'Parcelado')),
    CONSTRAINT ck_acordo_status CHECK (aco_status IN ('Pendente', 'Aprovado', 'Quitado', 'Cancelado'))
);

-- Tabela: cob_boleto_cobranca
-- Descrição: Boletos gerados para os acordos
CREATE TABLE uniod.cob_boleto_cobranca (
    bol_id NUMBER(10) NOT NULL,
    bol_aco_id NUMBER(10) NOT NULL,
    bol_numero VARCHAR2(50),
    bol_numero_parcela NUMBER(3),
    bol_valor NUMBER(15,2) DEFAULT 0,
    bol_data_vencimento DATE NOT NULL,
    bol_data_emissao TIMESTAMP DEFAULT SYSDATE,
    bol_data_pagamento DATE,
    bol_status VARCHAR2(50) NOT NULL,
    bol_observacoes VARCHAR2(4000),
    created_at TIMESTAMP DEFAULT SYSDATE,
    updated_at TIMESTAMP DEFAULT SYSDATE,
    CONSTRAINT pk_cob_boleto_cobranca PRIMARY KEY (bol_id),
    CONSTRAINT fk_cob_boleto_acordo FOREIGN KEY (bol_aco_id) REFERENCES uniod.cob_acordo_cobranca(aco_id) ON DELETE CASCADE,
    CONSTRAINT ck_boleto_status CHECK (bol_status IN ('Emitido', 'Pago', 'Vencido', 'Cancelado'))
);

-- Tabela: cob_historico_cobranca
-- Descrição: Histórico de ações realizadas nos processos de cobrança
CREATE TABLE uniod.cob_historico_cobranca (
    his_id NUMBER(10) NOT NULL,
    his_prc_id NUMBER(10) NOT NULL,
    his_tipo_acao VARCHAR2(50) NOT NULL,
    his_descricao VARCHAR2(500) NOT NULL,
    his_observacoes VARCHAR2(4000),
    his_usuario NUMBER(10),
    his_data TIMESTAMP DEFAULT SYSDATE,
    his_contato_tipo VARCHAR2(50),
    his_contato_detalhes VARCHAR2(500),
    created_at TIMESTAMP DEFAULT SYSDATE,
    updated_at TIMESTAMP DEFAULT SYSDATE,
    CONSTRAINT pk_cob_historico_cobranca PRIMARY KEY (his_id),
    CONSTRAINT fk_cob_historico_processo FOREIGN KEY (his_prc_id) REFERENCES uniod.cob_processo_cobranca(prc_id) ON DELETE CASCADE,
    CONSTRAINT fk_cob_historico_usuario FOREIGN KEY (his_usuario) REFERENCES sigoweb.fwk_usuarios(usu_codigo),
    CONSTRAINT ck_historico_tipo CHECK (his_tipo_acao IN ('Criação', 'Contato', 'Negociação', 'Acordo', 'Pagamento', 'Cancelamento', 'Alteração'))
);

-- Tabela: cob_acordo_fatura (Tabela Pivot)
-- Descrição: Relacionamento muitos-para-muitos entre acordos e faturas
CREATE TABLE uniod.cob_acordo_fatura (
    afc_aco_id NUMBER(10) NOT NULL,
    afc_fco_id NUMBER(10) NOT NULL,
    created_at TIMESTAMP DEFAULT SYSDATE,
    CONSTRAINT pk_cob_acordo_fatura PRIMARY KEY (afc_aco_id, afc_fco_id),
    CONSTRAINT fk_cob_acordo_fatura_acordo FOREIGN KEY (afc_aco_id) REFERENCES uniod.cob_acordo_cobranca(aco_id) ON DELETE CASCADE,
    CONSTRAINT fk_cob_acordo_fatura_fatura FOREIGN KEY (afc_fco_id) REFERENCES uniod.cob_fatura_cobranca(fco_id) ON DELETE CASCADE
);

-- =====================================================
-- 3. TRIGGERS PARA AUTO-INCREMENT
-- =====================================================

-- Trigger para cob_processo_cobranca
CREATE OR REPLACE TRIGGER uniod.trg_cob_processo_cobranca_id
    BEFORE INSERT ON uniod.cob_processo_cobranca
    FOR EACH ROW
BEGIN
    IF :NEW.prc_id IS NULL THEN
        SELECT uniod.seq_cob_processo_cobranca.NEXTVAL
        INTO :NEW.prc_id
        FROM DUAL;
    END IF;
END;
/

-- Trigger para cob_fatura_cobranca
CREATE OR REPLACE TRIGGER uniod.trg_cob_fatura_cobranca_id
    BEFORE INSERT ON uniod.cob_fatura_cobranca
    FOR EACH ROW
BEGIN
    IF :NEW.fco_id IS NULL THEN
        SELECT uniod.seq_cob_fatura_cobranca.NEXTVAL
        INTO :NEW.fco_id
        FROM DUAL;
    END IF;
END;
/

-- Trigger para cob_acordo_cobranca
CREATE OR REPLACE TRIGGER uniod.trg_cob_acordo_cobranca_id
    BEFORE INSERT ON uniod.cob_acordo_cobranca
    FOR EACH ROW
BEGIN
    IF :NEW.aco_id IS NULL THEN
        SELECT uniod.seq_cob_acordo_cobranca.NEXTVAL
        INTO :NEW.aco_id
        FROM DUAL;
    END IF;
END;
/

-- Trigger para cob_boleto_cobranca
CREATE OR REPLACE TRIGGER uniod.trg_cob_boleto_cobranca_id
    BEFORE INSERT ON uniod.cob_boleto_cobranca
    FOR EACH ROW
BEGIN
    IF :NEW.bol_id IS NULL THEN
        SELECT uniod.seq_cob_boleto_cobranca.NEXTVAL
        INTO :NEW.bol_id
        FROM DUAL;
    END IF;
END;
/

-- Trigger para cob_historico_cobranca
CREATE OR REPLACE TRIGGER uniod.trg_cob_historico_cobranca_id
    BEFORE INSERT ON uniod.cob_historico_cobranca
    FOR EACH ROW
BEGIN
    IF :NEW.his_id IS NULL THEN
        SELECT uniod.seq_cob_historico_cobranca.NEXTVAL
        INTO :NEW.his_id
        FROM DUAL;
    END IF;
END;
/

-- Trigger para atualizar updated_at em cob_processo_cobranca
CREATE OR REPLACE TRIGGER uniod.trg_cob_proc_cob_updated
    BEFORE UPDATE ON uniod.cob_processo_cobranca
    FOR EACH ROW
BEGIN
    :NEW.updated_at := SYSDATE;
END;
/

-- Trigger para atualizar updated_at em cob_fatura_cobranca
CREATE OR REPLACE TRIGGER uniod.trg_cob_fat_cob_updated
    BEFORE UPDATE ON uniod.cob_fatura_cobranca
    FOR EACH ROW
BEGIN
    :NEW.updated_at := SYSDATE;
END;
/

-- Trigger para atualizar updated_at em cob_acordo_cobranca
CREATE OR REPLACE TRIGGER uniod.trg_cob_acordo_cob_updated
    BEFORE UPDATE ON uniod.cob_acordo_cobranca
    FOR EACH ROW
BEGIN
    :NEW.updated_at := SYSDATE;
END;
/

-- Trigger para atualizar updated_at em cob_boleto_cobranca
CREATE OR REPLACE TRIGGER uniod.trg_cob_boleto_cob_updated
    BEFORE UPDATE ON uniod.cob_boleto_cobranca
    FOR EACH ROW
BEGIN
    :NEW.updated_at := SYSDATE;
END;
/

-- Trigger para atualizar updated_at em cob_historico_cobranca
CREATE OR REPLACE TRIGGER uniod.trg_cob_hist_cob_updated
    BEFORE UPDATE ON uniod.cob_historico_cobranca
    FOR EACH ROW
BEGIN
    :NEW.updated_at := SYSDATE;
END;
/

-- =====================================================
-- 4. ÍNDICES PARA PERFORMANCE
-- =====================================================

-- Índices para cob_processo_cobranca
CREATE INDEX uniod.idx_cob_processo_plano ON uniod.cob_processo_cobranca(prc_pla_codigo);
CREATE INDEX uniod.idx_cob_processo_status ON uniod.cob_processo_cobranca(prc_status);
CREATE INDEX uniod.idx_cob_processo_data_inicio ON uniod.cob_processo_cobranca(prc_data_inicio);

-- Índices para cob_fatura_cobranca
CREATE INDEX uniod.idx_cob_fatura_processo ON uniod.cob_fatura_cobranca(fco_prc_id);
CREATE INDEX uniod.idx_cob_fatura_numero ON uniod.cob_fatura_cobranca(fco_fat_numero);
CREATE INDEX uniod.idx_cob_fatura_status ON uniod.cob_fatura_cobranca(fco_status);

-- Índices para cob_acordo_cobranca
CREATE INDEX uniod.idx_cob_acordo_processo ON uniod.cob_acordo_cobranca(aco_prc_id);
CREATE INDEX uniod.idx_cob_acordo_status ON uniod.cob_acordo_cobranca(aco_status);
CREATE INDEX uniod.idx_cob_acordo_data ON uniod.cob_acordo_cobranca(aco_data_acordo);

-- Índices para cob_boleto_cobranca
CREATE INDEX uniod.idx_cob_boleto_acordo ON uniod.cob_boleto_cobranca(bol_aco_id);
CREATE INDEX uniod.idx_cob_boleto_status ON uniod.cob_boleto_cobranca(bol_status);
CREATE INDEX uniod.idx_cob_boleto_vencimento ON uniod.cob_boleto_cobranca(bol_data_vencimento);
CREATE INDEX uniod.idx_cob_boleto_numero ON uniod.cob_boleto_cobranca(bol_numero);

-- Índices para cob_historico_cobranca
CREATE INDEX uniod.idx_cob_historico_processo ON uniod.cob_historico_cobranca(his_prc_id);
CREATE INDEX uniod.idx_cob_historico_data ON uniod.cob_historico_cobranca(his_data);
CREATE INDEX uniod.idx_cob_historico_tipo ON uniod.cob_historico_cobranca(his_tipo_acao);
CREATE INDEX uniod.idx_cob_historico_usuario ON uniod.cob_historico_cobranca(his_usuario);

-- Índices para cob_acordo_fatura
CREATE INDEX uniod.idx_cob_acordo_fatura_acordo ON uniod.cob_acordo_fatura(afc_aco_id);
CREATE INDEX uniod.idx_cob_acordo_fatura_fatura ON uniod.cob_acordo_fatura(afc_fco_id);

-- =====================================================
-- 5. COMENTÁRIOS NAS TABELAS E COLUNAS
-- =====================================================

COMMENT ON TABLE uniod.cob_processo_cobranca IS 'Processos de cobrança de empresas PJ inadimplentes';
COMMENT ON COLUMN uniod.cob_processo_cobranca.prc_id IS 'ID único do processo';
COMMENT ON COLUMN uniod.cob_processo_cobranca.prc_pla_codigo IS 'Código do plano (empresa PJ)';
COMMENT ON COLUMN uniod.cob_processo_cobranca.prc_status IS 'Status do processo: Aberta, Em Negociação, Acordo Fechado, Quitada, Cancelada, Incobrável';
COMMENT ON COLUMN uniod.cob_processo_cobranca.prc_valor_total IS 'Valor total das faturas vinculadas';
COMMENT ON COLUMN uniod.cob_processo_cobranca.prc_data_inicio IS 'Data de criação do processo';
COMMENT ON COLUMN uniod.cob_processo_cobranca.prc_data_fechamento IS 'Data de fechamento do processo (quando quitado/cancelado)';

COMMENT ON TABLE uniod.cob_fatura_cobranca IS 'Faturas vinculadas aos processos de cobrança';
COMMENT ON COLUMN uniod.cob_fatura_cobranca.fco_id IS 'ID único da fatura no processo';
COMMENT ON COLUMN uniod.cob_fatura_cobranca.fco_prc_id IS 'ID do processo de cobrança';
COMMENT ON COLUMN uniod.cob_fatura_cobranca.fco_fat_numero IS 'Número da fatura original';
COMMENT ON COLUMN uniod.cob_fatura_cobranca.fco_valor_original IS 'Valor original da fatura';
COMMENT ON COLUMN uniod.cob_fatura_cobranca.fco_valor_juros_multa IS 'Valor de juros e multa acumulados';
COMMENT ON COLUMN uniod.cob_fatura_cobranca.fco_valor_atualizado IS 'Valor total atualizado (original + juros/multa)';
COMMENT ON COLUMN uniod.cob_fatura_cobranca.fco_status IS 'Status: Aberta, Negociada, Quitada';

COMMENT ON TABLE uniod.cob_acordo_cobranca IS 'Acordos de negociação criados para os processos';
COMMENT ON COLUMN uniod.cob_acordo_cobranca.aco_id IS 'ID único do acordo';
COMMENT ON COLUMN uniod.cob_acordo_cobranca.aco_prc_id IS 'ID do processo de cobrança';
COMMENT ON COLUMN uniod.cob_acordo_cobranca.aco_tipo IS 'Tipo: Vista ou Parcelado';
COMMENT ON COLUMN uniod.cob_acordo_cobranca.aco_valor_total IS 'Valor total do acordo';
COMMENT ON COLUMN uniod.cob_acordo_cobranca.aco_valor_desconto IS 'Valor de desconto concedido';
COMMENT ON COLUMN uniod.cob_acordo_cobranca.aco_valor_juros IS 'Valor de juros aplicado';
COMMENT ON COLUMN uniod.cob_acordo_cobranca.aco_numero_parcelas IS 'Número de parcelas (se parcelado)';
COMMENT ON COLUMN uniod.cob_acordo_cobranca.aco_status IS 'Status: Pendente, Aprovado, Quitado, Cancelado';

COMMENT ON TABLE uniod.cob_boleto_cobranca IS 'Boletos gerados para os acordos';
COMMENT ON COLUMN uniod.cob_boleto_cobranca.bol_id IS 'ID único do boleto';
COMMENT ON COLUMN uniod.cob_boleto_cobranca.bol_aco_id IS 'ID do acordo';
COMMENT ON COLUMN uniod.cob_boleto_cobranca.bol_numero IS 'Número do boleto (gerado pelo sistema de boletos)';
COMMENT ON COLUMN uniod.cob_boleto_cobranca.bol_numero_parcela IS 'Número da parcela (se parcelado)';
COMMENT ON COLUMN uniod.cob_boleto_cobranca.bol_valor IS 'Valor do boleto';
COMMENT ON COLUMN uniod.cob_boleto_cobranca.bol_data_vencimento IS 'Data de vencimento';
COMMENT ON COLUMN uniod.cob_boleto_cobranca.bol_status IS 'Status: Emitido, Pago, Vencido, Cancelado';

COMMENT ON TABLE uniod.cob_historico_cobranca IS 'Histórico de ações realizadas nos processos de cobrança';
COMMENT ON COLUMN uniod.cob_historico_cobranca.his_id IS 'ID único do registro histórico';
COMMENT ON COLUMN uniod.cob_historico_cobranca.his_prc_id IS 'ID do processo de cobrança';
COMMENT ON COLUMN uniod.cob_historico_cobranca.his_tipo_acao IS 'Tipo de ação: Criação, Contato, Negociação, Acordo, Pagamento, Cancelamento, Alteração';
COMMENT ON COLUMN uniod.cob_historico_cobranca.his_descricao IS 'Descrição resumida da ação';
COMMENT ON COLUMN uniod.cob_historico_cobranca.his_contato_tipo IS 'Tipo de contato: Ligação, WhatsApp, E-mail';
COMMENT ON COLUMN uniod.cob_historico_cobranca.his_contato_detalhes IS 'Detalhes do contato (número, e-mail, etc)';

COMMENT ON TABLE uniod.cob_acordo_fatura IS 'Relacionamento muitos-para-muitos entre acordos e faturas';

-- =====================================================
-- ALTER TABLE: CORREÇÃO DA FK DE HIS_USUARIO
-- =====================================================
-- Altera a chave estrangeira de HIS_USUARIO para referenciar
-- sigoweb.fwk_usuarios ao invés de uniod.tb_usuario
-- =====================================================

-- Remove a constraint antiga
ALTER TABLE uniod.cob_historico_cobranca
DROP CONSTRAINT fk_cob_historico_usuario;

-- Adiciona a nova constraint referenciando sigoweb.fwk_usuarios
ALTER TABLE uniod.cob_historico_cobranca
ADD CONSTRAINT fk_cob_historico_usuario 
FOREIGN KEY (his_usuario) REFERENCES sigoweb.fwk_usuarios(usu_codigo);

-- =====================================================
-- FIM DO SCRIPT
-- =====================================================
