CREATE TABLE high_risk_config_change_requests (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    target_area text NOT NULL CHECK (target_area IN (
        'risk_limits',
        'routing_codes',
        'appsec',
        'cards_pci',
        'crypto_custody',
        'privacy',
        'rate_limit',
        'provider_config',
        'other'
    )),
    change_type text NOT NULL,
    target_type text NOT NULL DEFAULT '',
    target_id text NOT NULL DEFAULT '',
    status text NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'approved', 'rejected', 'canceled', 'implemented')),
    requester_admin_user_id uuid NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    reviewer_admin_user_id uuid REFERENCES users(id) ON DELETE RESTRICT,
    proposed_payload jsonb NOT NULL DEFAULT '{}'::jsonb,
    reason text NOT NULL,
    decision_note text NOT NULL DEFAULT '',
    evidence_reference text NOT NULL DEFAULT '',
    decided_at timestamptz,
    implemented_at timestamptz,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    CHECK (reviewer_admin_user_id IS NULL OR reviewer_admin_user_id <> requester_admin_user_id),
    CHECK (status NOT IN ('approved', 'implemented') OR (reviewer_admin_user_id IS NOT NULL AND decided_at IS NOT NULL))
);

CREATE INDEX high_risk_config_change_requests_status_idx
    ON high_risk_config_change_requests (status, created_at DESC);

CREATE INDEX high_risk_config_change_requests_target_idx
    ON high_risk_config_change_requests (target_area, target_type, target_id);

CREATE TRIGGER high_risk_config_change_requests_set_updated_at
BEFORE UPDATE ON high_risk_config_change_requests
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

CREATE TABLE backoffice_cases (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    category text NOT NULL CHECK (category IN (
        'reconciliation_break',
        'payment_review',
        'fraud_review',
        'provider_incident',
        'customer_complaint',
        'config_change',
        'security_event',
        'other'
    )),
    source_type text NOT NULL DEFAULT '',
    source_id text NOT NULL DEFAULT '',
    customer_user_id uuid REFERENCES users(id) ON DELETE SET NULL,
    title text NOT NULL,
    description text NOT NULL DEFAULT '',
    priority text NOT NULL DEFAULT 'medium' CHECK (priority IN ('low', 'medium', 'high', 'critical')),
    status text NOT NULL DEFAULT 'open' CHECK (status IN (
        'open',
        'assigned',
        'in_progress',
        'waiting_on_customer',
        'waiting_on_provider',
        'resolved',
        'closed',
        'canceled'
    )),
    owner_team text NOT NULL DEFAULT 'operations' CHECK (owner_team IN (
        'operations',
        'finance',
        'compliance',
        'risk',
        'support',
        'security',
        'engineering'
    )),
    assigned_to_admin_user_id uuid REFERENCES users(id) ON DELETE SET NULL,
    sla_due_at timestamptz,
    resolved_at timestamptz,
    closed_at timestamptz,
    created_by_admin_user_id uuid REFERENCES users(id) ON DELETE SET NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    CHECK (status <> 'resolved' OR resolved_at IS NOT NULL),
    CHECK (status <> 'closed' OR closed_at IS NOT NULL)
);

CREATE UNIQUE INDEX backoffice_cases_source_unique_idx
    ON backoffice_cases (category, source_type, source_id)
    WHERE source_type <> '' AND source_id <> '';

CREATE INDEX backoffice_cases_queue_idx
    ON backoffice_cases (status, priority, owner_team, sla_due_at);

CREATE INDEX backoffice_cases_assignee_idx
    ON backoffice_cases (assigned_to_admin_user_id, status, sla_due_at);

CREATE TRIGGER backoffice_cases_set_updated_at
BEFORE UPDATE ON backoffice_cases
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

CREATE TABLE backoffice_case_notes (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    case_id uuid NOT NULL REFERENCES backoffice_cases(id) ON DELETE CASCADE,
    admin_user_id uuid REFERENCES users(id) ON DELETE SET NULL,
    note_type text NOT NULL DEFAULT 'internal' CHECK (note_type IN ('internal', 'customer_contact', 'provider_update', 'decision', 'system')),
    body text NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX backoffice_case_notes_case_idx
    ON backoffice_case_notes (case_id, created_at DESC);

CREATE TABLE backoffice_case_attachments (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    case_id uuid NOT NULL REFERENCES backoffice_cases(id) ON DELETE CASCADE,
    admin_user_id uuid REFERENCES users(id) ON DELETE SET NULL,
    file_name text NOT NULL,
    content_type text NOT NULL DEFAULT 'application/octet-stream',
    storage_reference text NOT NULL,
    checksum_sha256 text NOT NULL DEFAULT '',
    classification text NOT NULL DEFAULT 'confidential' CHECK (classification IN ('internal', 'confidential', 'restricted')),
    created_at timestamptz NOT NULL DEFAULT now(),
    CHECK (storage_reference <> '')
);

CREATE INDEX backoffice_case_attachments_case_idx
    ON backoffice_case_attachments (case_id, created_at DESC);

CREATE TABLE backoffice_case_history (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    case_id uuid NOT NULL REFERENCES backoffice_cases(id) ON DELETE CASCADE,
    actor_admin_user_id uuid REFERENCES users(id) ON DELETE SET NULL,
    event_type text NOT NULL CHECK (event_type IN (
        'created',
        'assigned',
        'status_changed',
        'priority_changed',
        'sla_changed',
        'note_added',
        'attachment_added',
        'linked',
        'closed',
        'reopened',
        'system_sync'
    )),
    from_status text NOT NULL DEFAULT '',
    to_status text NOT NULL DEFAULT '',
    from_assignee_admin_user_id uuid REFERENCES users(id) ON DELETE SET NULL,
    to_assignee_admin_user_id uuid REFERENCES users(id) ON DELETE SET NULL,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX backoffice_case_history_case_idx
    ON backoffice_case_history (case_id, created_at DESC);

CREATE TABLE support_impersonation_sessions (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    admin_user_id uuid NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    customer_user_id uuid NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    status text NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'ended', 'expired', 'canceled')),
    reason text NOT NULL,
    ticket_reference text NOT NULL DEFAULT '',
    allowed_actions text[] NOT NULL DEFAULT ARRAY['view_profile', 'view_accounts', 'view_cases']::text[],
    started_at timestamptz NOT NULL DEFAULT now(),
    expires_at timestamptz NOT NULL,
    ended_at timestamptz,
    remote_ip text NOT NULL DEFAULT '',
    user_agent text NOT NULL DEFAULT '',
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    CHECK (admin_user_id <> customer_user_id),
    CHECK (expires_at > started_at),
    CHECK (status = 'active' OR ended_at IS NOT NULL)
);

CREATE INDEX support_impersonation_sessions_admin_status_idx
    ON support_impersonation_sessions (admin_user_id, status, expires_at DESC);

CREATE INDEX support_impersonation_sessions_customer_idx
    ON support_impersonation_sessions (customer_user_id, created_at DESC);

CREATE TRIGGER support_impersonation_sessions_set_updated_at
BEFORE UPDATE ON support_impersonation_sessions
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

CREATE TABLE backoffice_dashboard_widgets (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    role_name text NOT NULL CHECK (role_name IN ('compliance', 'finance', 'support', 'operations')),
    widget_key text NOT NULL,
    enabled boolean NOT NULL DEFAULT true,
    sort_order integer NOT NULL DEFAULT 100,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    UNIQUE (role_name, widget_key)
);

CREATE TRIGGER backoffice_dashboard_widgets_set_updated_at
BEFORE UPDATE ON backoffice_dashboard_widgets
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

INSERT INTO backoffice_dashboard_widgets (role_name, widget_key, sort_order) VALUES
    ('compliance', 'fraud_review_queue', 10),
    ('compliance', 'customer_complaints', 20),
    ('compliance', 'security_events', 30),
    ('finance', 'reconciliation_breaks', 10),
    ('finance', 'payment_reviews', 20),
    ('finance', 'audit_evidence', 30),
    ('support', 'customer_complaints', 10),
    ('support', 'support_impersonation', 20),
    ('support', 'case_sla', 30),
    ('operations', 'provider_incidents', 10),
    ('operations', 'payment_reviews', 20),
    ('operations', 'reconciliation_breaks', 30)
ON CONFLICT DO NOTHING;

INSERT INTO backoffice_cases (
    category, source_type, source_id, title, description, priority, status, owner_team, assigned_to_admin_user_id, sla_due_at
)
SELECT
    'reconciliation_break',
    'reconciliation_break',
    rb.id::text,
    'Reconciliation break: ' || rb.break_type,
    rb.description,
    rb.severity,
    CASE rb.status
        WHEN 'resolved' THEN 'resolved'
        WHEN 'ignored' THEN 'closed'
        WHEN 'investigating' THEN 'in_progress'
        ELSE 'open'
    END,
    'finance',
    rb.owner_user_id,
    rb.created_at + interval '1 day'
FROM reconciliation_breaks rb
WHERE rb.status IN ('open', 'investigating')
ON CONFLICT DO NOTHING;

INSERT INTO backoffice_cases (
    category, source_type, source_id, customer_user_id, title, description, priority, status, owner_team, assigned_to_admin_user_id, sla_due_at
)
SELECT
    'payment_review',
    'payment_review_case',
    prc.id::text,
    t.user_id,
    'Manual payment review',
    prc.reason,
    CASE WHEN prc.reason_code LIKE '%SANCTIONS%' THEN 'critical' ELSE 'high' END,
    CASE prc.status WHEN 'open' THEN 'open' ELSE 'closed' END,
    'operations',
    prc.assigned_admin_user_id,
    prc.opened_at + interval '4 hours'
FROM payment_review_cases prc
JOIN transfers t ON t.id = prc.transfer_id
WHERE prc.status = 'open'
ON CONFLICT DO NOTHING;

INSERT INTO backoffice_cases (
    category, source_type, source_id, customer_user_id, title, description, priority, status, owner_team, sla_due_at
)
SELECT
    'fraud_review',
    'risk_event',
    re.id::text,
    re.user_id,
    'Risk event: ' || re.operation,
    re.reason,
    re.severity,
    'open',
    'risk',
    re.created_at + interval '8 hours'
FROM risk_events re
WHERE re.decision IN ('review', 'block')
ON CONFLICT DO NOTHING;

INSERT INTO backoffice_cases (
    category, source_type, source_id, title, description, priority, status, owner_team, sla_due_at
)
SELECT
    'provider_incident',
    'sepa_provider_report',
    spr.id::text,
    'Provider report failed: ' || spr.provider,
    spr.report_reference,
    'high',
    'open',
    'operations',
    spr.created_at + interval '4 hours'
FROM sepa_provider_reports spr
WHERE spr.status = 'failed'
ON CONFLICT DO NOTHING;

INSERT INTO backoffice_cases (
    category, source_type, source_id, customer_user_id, title, description, priority, status, owner_team, sla_due_at
)
SELECT
    'security_event',
    'security_event',
    se.id::text,
    se.user_id,
    'Security event: ' || se.event_type,
    se.identifier,
    se.severity,
    CASE se.status WHEN 'resolved' THEN 'resolved' WHEN 'ignored' THEN 'closed' WHEN 'reviewing' THEN 'in_progress' ELSE 'open' END,
    'security',
    se.created_at + interval '8 hours'
FROM security_events se
WHERE se.status IN ('open', 'reviewing')
ON CONFLICT DO NOTHING;

INSERT INTO privacy_data_inventory (
    table_name, field_name, data_category, classification, lawful_basis, retention_policy, residency_scope, encrypted_at_rest, owner, notes
) VALUES
    ('high_risk_config_change_requests', '*', 'audit', 'confidential', 'legal_obligation/risk_management', 'audit_logs_default', 'default', false, 'operations', 'Maker-checker records for high-risk admin configuration changes'),
    ('backoffice_cases', '*', 'operational', 'confidential', 'contract/risk_management', 'operational_records_default', 'default', false, 'operations', 'Backoffice operational queue and SLA records'),
    ('backoffice_case_notes', '*', 'operational', 'confidential', 'contract/risk_management', 'operational_records_default', 'default', false, 'operations', 'Admin notes for operational cases; do not store raw secrets'),
    ('backoffice_case_attachments', '*', 'operational', 'restricted', 'contract/risk_management', 'operational_records_default', 'default', true, 'operations', 'Attachment metadata and storage references only'),
    ('support_impersonation_sessions', '*', 'audit', 'restricted', 'contract/security', 'audit_logs_default', 'default', false, 'support', 'Support view sessions; no customer secrets or bypass tokens')
ON CONFLICT (system_name, table_name, field_name) DO NOTHING;