CREATE TABLE kyc_provider_decisions (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id uuid NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    kyc_profile_id uuid REFERENCES kyc_profiles(id) ON DELETE SET NULL,
    provider text NOT NULL,
    external_verification_id text NOT NULL,
    provider_event_id text NOT NULL DEFAULT '',
    decision text NOT NULL CHECK (decision IN ('pending', 'verified', 'rejected', 'manual_review')),
    reason text NOT NULL DEFAULT '',
    confidence_score integer CHECK (confidence_score BETWEEN 0 AND 100),
    raw_payload jsonb NOT NULL DEFAULT '{}'::jsonb,
    decided_at timestamptz NOT NULL DEFAULT now(),
    received_at timestamptz NOT NULL DEFAULT now(),
    UNIQUE (provider, external_verification_id, provider_event_id, decision)
);

CREATE INDEX kyc_provider_decisions_profile_created_idx
    ON kyc_provider_decisions (kyc_profile_id, received_at DESC);

CREATE TABLE kyc_evidence (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id uuid NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    kyc_profile_id uuid NOT NULL REFERENCES kyc_profiles(id) ON DELETE RESTRICT,
    kyc_document_id uuid REFERENCES kyc_documents(id) ON DELETE SET NULL,
    provider text NOT NULL,
    evidence_type text NOT NULL CHECK (evidence_type IN ('document', 'liveness', 'address', 'provider_report', 'manual_upload')),
    external_evidence_id text NOT NULL DEFAULT '',
    evidence_hash text NOT NULL DEFAULT '',
    status text NOT NULL DEFAULT 'submitted' CHECK (status IN ('uploaded', 'submitted', 'verified', 'rejected', 'expired')),
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    collected_at timestamptz NOT NULL DEFAULT now(),
    created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX kyc_evidence_profile_created_idx
    ON kyc_evidence (kyc_profile_id, created_at DESC);

ALTER TABLE aml_cases
    ADD COLUMN IF NOT EXISTS assigned_to_admin_user_id uuid REFERENCES users(id) ON DELETE SET NULL,
    ADD COLUMN IF NOT EXISTS sla_due_at timestamptz,
    ADD COLUMN IF NOT EXISTS escalated_at timestamptz,
    ADD COLUMN IF NOT EXISTS closed_at timestamptz,
    ADD COLUMN IF NOT EXISTS regulatory_report_required boolean NOT NULL DEFAULT false,
    ADD COLUMN IF NOT EXISTS false_positive boolean NOT NULL DEFAULT false;

CREATE INDEX IF NOT EXISTS aml_cases_assigned_status_idx
    ON aml_cases (assigned_to_admin_user_id, status);

CREATE INDEX IF NOT EXISTS aml_cases_sla_due_idx
    ON aml_cases (sla_due_at)
    WHERE status IN ('open', 'reviewing', 'escalated');

CREATE TABLE aml_case_notes (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    aml_case_id uuid NOT NULL REFERENCES aml_cases(id) ON DELETE CASCADE,
    author_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_contact', 'regulatory', 'decision')),
    body text NOT NULL CHECK (length(body) BETWEEN 1 AND 4000),
    created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX aml_case_notes_case_created_idx
    ON aml_case_notes (aml_case_id, created_at DESC);

CREATE TABLE aml_case_attachments (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    aml_case_id uuid NOT NULL REFERENCES aml_cases(id) ON DELETE CASCADE,
    uploaded_by_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_uri text NOT NULL DEFAULT '',
    sha256_hash text NOT NULL DEFAULT '',
    description text NOT NULL DEFAULT '',
    created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX aml_case_attachments_case_created_idx
    ON aml_case_attachments (aml_case_id, created_at DESC);

CREATE TABLE aml_case_status_history (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    aml_case_id uuid NOT NULL REFERENCES aml_cases(id) ON DELETE CASCADE,
    actor_admin_user_id uuid REFERENCES users(id) ON DELETE SET NULL,
    from_status text NOT NULL DEFAULT '',
    to_status text NOT NULL,
    note text NOT NULL DEFAULT '',
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX aml_case_status_history_case_created_idx
    ON aml_case_status_history (aml_case_id, created_at DESC);

CREATE TABLE customer_risk_profiles (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id uuid NOT NULL UNIQUE REFERENCES users(id) ON DELETE RESTRICT,
    risk_score integer NOT NULL CHECK (risk_score BETWEEN 0 AND 100),
    risk_tier text NOT NULL CHECK (risk_tier IN ('low', 'standard', 'high', 'prohibited')),
    factors jsonb NOT NULL DEFAULT '{}'::jsonb,
    status text NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'archived')),
    last_review_at timestamptz NOT NULL DEFAULT now(),
    next_review_at timestamptz NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE TRIGGER customer_risk_profiles_set_updated_at
BEFORE UPDATE ON customer_risk_profiles
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

CREATE INDEX customer_risk_profiles_tier_next_review_idx
    ON customer_risk_profiles (risk_tier, next_review_at);

CREATE TABLE enhanced_due_diligence_reviews (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id uuid NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    aml_case_id uuid REFERENCES aml_cases(id) ON DELETE SET NULL,
    status text NOT NULL DEFAULT 'requested' CHECK (status IN ('requested', 'in_progress', 'approved', 'rejected', 'closed')),
    reason text NOT NULL,
    required_evidence jsonb NOT NULL DEFAULT '[]'::jsonb,
    decision_note text NOT NULL DEFAULT '',
    requested_by_admin_user_id uuid REFERENCES users(id) ON DELETE SET NULL,
    decided_by_admin_user_id uuid REFERENCES users(id) ON DELETE SET NULL,
    due_at timestamptz,
    decided_at timestamptz,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE TRIGGER enhanced_due_diligence_reviews_set_updated_at
BEFORE UPDATE ON enhanced_due_diligence_reviews
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

CREATE INDEX enhanced_due_diligence_user_status_idx
    ON enhanced_due_diligence_reviews (user_id, status);

CREATE INDEX enhanced_due_diligence_status_due_idx
    ON enhanced_due_diligence_reviews (status, due_at);

CREATE TABLE compliance_monitoring_runs (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    run_type text NOT NULL CHECK (run_type IN ('user', 'batch')),
    provider text NOT NULL,
    status text NOT NULL DEFAULT 'running' CHECK (status IN ('running', 'completed', 'failed')),
    requested_by_admin_user_id uuid REFERENCES users(id) ON DELETE SET NULL,
    user_id uuid REFERENCES users(id) ON DELETE SET NULL,
    total_count integer NOT NULL DEFAULT 0 CHECK (total_count >= 0),
    screened_count integer NOT NULL DEFAULT 0 CHECK (screened_count >= 0),
    review_count integer NOT NULL DEFAULT 0 CHECK (review_count >= 0),
    hit_count integer NOT NULL DEFAULT 0 CHECK (hit_count >= 0),
    error_count integer NOT NULL DEFAULT 0 CHECK (error_count >= 0),
    started_at timestamptz NOT NULL DEFAULT now(),
    finished_at timestamptz,
    created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX compliance_monitoring_runs_created_idx
    ON compliance_monitoring_runs (created_at DESC);

CREATE TABLE regulatory_reports (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    aml_case_id uuid NOT NULL REFERENCES aml_cases(id) ON DELETE RESTRICT,
    user_id uuid NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    report_type text NOT NULL CHECK (report_type IN ('sar', 'str', 'ctr', 'other')),
    status text NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'in_review', 'submitted', 'rejected', 'withdrawn')),
    jurisdiction text NOT NULL DEFAULT '',
    filing_reference text NOT NULL DEFAULT '',
    narrative text NOT NULL DEFAULT '',
    created_by_admin_user_id uuid REFERENCES users(id) ON DELETE SET NULL,
    submitted_by_admin_user_id uuid REFERENCES users(id) ON DELETE SET NULL,
    submitted_at timestamptz,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE TRIGGER regulatory_reports_set_updated_at
BEFORE UPDATE ON regulatory_reports
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

CREATE INDEX regulatory_reports_status_created_idx
    ON regulatory_reports (status, created_at DESC);

CREATE TABLE compliance_tuning_rules (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    signal text NOT NULL UNIQUE,
    description text NOT NULL DEFAULT '',
    weight integer NOT NULL CHECK (weight BETWEEN -100 AND 100),
    review_threshold integer NOT NULL DEFAULT 70 CHECK (review_threshold BETWEEN 0 AND 100),
    block_threshold integer NOT NULL DEFAULT 90 CHECK (block_threshold BETWEEN 0 AND 100),
    enabled boolean NOT NULL DEFAULT true,
    false_positive_count integer NOT NULL DEFAULT 0 CHECK (false_positive_count >= 0),
    true_positive_count integer NOT NULL DEFAULT 0 CHECK (true_positive_count >= 0),
    updated_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()
);

CREATE TRIGGER compliance_tuning_rules_set_updated_at
BEFORE UPDATE ON compliance_tuning_rules
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

CREATE TABLE aml_false_positive_feedback (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    aml_case_id uuid NOT NULL REFERENCES aml_cases(id) ON DELETE CASCADE,
    aml_screening_id uuid REFERENCES aml_screenings(id) ON DELETE SET NULL,
    tuning_rule_id uuid REFERENCES compliance_tuning_rules(id) ON DELETE SET NULL,
    reviewed_by_admin_user_id uuid REFERENCES users(id) ON DELETE SET NULL,
    outcome text NOT NULL CHECK (outcome IN ('confirmed_false_positive', 'true_positive', 'inconclusive')),
    reason text NOT NULL DEFAULT '',
    created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX aml_false_positive_feedback_case_idx
    ON aml_false_positive_feedback (aml_case_id, created_at DESC);

INSERT INTO compliance_tuning_rules (signal, description, weight, review_threshold, block_threshold)
VALUES
    ('sanctions_hit', 'Sanctions match returned by provider', 90, 70, 90),
    ('pep_match', 'Politically exposed person match returned by provider', 55, 70, 90),
    ('adverse_media', 'Adverse media or negative news signal returned by provider', 45, 70, 90),
    ('high_risk_country', 'Customer country requires enhanced review', 35, 70, 90),
    ('velocity_block', 'Payment/card/FX risk engine produced a block decision', 25, 70, 90)
ON CONFLICT (signal) DO NOTHING;