CREATE TABLE margin_consult_uploads (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NULL,
    original_name VARCHAR(255) NOT NULL,
    stored_name VARCHAR(255) NOT NULL,
    source_type VARCHAR(20) NOT NULL,
    status VARCHAR(20) NOT NULL,
    rows_total INT NOT NULL DEFAULT 0,
    rows_imported INT NOT NULL DEFAULT 0,
    rows_rejected INT NOT NULL DEFAULT 0,
    warnings TEXT NULL,
    dependents_count INT NOT NULL DEFAULT 0,
    is_retired TINYINT(1) NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL,
    finished_at DATETIME NULL,
    updated_at DATETIME NULL,
    CONSTRAINT fk_margin_consult_uploads_user
        FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE margin_consult_documents (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    upload_id INT UNSIGNED NOT NULL,
    original_name VARCHAR(255) NOT NULL,
    stored_name VARCHAR(255) NOT NULL,
    mime_type VARCHAR(100) NOT NULL,
    raw_text LONGTEXT NULL,
    created_at DATETIME NOT NULL,
    CONSTRAINT fk_margin_consult_documents_upload
        FOREIGN KEY (upload_id) REFERENCES margin_consult_uploads(id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE margin_consult_catalog (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(20) NOT NULL,
    name VARCHAR(255) NOT NULL,
    observation VARCHAR(50) NULL,
    published_info TEXT NULL,
    law_reference VARCHAR(255) NULL,
    extra_info TEXT NULL,
    updated_at DATETIME NOT NULL,
    UNIQUE KEY uq_margin_consult_catalog_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE margin_consult_rows (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    upload_id INT UNSIGNED NOT NULL,
    row_index INT NOT NULL,
    code VARCHAR(20) NULL,
    description VARCHAR(255) NOT NULL,
    observation VARCHAR(50) NULL,
    amount DECIMAL(13,2) NOT NULL DEFAULT 0,
    kind VARCHAR(20) NOT NULL DEFAULT 'unknown',
    category VARCHAR(30) NULL,
    raw_payload TEXT NULL,
    created_at DATETIME NOT NULL,
    CONSTRAINT fk_margin_consult_rows_upload
        FOREIGN KEY (upload_id) REFERENCES margin_consult_uploads(id)
        ON DELETE CASCADE,
    KEY idx_margin_consult_rows_upload (upload_id),
    KEY idx_margin_consult_rows_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE margin_consult_results (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    upload_id INT UNSIGNED NOT NULL,
    base_value DECIMAL(13,2) NOT NULL DEFAULT 0,
    saldo_value DECIMAL(13,2) NOT NULL DEFAULT 0,
    compulsory_total DECIMAL(13,2) NOT NULL DEFAULT 0,
    facultative_total DECIMAL(13,2) NOT NULL DEFAULT 0,
    irrf_base DECIMAL(13,2) NOT NULL DEFAULT 0,
    irrf_value DECIMAL(13,2) NOT NULL DEFAULT 0,
    previdencia_value DECIMAL(13,2) NOT NULL DEFAULT 0,
    margin_bruta_30 DECIMAL(13,2) NOT NULL DEFAULT 0,
    margin_bruta_35 DECIMAL(13,2) NOT NULL DEFAULT 0,
    margin_avail_5 DECIMAL(13,2) NOT NULL DEFAULT 0,
    margin_avail_15 DECIMAL(13,2) NOT NULL DEFAULT 0,
    margin_avail_30 DECIMAL(13,2) NOT NULL DEFAULT 0,
    margin_avail_35 DECIMAL(13,2) NOT NULL DEFAULT 0,
    calculation_source VARCHAR(20) NOT NULL DEFAULT 'estimated',
    warnings TEXT NULL,
    created_at DATETIME NOT NULL,
    CONSTRAINT fk_margin_consult_results_upload
        FOREIGN KEY (upload_id) REFERENCES margin_consult_uploads(id)
        ON DELETE CASCADE,
    UNIQUE KEY uq_margin_consult_results_upload (upload_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;