CREATE TABLE loan_proposal_inss_personal (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    proposal_id INT UNSIGNED NOT NULL UNIQUE,
    cpf VARCHAR(20) NOT NULL,
    full_name VARCHAR(150) NOT NULL,
    birth_date DATE NULL,
    gender CHAR(1) NULL,
    phone_primary VARCHAR(20) NULL,
    phone_secondary VARCHAR(20) NULL,
    mother_name VARCHAR(150) NULL,
    father_name VARCHAR(150) NULL,
    rg_number VARCHAR(30) 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(120) NULL,
    origin_payment_agency VARCHAR(10) NULL,
    origin_payment_account VARCHAR(30) NULL,
    origin_payment_account_type VARCHAR(30) NULL,
    address_street VARCHAR(150) NULL,
    address_number VARCHAR(20) NULL,
    address_neighborhood VARCHAR(100) NULL,
    address_city VARCHAR(100) NULL,
    address_state CHAR(2) NULL,
    address_zip VARCHAR(15) NULL,
    email VARCHAR(150) NULL,
    patrimony_range VARCHAR(60) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_inss_personal_proposal FOREIGN KEY (proposal_id) REFERENCES loan_proposals (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE loan_proposal_inss_benefits (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    proposal_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(60) NULL,
    payment_bank VARCHAR(120) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_inss_benefit_proposal FOREIGN KEY (proposal_id) REFERENCES loan_proposals (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE loan_proposal_inss_contracts (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    proposal_id INT UNSIGNED NOT NULL UNIQUE,
    contracted_bank VARCHAR(120) NULL,
    operation_type VARCHAR(80) NULL,
    table_reference VARCHAR(80) NULL,
    installment_value DECIMAL(12,2) NULL,
    term_total SMALLINT UNSIGNED NULL,
    term_remaining SMALLINT UNSIGNED NULL,
    outstanding_balance DECIMAL(12,2) NULL,
    customer_value DECIMAL(12,2) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_inss_contract_proposal FOREIGN KEY (proposal_id) REFERENCES loan_proposals (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;