-- Schema for Template Engine
CREATE SCHEMA IF NOT EXISTS templates;

-- 1. documents table
CREATE TABLE IF NOT EXISTS templates.documents (
    pk_document_id BIGSERIAL PRIMARY KEY,
    document_name VARCHAR(500),
    document_type VARCHAR(100),
    file_name VARCHAR(500),
    file_hash VARCHAR(500),
    page_count INTEGER,
    status VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Index for quick lookups
CREATE INDEX idx_documents_hash ON templates.documents(file_hash);

-- 2. document_layout table
CREATE TABLE IF NOT EXISTS templates.document_layout (
    pk_document_data_id BIGSERIAL PRIMARY KEY,
    fk_document_id BIGINT NOT NULL,
    page_no INTEGER NOT NULL,
    text_value TEXT,
    block_type VARCHAR(100),
    parent_block_id BIGINT,
    x_coordinate NUMERIC,
    y_coordinate NUMERIC,
    width NUMERIC,
    height NUMERIC,
    confidence NUMERIC,
    sequence_no INTEGER,
    layout_path TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    CONSTRAINT fk_dl_document FOREIGN KEY (fk_document_id) REFERENCES templates.documents(pk_document_id) ON DELETE CASCADE,
    CONSTRAINT fk_dl_parent FOREIGN KEY (parent_block_id) REFERENCES templates.document_layout(pk_document_data_id) ON DELETE SET NULL
);

CREATE INDEX idx_doc_layout_doc_id ON templates.document_layout(fk_document_id);
CREATE INDEX idx_doc_layout_page ON templates.document_layout(page_no);

-- 3. templates table
CREATE TABLE IF NOT EXISTS templates.templates (
    pk_template_id BIGSERIAL PRIMARY KEY,
    template_name VARCHAR(255) NOT NULL,
    template_fingerprint TEXT,
    active_flag BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_templates_name ON templates.templates(template_name);

-- 4. template_fields table
CREATE TABLE IF NOT EXISTS templates.template_fields (
    pk_template_field_id BIGSERIAL PRIMARY KEY,
    fk_template_id BIGINT NOT NULL,
    field_label VARCHAR(255) NOT NULL,
    field_type VARCHAR(100) NOT NULL,
    display_order INTEGER,
    required_flag BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    CONSTRAINT fk_tf_template FOREIGN KEY (fk_template_id) REFERENCES templates.templates(pk_template_id) ON DELETE CASCADE
);

CREATE INDEX idx_template_fields_tmpl_id ON templates.template_fields(fk_template_id);

-- 5. template_fields_mapping table
CREATE TABLE IF NOT EXISTS templates.template_fields_mapping (
    pk_mapping_id BIGSERIAL PRIMARY KEY,
    fk_template_id BIGINT NOT NULL,
    fk_template_field_id BIGINT NOT NULL,
    fk_document_data_id BIGINT,
    page_no INTEGER,
    x_coordinate NUMERIC,
    y_coordinate NUMERIC,
    width NUMERIC,
    height NUMERIC,
    mapping_confidence NUMERIC,
    layout_path TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    CONSTRAINT fk_tfm_template FOREIGN KEY (fk_template_id) REFERENCES templates.templates(pk_template_id) ON DELETE CASCADE,
    CONSTRAINT fk_tfm_field FOREIGN KEY (fk_template_field_id) REFERENCES templates.template_fields(pk_template_field_id) ON DELETE CASCADE,
    CONSTRAINT fk_tfm_doc_data FOREIGN KEY (fk_document_data_id) REFERENCES templates.document_layout(pk_document_data_id) ON DELETE SET NULL
);

CREATE INDEX idx_mapping_tmpl_id ON templates.template_fields_mapping(fk_template_id);
CREATE INDEX idx_mapping_field_id ON templates.template_fields_mapping(fk_template_field_id);

-- 6. template_recognition_history table
CREATE TABLE IF NOT EXISTS templates.template_recognition_history (
    pk_history_id BIGSERIAL PRIMARY KEY,
    fk_template_id BIGINT NOT NULL,
    fk_document_id BIGINT NOT NULL,
    recognition_score NUMERIC,
    matched_flag BOOLEAN,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    CONSTRAINT fk_trh_template FOREIGN KEY (fk_template_id) REFERENCES templates.templates(pk_template_id) ON DELETE CASCADE,
    CONSTRAINT fk_trh_document FOREIGN KEY (fk_document_id) REFERENCES templates.documents(pk_document_id) ON DELETE CASCADE
);

CREATE INDEX idx_recognition_history_doc ON templates.template_recognition_history(fk_document_id);