CREATE TABLE IF NOT EXISTS production_analysis_uploads (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NULL,
    original_name VARCHAR(255) NOT NULL,
    stored_name VARCHAR(255) NOT NULL,
    status ENUM('pending', 'processing', 'completed', 'failed') NOT NULL DEFAULT 'pending',
    rows_total INT UNSIGNED NOT NULL DEFAULT 0,
    rows_imported INT UNSIGNED NOT NULL DEFAULT 0,
    rows_rejected INT UNSIGNED NOT NULL DEFAULT 0,
    total_bruto DECIMAL(15, 2) NOT NULL DEFAULT 0.00,
    total_liquido DECIMAL(15, 2) NOT NULL DEFAULT 0.00,
    error_log TEXT NULL,
    finished_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_prodan_uploads_status (status),
    INDEX idx_prodan_uploads_created_at (created_at),
    CONSTRAINT fk_prodan_uploads_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS production_analysis_records (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    upload_id BIGINT UNSIGNED NOT NULL,
    bank_name VARCHAR(150) NOT NULL,
    proposal_number VARCHAR(100) DEFAULT '',
    digitador VARCHAR(150) DEFAULT '',
    commission_flag VARCHAR(10) DEFAULT '',
    emission_date DATE NULL,
    gross_value DECIMAL(15, 2) NOT NULL DEFAULT 0.00,
    net_value DECIMAL(15, 2) NOT NULL DEFAULT 0.00,
    status_code VARCHAR(50) DEFAULT '',
    status_name VARCHAR(100) DEFAULT '',
    observation VARCHAR(500) DEFAULT '',
    partner_code VARCHAR(100) DEFAULT '',
    convenio VARCHAR(150) DEFAULT '',
    customer_cpf VARCHAR(20) DEFAULT '',
    description_one VARCHAR(150) DEFAULT '',
    description_two VARCHAR(150) DEFAULT '',
    operation_type VARCHAR(150) DEFAULT '',
    search_terms TEXT NULL,
    raw_payload JSON NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_prodan_records_upload (upload_id),
    INDEX idx_prodan_records_bank (upload_id, bank_name),
    INDEX idx_prodan_records_user (upload_id, digitador),
    INDEX idx_prodan_records_status (upload_id, status_name),
    INDEX idx_prodan_records_operation (upload_id, operation_type),
    INDEX idx_prodan_records_date (upload_id, emission_date),
    CONSTRAINT fk_prodan_records_upload FOREIGN KEY (upload_id) REFERENCES production_analysis_uploads (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;