-- Customer Statement Batch Tables
-- Run this SQL to set up the necessary tables for the statement splitting system

CREATE TABLE IF NOT EXISTS customer_statement_batches (
    id_batch INT AUTO_INCREMENT PRIMARY KEY,
    original_filename VARCHAR(255) NOT NULL,
    uploaded_by VARCHAR(255),
    batch_status ENUM('processing', 'completed', 'error') DEFAULT 'processing',
    statement_count INT DEFAULT 0,
    page_count INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX (batch_status),
    INDEX (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS customer_statements (
    id_statement INT AUTO_INCREMENT PRIMARY KEY,
    id_batch INT NOT NULL,
    customer_account VARCHAR(100),
    filename VARCHAR(255) NOT NULL,
    page_count INT DEFAULT 1,
    file_path VARCHAR(500),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (id_batch) REFERENCES customer_statement_batches(id_batch) ON DELETE CASCADE,
    INDEX (id_batch),
    INDEX (customer_account),
    INDEX (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;