CREATE TABLE IF NOT EXISTS report_templates (
    id UUID PRIMARY KEY,
    name VARCHAR(255) UNIQUE NOT NULL,
    type VARCHAR(50) NOT NULL,
    description TEXT,
    template_structure JSONB NOT NULL,
    default_format VARCHAR(20) DEFAULT 'pdf',
    version INT DEFAULT 1,
    active BOOLEAN DEFAULT TRUE,
    created_by UUID REFERENCES users(id),
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ
);

CREATE TABLE IF NOT EXISTS reports (
    id UUID PRIMARY KEY,
    template_id UUID REFERENCES report_templates(id),
    name VARCHAR(255) NOT NULL,
    scope JSONB NOT NULL,
    status VARCHAR(50) NOT NULL,
    format VARCHAR(20),
    language VARCHAR(5),
    options JSONB,
    file_path VARCHAR(500),
    file_size BIGINT,
    file_hash VARCHAR(64),
    password_protected BOOLEAN DEFAULT FALSE,
    watermarked BOOLEAN DEFAULT FALSE,
    digitally_signed BOOLEAN DEFAULT FALSE,
    requested_by UUID REFERENCES users(id),
    generated_at TIMESTAMPTZ,
    expires_at TIMESTAMPTZ,
    error_message TEXT,
    policy_evaluation JSONB,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS report_attachments (
    id UUID PRIMARY KEY,
    report_id UUID REFERENCES reports(id) ON DELETE CASCADE,
    attachment_type VARCHAR(50),
    file_name VARCHAR(500),
    file_path VARCHAR(500),
    file_size BIGINT,
    order_index INT
);

CREATE TABLE IF NOT EXISTS report_schedules (
    id UUID PRIMARY KEY,
    template_id UUID REFERENCES report_templates(id),
    name VARCHAR(255) NOT NULL,
    scope JSONB NOT NULL,
    frequency VARCHAR(50) NOT NULL,
    cron_expression VARCHAR(100),
    time_window JSONB,
    distribution JSONB,
    format VARCHAR(20),
    language VARCHAR(5),
    options JSONB,
    next_run_at TIMESTAMPTZ,
    last_run_at TIMESTAMPTZ,
    last_report_id UUID REFERENCES reports(id),
    active BOOLEAN DEFAULT TRUE,
    retention_days INT DEFAULT 90,
    created_by UUID REFERENCES users(id),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS report_deliveries (
    id UUID PRIMARY KEY,
    report_id UUID REFERENCES reports(id),
    recipient_email VARCHAR(255) NOT NULL,
    recipient_name VARCHAR(255),
    delivery_channel VARCHAR(50),
    status VARCHAR(50),
    sent_at TIMESTAMPTZ,
    delivered_at TIMESTAMPTZ,
    read_at TIMESTAMPTZ,
    tracking_id VARCHAR(255),
    error_message TEXT
);

CREATE TABLE IF NOT EXISTS audit_logs (
    id UUID PRIMARY KEY,
    timestamp TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    request_id VARCHAR(100) NOT NULL,
    user_id UUID REFERENCES users(id),
    user_email VARCHAR(255),
    ip_address INET,
    user_agent TEXT,
    action VARCHAR(100) NOT NULL,
    resource_type VARCHAR(50),
    resource_id UUID,
    case_id UUID REFERENCES cases(id),
    review_set_id UUID REFERENCES review_sets(id),
    result VARCHAR(50) NOT NULL,
    reason TEXT,
    details JSONB,
    policy_refs TEXT[],
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS audit_batches (
    id UUID PRIMARY KEY,
    batch_timestamp TIMESTAMPTZ NOT NULL,
    log_count INT NOT NULL,
    merkle_root VARCHAR(64) NOT NULL,
    signed_hash VARCHAR(128) NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_reports_status_generated ON reports(status, generated_at DESC);
CREATE INDEX IF NOT EXISTS idx_schedules_next_run ON report_schedules(next_run_at, active);
CREATE INDEX IF NOT EXISTS idx_deliveries_report_status ON report_deliveries(report_id, status);
CREATE INDEX IF NOT EXISTS idx_audit_timestamp ON audit_logs(timestamp DESC);
CREATE INDEX IF NOT EXISTS idx_audit_request_id ON audit_logs(request_id);
CREATE INDEX IF NOT EXISTS idx_audit_user_action ON audit_logs(user_id, action, timestamp DESC);
CREATE INDEX IF NOT EXISTS idx_audit_resource ON audit_logs(resource_type, resource_id, timestamp DESC);
CREATE INDEX IF NOT EXISTS idx_audit_case ON audit_logs(case_id, timestamp DESC);

CREATE MATERIALIZED VIEW IF NOT EXISTS mv_report_stats_by_template AS
SELECT 
    template_id,
    COUNT(*) AS total_generated,
    COUNT(*) FILTER (WHERE status = 'completed') AS completed,
    COUNT(*) FILTER (WHERE status = 'failed') AS failed,
    AVG(EXTRACT(EPOCH FROM (generated_at - created_at))) AS avg_generation_time_seconds,
    MAX(generated_at) AS last_generated_at
FROM reports
GROUP BY template_id;

CREATE MATERIALIZED VIEW IF NOT EXISTS mv_audit_hotspots AS
SELECT 
    action,
    COUNT(*) AS action_count,
    COUNT(DISTINCT user_id) AS unique_users,
    COUNT(*) FILTER (WHERE result = 'denied') AS denied_count,
    DATE_TRUNC('day', timestamp) AS day
FROM audit_logs
WHERE timestamp > NOW() - INTERVAL '30 days'
GROUP BY action, DATE_TRUNC('day', timestamp);

