

-- Extend users table for access control metadata
ALTER TABLE users ADD COLUMN IF NOT EXISTS require_2fa BOOLEAN DEFAULT FALSE;
ALTER TABLE users ADD COLUMN IF NOT EXISTS scopes JSONB DEFAULT '{}'::jsonb;
ALTER TABLE users ADD COLUMN IF NOT EXISTS status TEXT DEFAULT 'active';
ALTER TABLE users ADD COLUMN IF NOT EXISTS last_login TIMESTAMPTZ;

-- Integrations registry
CREATE TABLE IF NOT EXISTS integrations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    provider TEXT NOT NULL,
    name TEXT,
    config JSONB DEFAULT '{}'::jsonb,
    scopes TEXT[] DEFAULT ARRAY[]::TEXT[],
    schedule JSONB,
    status TEXT DEFAULT 'inactive',
    is_active BOOLEAN DEFAULT TRUE,
    last_health_check TIMESTAMPTZ,
    created_by UUID REFERENCES users(id),
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_integrations_provider ON integrations(provider);
CREATE INDEX IF NOT EXISTS idx_integrations_status ON integrations(status);

CREATE TABLE IF NOT EXISTS integration_health (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    integration_id UUID REFERENCES integrations(id) ON DELETE CASCADE,
    timestamp TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    status TEXT NOT NULL,
    metrics JSONB,
    error TEXT
);

CREATE INDEX IF NOT EXISTS idx_integration_health_integration ON integration_health(integration_id, timestamp DESC);

-- OCR settings (single row)
CREATE TABLE IF NOT EXISTS ocr_settings (
    id SMALLINT PRIMARY KEY DEFAULT 1,
    enabled BOOLEAN DEFAULT FALSE,
    dpi INT DEFAULT 300,
    languages TEXT[] DEFAULT ARRAY['eng'],
    page_limit INT DEFAULT 50,
    file_size_limit_mb INT DEFAULT 50,
    concurrency INT DEFAULT 2,
    pipeline_priority TEXT DEFAULT 'normal',
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

INSERT INTO ocr_settings (id) VALUES (1) ON CONFLICT (id) DO NOTHING;

-- Role based access control metadata
CREATE TABLE IF NOT EXISTS roles (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name TEXT UNIQUE NOT NULL,
    description TEXT,
    is_system BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS permissions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    resource TEXT NOT NULL,
    action TEXT NOT NULL,
    description TEXT,
    CONSTRAINT uq_permission UNIQUE (resource, action)
);

CREATE TABLE IF NOT EXISTS role_permissions (
    role_id UUID REFERENCES roles(id) ON DELETE CASCADE,
    permission_id UUID REFERENCES permissions(id) ON DELETE CASCADE,
    PRIMARY KEY (role_id, permission_id)
);

CREATE TABLE IF NOT EXISTS user_roles (
    user_id UUID REFERENCES users(id) ON DELETE CASCADE,
    role_id UUID REFERENCES roles(id) ON DELETE CASCADE,
    assigned_at TIMESTAMPTZ DEFAULT NOW(),
    assigned_by UUID REFERENCES users(id),
    PRIMARY KEY (user_id, role_id)
);

-- Access logs dedicated view (separate from audit log for quick access checks)
CREATE TABLE IF NOT EXISTS access_logs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    timestamp TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    user_id UUID,
    ip_address TEXT,
    user_agent TEXT,
    action TEXT NOT NULL,
    result TEXT NOT NULL,
    reason TEXT,
    details JSONB
);

CREATE INDEX IF NOT EXISTS idx_access_logs_user ON access_logs(user_id, timestamp DESC);
CREATE INDEX IF NOT EXISTS idx_access_logs_action ON access_logs(action, timestamp DESC);

-- Alerts & notifications
CREATE TABLE IF NOT EXISTS alert_types (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name TEXT NOT NULL,
    category TEXT,
    severity TEXT DEFAULT 'medium',
    description TEXT,
    enabled BOOLEAN DEFAULT TRUE,
    trigger_conditions JSONB DEFAULT '{}'::jsonb,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS alert_channels (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name TEXT NOT NULL,
    type TEXT NOT NULL,
    config JSONB DEFAULT '{}'::jsonb,
    active BOOLEAN DEFAULT TRUE,
    last_tested TIMESTAMPTZ,
    test_result JSONB,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS alert_recipients (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    alert_type_id UUID REFERENCES alert_types(id) ON DELETE CASCADE,
    channel_id UUID REFERENCES alert_channels(id) ON DELETE CASCADE,
    recipient TEXT NOT NULL,
    priority TEXT DEFAULT 'medium',
    digest_enabled BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_alert_recipients_type ON alert_recipients(alert_type_id);

CREATE TABLE IF NOT EXISTS alert_history (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    alert_type UUID REFERENCES alert_types(id) ON DELETE SET NULL,
    triggered_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    recipients JSONB,
    delivered_count INT DEFAULT 0,
    failed_count INT DEFAULT 0,
    details JSONB
);

CREATE INDEX IF NOT EXISTS idx_alert_history_type_time ON alert_history(alert_type, triggered_at DESC);

-- Policy controls
CREATE TABLE IF NOT EXISTS policies_export_gate (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    rule_name TEXT NOT NULL,
    conditions JSONB DEFAULT '{}'::jsonb,
    action TEXT NOT NULL,
    requires_watermark BOOLEAN DEFAULT FALSE,
    allowed_recipients TEXT,
    reason_template TEXT,
    priority INT DEFAULT 50,
    active BOOLEAN DEFAULT TRUE,
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS policies_retention (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    category TEXT NOT NULL,
    source_types TEXT[] DEFAULT ARRAY[]::TEXT[],
    retention_days INT,
    legal_hold_exception BOOLEAN DEFAULT TRUE,
    auto_dispose BOOLEAN DEFAULT FALSE,
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS policies_legal_hold (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    rule_name TEXT NOT NULL,
    auto_apply_conditions JSONB DEFAULT '{}'::jsonb,
    notification_template TEXT,
    escalation_days INT,
    active BOOLEAN DEFAULT TRUE,
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- System level configuration
CREATE TABLE IF NOT EXISTS system_storage (
    id SMALLINT PRIMARY KEY DEFAULT 1,
    provider TEXT,
    bucket_name TEXT,
    region TEXT,
    signed_url_ttl_hours INT DEFAULT 24,
    encryption_enabled BOOLEAN DEFAULT TRUE,
    redundancy TEXT,
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

INSERT INTO system_storage (id) VALUES (1) ON CONFLICT (id) DO NOTHING;

CREATE TABLE IF NOT EXISTS system_indexing (
    id SMALLINT PRIMARY KEY DEFAULT 1,
    default_analyzer TEXT,
    languages TEXT[] DEFAULT ARRAY['english'],
    searchable_fields TEXT[] DEFAULT ARRAY['content'],
    reindex_schedule TEXT,
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

INSERT INTO system_indexing (id) VALUES (1) ON CONFLICT (id) DO NOTHING;

CREATE TABLE IF NOT EXISTS system_messaging (
    id SMALLINT PRIMARY KEY DEFAULT 1,
    smtp_config JSONB DEFAULT '{}'::jsonb,
    teams_webhook_url TEXT,
    notification_from_address TEXT,
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

INSERT INTO system_messaging (id) VALUES (1) ON CONFLICT (id) DO NOTHING;

CREATE TABLE IF NOT EXISTS system_branding (
    id SMALLINT PRIMARY KEY DEFAULT 1,
    logo_url TEXT,
    primary_color TEXT,
    secondary_color TEXT,
    company_name TEXT,
    footer_text TEXT,
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

INSERT INTO system_branding (id) VALUES (1) ON CONFLICT (id) DO NOTHING;

-- Convenience views to align with existing classification tables
CREATE OR REPLACE VIEW ai_models AS
SELECT
    id,
    task,
    name,
    version,
    path_reference,
    active,
    params,
    performance_metrics,
    supported_languages,
    features,
    fallback_order,
    base_threshold,
    updated_at
FROM ml_models;

CREATE OR REPLACE VIEW detection_rules AS
SELECT
    id,
    name,
    type,
    pattern,
    detection_type,
    sensitivity,
    confidence,
    priority,
    active,
    created_by AS owner,
    created_at,
    updated_at
FROM classification_rules;


