CREATE TABLE aws_documents (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NULL,
    lead_id INT UNSIGNED NULL,
    doc_type ENUM('CONTRACHEQUE','RG','CNH','OUTRO') NOT NULL DEFAULT 'OUTRO',
    original_filename VARCHAR(255) NOT NULL,
    mime_type VARCHAR(120) NOT NULL,
    size_bytes INT UNSIGNED NOT NULL,
    sha256_hash CHAR(64) NOT NULL,
    status ENUM('UPLOADED','S3_UPLOADED','PROCESSING','DONE','ERROR') NOT NULL DEFAULT 'UPLOADED',
    s3_bucket VARCHAR(120) NOT NULL,
    s3_key VARCHAR(512) NOT NULL,
    textract_job_id VARCHAR(120) NULL,
    error_message TEXT NULL,
    extracted_name VARCHAR(160) NULL,
    extracted_cpf VARCHAR(20) NULL,
    extracted_registration VARCHAR(40) NULL,
    extracted_birth_date DATE NULL,
    extracted_payment_date DATE NULL,
    extracted_document_number VARCHAR(40) NULL,
    extracted_document_org VARCHAR(80) NULL,
    extracted_document_state VARCHAR(10) NULL,
    extracted_competence VARCHAR(15) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_aws_documents_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE SET NULL,
    CONSTRAINT fk_aws_documents_lead FOREIGN KEY (lead_id) REFERENCES leads (id) ON DELETE SET NULL,
    INDEX idx_aws_documents_hash (sha256_hash),
    INDEX idx_aws_documents_status (status),
    INDEX idx_aws_documents_lead (lead_id),
    INDEX idx_aws_documents_cpf (extracted_cpf),
    INDEX idx_aws_documents_name (extracted_name),
    INDEX idx_aws_documents_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE aws_document_textract_raw (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    document_id INT UNSIGNED NOT NULL,
    raw_json LONGTEXT NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_aws_document_raw_document FOREIGN KEY (document_id) REFERENCES aws_documents (id) ON DELETE CASCADE,
    INDEX idx_aws_document_raw_document (document_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE aws_document_items (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    document_id INT UNSIGNED NOT NULL,
    source ENUM('TABLE','FORM','LINE') NOT NULL,
    page_num INT NOT NULL DEFAULT 1,
    row_num INT NULL,
    col_num INT NULL,
    key_text VARCHAR(255) NULL,
    value_text TEXT NULL,
    text TEXT NULL,
    confidence DECIMAL(6,4) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_aws_document_items_document FOREIGN KEY (document_id) REFERENCES aws_documents (id) ON DELETE CASCADE,
    INDEX idx_aws_document_items_document (document_id),
    INDEX idx_aws_document_items_source (source)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;