CREATE TABLE lead_inss_personal_profiles (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    lead_id INT UNSIGNED NOT NULL UNIQUE,
    cpf VARCHAR(20) NULL,
    full_name VARCHAR(150) NULL,
    birth_date DATE NULL,
    gender VARCHAR(10) NULL,
    phone_primary VARCHAR(30) NULL,
    phone_secondary VARCHAR(30) NULL,
    mother_name VARCHAR(150) NULL,
    father_name VARCHAR(150) NULL,
    rg_number VARCHAR(50) NULL,
    rg_issued_at DATE NULL,
    rg_issuer VARCHAR(50) NULL,
    rg_issuer_state CHAR(2) NULL,
    legal_representative VARCHAR(150) NULL,
    place_of_birth VARCHAR(150) NULL,
    place_of_birth_state CHAR(2) NULL,
    origin_payment_bank VARCHAR(80) NULL,
    origin_payment_agency VARCHAR(30) NULL,
    origin_payment_account VARCHAR(30) NULL,
    origin_payment_account_type VARCHAR(10) NULL,
    address_street VARCHAR(150) NULL,
    address_number VARCHAR(30) NULL,
    address_neighborhood VARCHAR(100) NULL,
    address_city VARCHAR(100) NULL,
    address_state CHAR(2) NULL,
    address_zip VARCHAR(20) NULL,
    email VARCHAR(150) NULL,
    patrimony_range VARCHAR(30) NULL,
    last_proposal_id INT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_lead_inss_personal_lead FOREIGN KEY (lead_id) REFERENCES leads (id) ON DELETE CASCADE,
    CONSTRAINT fk_lead_inss_personal_proposal FOREIGN KEY (last_proposal_id) REFERENCES loan_proposals (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE lead_inss_benefit_profiles (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    lead_id INT UNSIGNED NOT NULL UNIQUE,
    benefit_number VARCHAR(30) NULL,
    benefit_type VARCHAR(80) NULL,
    benefit_value DECIMAL(12,2) NULL,
    benefit_state CHAR(2) NULL,
    payment_method VARCHAR(50) NULL,
    payment_bank VARCHAR(120) NULL,
    last_proposal_id INT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_lead_inss_benefit_lead FOREIGN KEY (lead_id) REFERENCES leads (id) ON DELETE CASCADE,
    CONSTRAINT fk_lead_inss_benefit_proposal FOREIGN KEY (last_proposal_id) REFERENCES loan_proposals (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE lead_inss_contract_profiles (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    lead_id INT UNSIGNED NOT NULL UNIQUE,
    contracted_bank VARCHAR(80) NULL,
    operation_type VARCHAR(50) NULL,
    table_reference VARCHAR(80) NULL,
    installment_value DECIMAL(12,2) NULL,
    term_total INT NULL,
    term_remaining INT NULL,
    outstanding_balance DECIMAL(12,2) NULL,
    customer_value DECIMAL(12,2) NULL,
    last_proposal_id INT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_lead_inss_contract_lead FOREIGN KEY (lead_id) REFERENCES leads (id) ON DELETE CASCADE,
    CONSTRAINT fk_lead_inss_contract_proposal FOREIGN KEY (last_proposal_id) REFERENCES loan_proposals (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO lead_inss_personal_profiles (
    lead_id,
    cpf,
    full_name,
    birth_date,
    gender,
    phone_primary,
    phone_secondary,
    mother_name,
    father_name,
    rg_number,
    rg_issued_at,
    rg_issuer,
    rg_issuer_state,
    legal_representative,
    place_of_birth,
    place_of_birth_state,
    origin_payment_bank,
    origin_payment_agency,
    origin_payment_account,
    origin_payment_account_type,
    address_street,
    address_number,
    address_neighborhood,
    address_city,
    address_state,
    address_zip,
    email,
    patrimony_range,
    last_proposal_id,
    created_at,
    updated_at
)
SELECT
    proposals.lead_id,
    personal.cpf,
    personal.full_name,
    personal.birth_date,
    personal.gender,
    personal.phone_primary,
    personal.phone_secondary,
    personal.mother_name,
    personal.father_name,
    personal.rg_number,
    personal.rg_issued_at,
    personal.rg_issuer,
    personal.rg_issuer_state,
    personal.legal_representative,
    personal.place_of_birth,
    personal.place_of_birth_state,
    personal.origin_payment_bank,
    personal.origin_payment_agency,
    personal.origin_payment_account,
    personal.origin_payment_account_type,
    personal.address_street,
    personal.address_number,
    personal.address_neighborhood,
    personal.address_city,
    personal.address_state,
    personal.address_zip,
    personal.email,
    personal.patrimony_range,
    personal.proposal_id,
    COALESCE(personal.created_at, proposals.created_at, NOW()),
    COALESCE(personal.updated_at, proposals.updated_at, personal.created_at, proposals.created_at, NOW())
FROM loan_proposal_inss_personal personal
INNER JOIN loan_proposals proposals ON proposals.id = personal.proposal_id
WHERE proposals.lead_id IS NOT NULL
ORDER BY COALESCE(personal.updated_at, personal.created_at, proposals.updated_at, proposals.created_at)
ON DUPLICATE KEY UPDATE
    cpf = VALUES(cpf),
    full_name = VALUES(full_name),
    birth_date = VALUES(birth_date),
    gender = VALUES(gender),
    phone_primary = VALUES(phone_primary),
    phone_secondary = VALUES(phone_secondary),
    mother_name = VALUES(mother_name),
    father_name = VALUES(father_name),
    rg_number = VALUES(rg_number),
    rg_issued_at = VALUES(rg_issued_at),
    rg_issuer = VALUES(rg_issuer),
    rg_issuer_state = VALUES(rg_issuer_state),
    legal_representative = VALUES(legal_representative),
    place_of_birth = VALUES(place_of_birth),
    place_of_birth_state = VALUES(place_of_birth_state),
    origin_payment_bank = VALUES(origin_payment_bank),
    origin_payment_agency = VALUES(origin_payment_agency),
    origin_payment_account = VALUES(origin_payment_account),
    origin_payment_account_type = VALUES(origin_payment_account_type),
    address_street = VALUES(address_street),
    address_number = VALUES(address_number),
    address_neighborhood = VALUES(address_neighborhood),
    address_city = VALUES(address_city),
    address_state = VALUES(address_state),
    address_zip = VALUES(address_zip),
    email = VALUES(email),
    patrimony_range = VALUES(patrimony_range),
    last_proposal_id = VALUES(last_proposal_id),
    updated_at = VALUES(updated_at);

INSERT INTO lead_inss_benefit_profiles (
    lead_id,
    benefit_number,
    benefit_type,
    benefit_value,
    benefit_state,
    payment_method,
    payment_bank,
    last_proposal_id,
    created_at,
    updated_at
)
SELECT
    proposals.lead_id,
    benefit.benefit_number,
    benefit.benefit_type,
    benefit.benefit_value,
    benefit.benefit_state,
    benefit.payment_method,
    benefit.payment_bank,
    benefit.proposal_id,
    COALESCE(benefit.created_at, proposals.created_at, NOW()),
    COALESCE(benefit.updated_at, proposals.updated_at, benefit.created_at, proposals.created_at, NOW())
FROM loan_proposal_inss_benefits benefit
INNER JOIN loan_proposals proposals ON proposals.id = benefit.proposal_id
WHERE proposals.lead_id IS NOT NULL
ORDER BY COALESCE(benefit.updated_at, benefit.created_at, proposals.updated_at, proposals.created_at)
ON DUPLICATE KEY UPDATE
    benefit_number = VALUES(benefit_number),
    benefit_type = VALUES(benefit_type),
    benefit_value = VALUES(benefit_value),
    benefit_state = VALUES(benefit_state),
    payment_method = VALUES(payment_method),
    payment_bank = VALUES(payment_bank),
    last_proposal_id = VALUES(last_proposal_id),
    updated_at = VALUES(updated_at);

INSERT INTO lead_inss_contract_profiles (
    lead_id,
    contracted_bank,
    operation_type,
    table_reference,
    installment_value,
    term_total,
    term_remaining,
    outstanding_balance,
    customer_value,
    last_proposal_id,
    created_at,
    updated_at
)
SELECT
    proposals.lead_id,
    contract.contracted_bank,
    contract.operation_type,
    contract.table_reference,
    contract.installment_value,
    contract.term_total,
    contract.term_remaining,
    contract.outstanding_balance,
    contract.customer_value,
    contract.proposal_id,
    COALESCE(contract.created_at, proposals.created_at, NOW()),
    COALESCE(contract.updated_at, proposals.updated_at, contract.created_at, proposals.created_at, NOW())
FROM loan_proposal_inss_contracts contract
INNER JOIN loan_proposals proposals ON proposals.id = contract.proposal_id
WHERE proposals.lead_id IS NOT NULL
ORDER BY COALESCE(contract.updated_at, contract.created_at, proposals.updated_at, proposals.created_at)
ON DUPLICATE KEY UPDATE
    contracted_bank = VALUES(contracted_bank),
    operation_type = VALUES(operation_type),
    table_reference = VALUES(table_reference),
    installment_value = VALUES(installment_value),
    term_total = VALUES(term_total),
    term_remaining = VALUES(term_remaining),
    outstanding_balance = VALUES(outstanding_balance),
    customer_value = VALUES(customer_value),
    last_proposal_id = VALUES(last_proposal_id),
    updated_at = VALUES(updated_at);