-- DocEngine: Complete table creation script
-- Schema: admin
-- Database: ocr

SET search_path TO admin, public;

-- ============================================
-- Users & Authentication
-- ============================================

CREATE TABLE IF NOT EXISTS admin.users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    username VARCHAR(150) NOT NULL UNIQUE,
    email VARCHAR(255) NOT NULL UNIQUE,
    hashed_password VARCHAR(255) NOT NULL,
    full_name VARCHAR(255),
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    is_superuser BOOLEAN NOT NULL DEFAULT FALSE,
    last_login TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS admin.roles (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(50) NOT NULL UNIQUE,
    description TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS admin.user_roles (
    user_id UUID NOT NULL REFERENCES admin.users(id) ON DELETE CASCADE,
    role_id UUID NOT NULL REFERENCES admin.roles(id) ON DELETE CASCADE,
    PRIMARY KEY (user_id, role_id)
);

CREATE TABLE IF NOT EXISTS admin.refresh_tokens (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES admin.users(id) ON DELETE CASCADE,
    token VARCHAR(512) NOT NULL UNIQUE,
    expires_at TIMESTAMPTZ NOT NULL,
    revoked BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS admin.audit_logs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID REFERENCES admin.users(id) ON DELETE SET NULL,
    action VARCHAR(100) NOT NULL,
    resource_type VARCHAR(100) NOT NULL,
    resource_id VARCHAR(255),
    details TEXT,
    ip_address VARCHAR(45),
    user_agent VARCHAR(512),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- ============================================
-- Documents
-- ============================================

CREATE TABLE IF NOT EXISTS admin.documents (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    filename VARCHAR(500) NOT NULL,
    original_filename VARCHAR(500) NOT NULL,
    content_type VARCHAR(100) NOT NULL,
    file_size BIGINT NOT NULL,
    checksum VARCHAR(128) NOT NULL,
    storage_path VARCHAR(1024) NOT NULL,
    status VARCHAR(50) NOT NULL DEFAULT 'pending',
    page_count INTEGER,
    is_scanned BOOLEAN,
    document_metadata JSONB,
    error_message TEXT,
    uploaded_by UUID REFERENCES admin.users(id) ON DELETE SET NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS admin.document_pages (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    document_id UUID NOT NULL REFERENCES admin.documents(id) ON DELETE CASCADE,
    page_number INTEGER NOT NULL,
    width DOUBLE PRECISION NOT NULL,
    height DOUBLE PRECISION NOT NULL,
    image_path VARCHAR(1024),
    text_content TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS admin.document_text_blocks (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    page_id UUID NOT NULL REFERENCES admin.document_pages(id) ON DELETE CASCADE,
    text TEXT NOT NULL,
    x DOUBLE PRECISION NOT NULL,
    y DOUBLE PRECISION NOT NULL,
    width DOUBLE PRECISION NOT NULL,
    height DOUBLE PRECISION NOT NULL,
    confidence DOUBLE PRECISION,
    font_family VARCHAR(255),
    font_size DOUBLE PRECISION,
    font_color VARCHAR(50),
    font_style VARCHAR(50),
    block_type VARCHAR(50) NOT NULL DEFAULT 'text',
    sequence INTEGER NOT NULL DEFAULT 0,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS admin.document_images (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    page_id UUID NOT NULL REFERENCES admin.document_pages(id) ON DELETE CASCADE,
    x DOUBLE PRECISION NOT NULL,
    y DOUBLE PRECISION NOT NULL,
    width DOUBLE PRECISION NOT NULL,
    height DOUBLE PRECISION NOT NULL,
    image_path VARCHAR(1024) NOT NULL,
    image_type VARCHAR(50) NOT NULL DEFAULT 'figure',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS admin.document_tables (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    page_id UUID NOT NULL REFERENCES admin.document_pages(id) ON DELETE CASCADE,
    x DOUBLE PRECISION NOT NULL,
    y DOUBLE PRECISION NOT NULL,
    width DOUBLE PRECISION NOT NULL,
    height DOUBLE PRECISION NOT NULL,
    rows INTEGER NOT NULL,
    columns INTEGER NOT NULL,
    data JSONB,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- ============================================
-- Templates
-- ============================================

CREATE TABLE IF NOT EXISTS admin.document_formats (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(255) NOT NULL,
    description TEXT,
    page_width DOUBLE PRECISION NOT NULL,
    page_height DOUBLE PRECISION NOT NULL,
    page_count INTEGER NOT NULL DEFAULT 1,
    margin_top DOUBLE PRECISION NOT NULL DEFAULT 72.0,
    margin_right DOUBLE PRECISION NOT NULL DEFAULT 72.0,
    margin_bottom DOUBLE PRECISION NOT NULL DEFAULT 72.0,
    margin_left DOUBLE PRECISION NOT NULL DEFAULT 72.0,
    fingerprint JSONB,
    source_document_id UUID REFERENCES admin.documents(id) ON DELETE SET NULL,
    version INTEGER NOT NULL DEFAULT 1,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_by UUID REFERENCES admin.users(id) ON DELETE SET NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS admin.document_cells (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    format_id UUID NOT NULL REFERENCES admin.document_formats(id) ON DELETE CASCADE,
    page_number INTEGER NOT NULL,
    x DOUBLE PRECISION NOT NULL,
    y DOUBLE PRECISION NOT NULL,
    width DOUBLE PRECISION NOT NULL,
    height DOUBLE PRECISION NOT NULL,
    row_no INTEGER NOT NULL DEFAULT 0,
    column_no INTEGER NOT NULL DEFAULT 0,
    data_type VARCHAR(50) NOT NULL DEFAULT 'text',
    font_family VARCHAR(255),
    font_size DOUBLE PRECISION,
    font_style VARCHAR(50),
    font_color VARCHAR(50),
    background_color VARCHAR(50),
    border_top VARCHAR(100),
    border_right VARCHAR(100),
    border_bottom VARCHAR(100),
    border_left VARCHAR(100),
    padding_top DOUBLE PRECISION NOT NULL DEFAULT 0.0,
    padding_right DOUBLE PRECISION NOT NULL DEFAULT 0.0,
    padding_bottom DOUBLE PRECISION NOT NULL DEFAULT 0.0,
    padding_left DOUBLE PRECISION NOT NULL DEFAULT 0.0,
    alignment VARCHAR(20) NOT NULL DEFAULT 'left',
    vertical_alignment VARCHAR(20) NOT NULL DEFAULT 'top',
    rowspan INTEGER NOT NULL DEFAULT 1,
    colspan INTEGER NOT NULL DEFAULT 1,
    static_text TEXT,
    field_name VARCHAR(255),
    sequence INTEGER NOT NULL DEFAULT 0,
    is_dynamic BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS admin.document_regions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    format_id UUID NOT NULL REFERENCES admin.document_formats(id) ON DELETE CASCADE,
    page_number INTEGER NOT NULL,
    region_type VARCHAR(50) NOT NULL,
    x DOUBLE PRECISION NOT NULL,
    y DOUBLE PRECISION NOT NULL,
    width DOUBLE PRECISION NOT NULL,
    height DOUBLE PRECISION NOT NULL,
    content JSONB,
    sequence INTEGER NOT NULL DEFAULT 0,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS admin.table_formats (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    format_id UUID NOT NULL REFERENCES admin.document_formats(id) ON DELETE CASCADE,
    page_number INTEGER NOT NULL,
    x DOUBLE PRECISION NOT NULL,
    y DOUBLE PRECISION NOT NULL,
    width DOUBLE PRECISION NOT NULL,
    height DOUBLE PRECISION NOT NULL,
    rows INTEGER NOT NULL,
    columns INTEGER NOT NULL,
    border_style VARCHAR(50) NOT NULL DEFAULT 'solid',
    border_width DOUBLE PRECISION NOT NULL DEFAULT 1.0,
    border_color VARCHAR(50) NOT NULL DEFAULT '#000000',
    header_rows INTEGER NOT NULL DEFAULT 1,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS admin.table_columns (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    table_format_id UUID NOT NULL REFERENCES admin.table_formats(id) ON DELETE CASCADE,
    column_index INTEGER NOT NULL,
    width DOUBLE PRECISION NOT NULL,
    header_text VARCHAR(500),
    data_type VARCHAR(50) NOT NULL DEFAULT 'text',
    alignment VARCHAR(20) NOT NULL DEFAULT 'left',
    font_family VARCHAR(255),
    font_size DOUBLE PRECISION,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS admin.table_rows (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    table_format_id UUID NOT NULL REFERENCES admin.table_formats(id) ON DELETE CASCADE,
    row_index INTEGER NOT NULL,
    height DOUBLE PRECISION NOT NULL DEFAULT 20.0,
    is_header BOOLEAN NOT NULL DEFAULT FALSE,
    background_color VARCHAR(50),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS admin.watermarks (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    format_id UUID NOT NULL REFERENCES admin.document_formats(id) ON DELETE CASCADE,
    page_number INTEGER,
    text VARCHAR(500),
    image_path VARCHAR(1024),
    x DOUBLE PRECISION NOT NULL,
    y DOUBLE PRECISION NOT NULL,
    width DOUBLE PRECISION NOT NULL,
    height DOUBLE PRECISION NOT NULL,
    opacity DOUBLE PRECISION NOT NULL DEFAULT 0.3,
    rotation DOUBLE PRECISION NOT NULL DEFAULT 0.0,
    font_family VARCHAR(255),
    font_size DOUBLE PRECISION,
    font_color VARCHAR(50),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS admin.image_regions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    format_id UUID NOT NULL REFERENCES admin.document_formats(id) ON DELETE CASCADE,
    page_number INTEGER NOT NULL,
    x DOUBLE PRECISION NOT NULL,
    y DOUBLE PRECISION NOT NULL,
    width DOUBLE PRECISION NOT NULL,
    height DOUBLE PRECISION NOT NULL,
    image_path VARCHAR(1024),
    image_type VARCHAR(50) NOT NULL DEFAULT 'figure',
    is_static BOOLEAN NOT NULL DEFAULT TRUE,
    field_name VARCHAR(255),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS admin.template_fingerprints (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    format_id UUID NOT NULL UNIQUE REFERENCES admin.document_formats(id) ON DELETE CASCADE,
    page_dimensions JSONB,
    logo_coordinates JSONB,
    header_coordinates JSONB,
    footer_coordinates JSONB,
    table_coordinates JSONB,
    cell_coordinates JSONB,
    fingerprint_hash VARCHAR(256) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS admin.template_matches (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    document_id UUID NOT NULL REFERENCES admin.documents(id) ON DELETE CASCADE,
    format_id UUID NOT NULL REFERENCES admin.document_formats(id) ON DELETE CASCADE,
    confidence_score DOUBLE PRECISION NOT NULL,
    match_details JSONB,
    selected BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);