CREATE TABLE organizations (
    id uuid PRIMARY KEY,
    slug text NOT NULL UNIQUE,
    name text NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE users (
    id uuid PRIMARY KEY,
    identity_issuer text NOT NULL,
    identity_subject text NOT NULL,
    display_name text NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    CHECK (length(identity_issuer) > 0),
    CHECK (length(identity_subject) > 0),
    UNIQUE (identity_issuer, identity_subject)
);

CREATE TABLE organization_memberships (
    organization_id uuid NOT NULL REFERENCES organizations (id) ON DELETE CASCADE,
    user_id uuid NOT NULL REFERENCES users (id) ON DELETE RESTRICT,
    role text NOT NULL CHECK (role IN ('owner', 'administrator', 'member', 'viewer')),
    created_at timestamptz NOT NULL DEFAULT now(),
    removed_at timestamptz,
    PRIMARY KEY (organization_id, user_id)
);

CREATE INDEX organization_memberships_user_idx
    ON organization_memberships (user_id);

CREATE TABLE projects (
    organization_id uuid NOT NULL REFERENCES organizations (id) ON DELETE CASCADE,
    id uuid NOT NULL,
    slug text NOT NULL,
    name text NOT NULL,
    remediation_mode text NOT NULL DEFAULT 'report_before_change'
        CHECK (remediation_mode IN ('report_before_change', 'auto_change_then_report')),
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (organization_id, id),
    UNIQUE (organization_id, slug)
);

CREATE TABLE ingestion_sources (
    organization_id uuid NOT NULL,
    id uuid NOT NULL,
    project_id uuid NOT NULL,
    name text NOT NULL,
    credential_reference text NOT NULL,
    status text NOT NULL DEFAULT 'active'
        CHECK (status IN ('active', 'disabled')),
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (organization_id, id),
    CHECK (length(credential_reference) > 0),
    UNIQUE (organization_id, id, project_id),
    UNIQUE (organization_id, project_id, name),
    UNIQUE (organization_id, credential_reference),
    FOREIGN KEY (organization_id, project_id)
        REFERENCES projects (organization_id, id) ON DELETE CASCADE
);

CREATE INDEX ingestion_sources_project_idx
    ON ingestion_sources (organization_id, project_id);

CREATE TABLE repositories (
    organization_id uuid NOT NULL,
    id uuid NOT NULL,
    project_id uuid NOT NULL,
    provider text NOT NULL,
    clone_url text NOT NULL,
    default_branch text NOT NULL,
    credential_reference text,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (organization_id, id),
    UNIQUE (organization_id, id, project_id),
    FOREIGN KEY (organization_id, project_id)
        REFERENCES projects (organization_id, id) ON DELETE CASCADE
);

CREATE INDEX repositories_project_idx
    ON repositories (organization_id, project_id);

CREATE TABLE runtime_environments (
    organization_id uuid NOT NULL,
    id uuid NOT NULL,
    project_id uuid NOT NULL,
    name text NOT NULL,
    connection_type text NOT NULL CHECK (connection_type IN ('ssh', 'collector')),
    endpoint text,
    credential_reference text,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (organization_id, id),
    UNIQUE (organization_id, id, project_id),
    UNIQUE (organization_id, project_id, name),
    FOREIGN KEY (organization_id, project_id)
        REFERENCES projects (organization_id, id) ON DELETE CASCADE
);

CREATE INDEX runtime_environments_project_idx
    ON runtime_environments (organization_id, project_id);

CREATE TABLE analysis_target_rules (
    organization_id uuid NOT NULL,
    id uuid NOT NULL,
    project_id uuid NOT NULL,
    ingestion_source_id uuid,
    priority integer NOT NULL CHECK (priority >= 0),
    source_system text,
    source_service text,
    source_environment text,
    source_component text,
    repository_id uuid NOT NULL,
    enabled boolean NOT NULL DEFAULT true,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (organization_id, id),
    UNIQUE (organization_id, id, project_id),
    UNIQUE (organization_id, project_id, priority),
    FOREIGN KEY (organization_id, ingestion_source_id, project_id)
        REFERENCES ingestion_sources (organization_id, id, project_id)
        ON DELETE CASCADE,
    FOREIGN KEY (organization_id, repository_id, project_id)
        REFERENCES repositories (organization_id, id, project_id)
        ON DELETE RESTRICT
);

CREATE INDEX analysis_target_rules_match_idx
    ON analysis_target_rules (
        organization_id,
        project_id,
        enabled,
        priority
    );

CREATE TABLE analysis_target_rule_runtime_environments (
    organization_id uuid NOT NULL,
    analysis_target_rule_id uuid NOT NULL,
    project_id uuid NOT NULL,
    runtime_environment_id uuid NOT NULL,
    PRIMARY KEY (
        organization_id,
        analysis_target_rule_id,
        runtime_environment_id
    ),
    FOREIGN KEY (organization_id, analysis_target_rule_id, project_id)
        REFERENCES analysis_target_rules (organization_id, id, project_id)
        ON DELETE CASCADE,
    FOREIGN KEY (organization_id, runtime_environment_id, project_id)
        REFERENCES runtime_environments (organization_id, id, project_id)
        ON DELETE RESTRICT
);

CREATE INDEX analysis_target_rule_runtime_environment_idx
    ON analysis_target_rule_runtime_environments (
        organization_id,
        runtime_environment_id,
        project_id
    );

CREATE TABLE error_groups (
    organization_id uuid NOT NULL,
    id uuid NOT NULL,
    project_id uuid NOT NULL,
    fingerprint text NOT NULL,
    error_type text NOT NULL,
    latest_message text NOT NULL,
    occurrence_count bigint NOT NULL DEFAULT 0 CHECK (occurrence_count >= 0),
    first_seen_at timestamptz NOT NULL,
    last_seen_at timestamptz NOT NULL,
    status text NOT NULL DEFAULT 'open'
        CHECK (status IN ('open', 'analyzing', 'actionable', 'resolved', 'ignored')),
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (organization_id, id),
    CHECK (first_seen_at <= last_seen_at),
    UNIQUE (organization_id, id, project_id),
    UNIQUE (organization_id, project_id, fingerprint),
    FOREIGN KEY (organization_id, project_id)
        REFERENCES projects (organization_id, id) ON DELETE CASCADE
);

CREATE INDEX error_groups_project_status_idx
    ON error_groups (organization_id, project_id, status, last_seen_at DESC);

CREATE TABLE error_occurrences (
    organization_id uuid NOT NULL,
    id uuid NOT NULL,
    project_id uuid NOT NULL,
    ingestion_source_id uuid NOT NULL,
    error_group_id uuid NOT NULL,
    source_event_id text NOT NULL,
    occurred_at timestamptz NOT NULL,
    payload jsonb NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (organization_id, id),
    CHECK (length(source_event_id) > 0),
    UNIQUE (organization_id, ingestion_source_id, source_event_id),
    FOREIGN KEY (organization_id, ingestion_source_id, project_id)
        REFERENCES ingestion_sources (organization_id, id, project_id)
        ON DELETE RESTRICT,
    FOREIGN KEY (organization_id, error_group_id, project_id)
        REFERENCES error_groups (organization_id, id, project_id) ON DELETE CASCADE
);

CREATE INDEX error_occurrences_group_idx
    ON error_occurrences (organization_id, error_group_id, occurred_at DESC);

CREATE INDEX error_occurrences_ingestion_source_idx
    ON error_occurrences (
        organization_id,
        ingestion_source_id,
        occurred_at DESC
    );

CREATE TABLE remediation_runs (
    organization_id uuid NOT NULL,
    id uuid NOT NULL,
    project_id uuid NOT NULL,
    error_group_id uuid NOT NULL,
    repository_id uuid NOT NULL,
    base_revision text NOT NULL,
    mode text NOT NULL CHECK (mode IN ('report_before_change', 'auto_change_then_report')),
    status text NOT NULL
        CHECK (status IN ('queued', 'analyzing', 'awaiting_approval', 'changing', 'validating', 'completed', 'failed', 'cancelled')),
    fixability text NOT NULL DEFAULT 'undetermined'
        CHECK (fixability IN ('fixable', 'not_fixable', 'undetermined')),
    proposal_digest text,
    report jsonb,
    failure jsonb,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    completed_at timestamptz,
    PRIMARY KEY (organization_id, id),
    UNIQUE (organization_id, id, project_id),
    UNIQUE (organization_id, id, project_id, repository_id),
    UNIQUE (organization_id, id, project_id, proposal_digest),
    CHECK (proposal_digest IS NULL OR length(proposal_digest) > 0),
    CHECK (
        failure IS NULL OR
        failure ->> 'schema_version' = 'ariadne.execution-failure.v1'
    ),
    CHECK (
        (status IN ('completed', 'failed', 'cancelled')) =
        (completed_at IS NOT NULL)
    ),
    FOREIGN KEY (organization_id, error_group_id, project_id)
        REFERENCES error_groups (organization_id, id, project_id) ON DELETE CASCADE,
    FOREIGN KEY (organization_id, repository_id, project_id)
        REFERENCES repositories (organization_id, id, project_id) ON DELETE RESTRICT
);

CREATE INDEX remediation_runs_error_group_idx
    ON remediation_runs (organization_id, error_group_id, created_at DESC);

CREATE INDEX remediation_runs_repository_idx
    ON remediation_runs (organization_id, repository_id, project_id);

CREATE TABLE remediation_executions (
    organization_id uuid NOT NULL,
    id uuid NOT NULL,
    project_id uuid NOT NULL,
    remediation_run_id uuid NOT NULL,
    intent text NOT NULL CHECK (intent IN ('analyze', 'apply_change')),
    attempt integer NOT NULL CHECK (attempt > 0),
    requested_proposal_digest text,
    status text NOT NULL
        CHECK (status IN ('queued', 'running', 'completed', 'failed', 'cancelled')),
    failure jsonb,
    created_at timestamptz NOT NULL DEFAULT now(),
    started_at timestamptz,
    completed_at timestamptz,
    PRIMARY KEY (organization_id, id),
    CHECK (
        (
            intent = 'analyze' AND
            requested_proposal_digest IS NULL
        )
        OR
        (
            intent = 'apply_change' AND
            requested_proposal_digest IS NOT NULL AND
            length(requested_proposal_digest) > 0
        )
    ),
    CHECK (
        failure IS NULL OR
        failure ->> 'schema_version' = 'ariadne.execution-failure.v1'
    ),
    CHECK (
        (status IN ('completed', 'failed', 'cancelled')) =
        (completed_at IS NOT NULL)
    ),
    CHECK ((status = 'failed') = (failure IS NOT NULL)),
    CHECK (started_at IS NULL OR started_at >= created_at),
    CHECK (completed_at IS NULL OR completed_at >= created_at),
    UNIQUE (organization_id, remediation_run_id, intent, attempt),
    FOREIGN KEY (organization_id, remediation_run_id, project_id)
        REFERENCES remediation_runs (organization_id, id, project_id)
        ON DELETE CASCADE,
    FOREIGN KEY (
        organization_id,
        remediation_run_id,
        project_id,
        requested_proposal_digest
    )
        REFERENCES remediation_runs (
            organization_id,
            id,
            project_id,
            proposal_digest
        )
        ON DELETE CASCADE
);

CREATE INDEX remediation_executions_run_idx
    ON remediation_executions (
        organization_id,
        remediation_run_id,
        created_at DESC
    );

CREATE TABLE remediation_run_runtime_targets (
    organization_id uuid NOT NULL,
    remediation_run_id uuid NOT NULL,
    project_id uuid NOT NULL,
    runtime_environment_id uuid NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (
        organization_id,
        remediation_run_id,
        runtime_environment_id
    ),
    FOREIGN KEY (organization_id, remediation_run_id, project_id)
        REFERENCES remediation_runs (organization_id, id, project_id)
        ON DELETE CASCADE,
    FOREIGN KEY (organization_id, runtime_environment_id, project_id)
        REFERENCES runtime_environments (organization_id, id, project_id)
        ON DELETE RESTRICT
);

CREATE INDEX remediation_run_runtime_target_environment_idx
    ON remediation_run_runtime_targets (
        organization_id,
        runtime_environment_id,
        project_id
    );

CREATE TABLE remediation_approvals (
    organization_id uuid NOT NULL,
    id uuid NOT NULL,
    project_id uuid NOT NULL,
    remediation_run_id uuid NOT NULL,
    actor_user_id uuid NOT NULL,
    proposal_digest text NOT NULL,
    decision text NOT NULL CHECK (decision IN ('approved', 'rejected')),
    comment text,
    decided_at timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (organization_id, id),
    CHECK (length(proposal_digest) > 0),
    UNIQUE (
        organization_id,
        remediation_run_id,
        proposal_digest,
        actor_user_id
    ),
    FOREIGN KEY (
        organization_id,
        remediation_run_id,
        project_id,
        proposal_digest
    )
        REFERENCES remediation_runs (
            organization_id,
            id,
            project_id,
            proposal_digest
        )
        ON DELETE RESTRICT,
    FOREIGN KEY (organization_id, actor_user_id)
        REFERENCES organization_memberships (organization_id, user_id)
        ON DELETE RESTRICT
);

CREATE INDEX remediation_approvals_run_idx
    ON remediation_approvals (
        organization_id,
        remediation_run_id,
        decided_at DESC
    );

CREATE INDEX remediation_approvals_actor_idx
    ON remediation_approvals (organization_id, actor_user_id);

CREATE TABLE merge_requests (
    organization_id uuid NOT NULL,
    id uuid NOT NULL,
    project_id uuid NOT NULL,
    remediation_run_id uuid NOT NULL,
    repository_id uuid NOT NULL,
    provider text NOT NULL,
    external_id text NOT NULL,
    url text NOT NULL,
    status text NOT NULL CHECK (status IN ('open', 'merged', 'closed')),
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (organization_id, id),
    UNIQUE (organization_id, repository_id, provider, external_id),
    FOREIGN KEY (organization_id, remediation_run_id, project_id, repository_id)
        REFERENCES remediation_runs (organization_id, id, project_id, repository_id)
        ON DELETE CASCADE
);

CREATE INDEX merge_requests_remediation_run_idx
    ON merge_requests (
        organization_id,
        remediation_run_id,
        project_id,
        repository_id
    );

CREATE TABLE audit_events (
    organization_id uuid NOT NULL,
    id uuid NOT NULL,
    actor_kind text NOT NULL
        CHECK (actor_kind IN ('user', 'system', 'iop', 'ingestion_source')),
    actor_user_id uuid,
    actor_reference text,
    correlation_id text NOT NULL,
    action text NOT NULL,
    resource_type text NOT NULL,
    resource_id text NOT NULL,
    detail jsonb NOT NULL DEFAULT '{}'::jsonb,
    occurred_at timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (organization_id, id),
    CHECK (length(correlation_id) > 0),
    CHECK (length(action) > 0),
    CHECK (length(resource_type) > 0),
    CHECK (length(resource_id) > 0),
    CHECK (actor_reference IS NULL OR length(actor_reference) > 0),
    CHECK (
        (
            actor_kind = 'user' AND
            actor_user_id IS NOT NULL AND
            actor_reference IS NULL
        )
        OR
        (
            actor_kind <> 'user' AND
            actor_user_id IS NULL AND
            actor_reference IS NOT NULL
        )
    ),
    FOREIGN KEY (organization_id)
        REFERENCES organizations (id) ON DELETE CASCADE,
    FOREIGN KEY (organization_id, actor_user_id)
        REFERENCES organization_memberships (organization_id, user_id)
        ON DELETE RESTRICT
);

CREATE INDEX audit_events_timeline_idx
    ON audit_events (organization_id, occurred_at DESC);

CREATE INDEX audit_events_actor_user_idx
    ON audit_events (organization_id, actor_user_id)
    WHERE actor_user_id IS NOT NULL;

CREATE INDEX audit_events_correlation_idx
    ON audit_events (organization_id, correlation_id, occurred_at);

CREATE FUNCTION ariadne_current_organization_id()
RETURNS uuid
LANGUAGE sql
STABLE
AS $$
    SELECT NULLIF(current_setting('ariadne.organization_id', true), '')::uuid
$$;

REVOKE ALL ON FUNCTION ariadne_current_organization_id() FROM PUBLIC;

ALTER TABLE organizations ENABLE ROW LEVEL SECURITY;
ALTER TABLE organizations FORCE ROW LEVEL SECURITY;
CREATE POLICY organizations_isolation ON organizations
    USING (id = ariadne_current_organization_id())
    WITH CHECK (id = ariadne_current_organization_id());

ALTER TABLE organization_memberships ENABLE ROW LEVEL SECURITY;
ALTER TABLE organization_memberships FORCE ROW LEVEL SECURITY;
CREATE POLICY organization_memberships_isolation ON organization_memberships
    USING (organization_id = ariadne_current_organization_id())
    WITH CHECK (organization_id = ariadne_current_organization_id());

ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
ALTER TABLE projects FORCE ROW LEVEL SECURITY;
CREATE POLICY projects_organization_isolation ON projects
    USING (organization_id = ariadne_current_organization_id())
    WITH CHECK (organization_id = ariadne_current_organization_id());

ALTER TABLE ingestion_sources ENABLE ROW LEVEL SECURITY;
ALTER TABLE ingestion_sources FORCE ROW LEVEL SECURITY;
CREATE POLICY ingestion_sources_organization_isolation ON ingestion_sources
    USING (organization_id = ariadne_current_organization_id())
    WITH CHECK (organization_id = ariadne_current_organization_id());

ALTER TABLE repositories ENABLE ROW LEVEL SECURITY;
ALTER TABLE repositories FORCE ROW LEVEL SECURITY;
CREATE POLICY repositories_organization_isolation ON repositories
    USING (organization_id = ariadne_current_organization_id())
    WITH CHECK (organization_id = ariadne_current_organization_id());

ALTER TABLE runtime_environments ENABLE ROW LEVEL SECURITY;
ALTER TABLE runtime_environments FORCE ROW LEVEL SECURITY;
CREATE POLICY runtime_environments_organization_isolation ON runtime_environments
    USING (organization_id = ariadne_current_organization_id())
    WITH CHECK (organization_id = ariadne_current_organization_id());

ALTER TABLE analysis_target_rules ENABLE ROW LEVEL SECURITY;
ALTER TABLE analysis_target_rules FORCE ROW LEVEL SECURITY;
CREATE POLICY analysis_target_rules_organization_isolation ON analysis_target_rules
    USING (organization_id = ariadne_current_organization_id())
    WITH CHECK (organization_id = ariadne_current_organization_id());

ALTER TABLE analysis_target_rule_runtime_environments ENABLE ROW LEVEL SECURITY;
ALTER TABLE analysis_target_rule_runtime_environments FORCE ROW LEVEL SECURITY;
CREATE POLICY analysis_target_rule_runtime_environments_isolation
    ON analysis_target_rule_runtime_environments
    USING (organization_id = ariadne_current_organization_id())
    WITH CHECK (organization_id = ariadne_current_organization_id());

ALTER TABLE error_groups ENABLE ROW LEVEL SECURITY;
ALTER TABLE error_groups FORCE ROW LEVEL SECURITY;
CREATE POLICY error_groups_organization_isolation ON error_groups
    USING (organization_id = ariadne_current_organization_id())
    WITH CHECK (organization_id = ariadne_current_organization_id());

ALTER TABLE error_occurrences ENABLE ROW LEVEL SECURITY;
ALTER TABLE error_occurrences FORCE ROW LEVEL SECURITY;
CREATE POLICY error_occurrences_organization_isolation ON error_occurrences
    USING (organization_id = ariadne_current_organization_id())
    WITH CHECK (organization_id = ariadne_current_organization_id());

ALTER TABLE remediation_runs ENABLE ROW LEVEL SECURITY;
ALTER TABLE remediation_runs FORCE ROW LEVEL SECURITY;
CREATE POLICY remediation_runs_organization_isolation ON remediation_runs
    USING (organization_id = ariadne_current_organization_id())
    WITH CHECK (organization_id = ariadne_current_organization_id());

ALTER TABLE remediation_executions ENABLE ROW LEVEL SECURITY;
ALTER TABLE remediation_executions FORCE ROW LEVEL SECURITY;
CREATE POLICY remediation_executions_organization_isolation
    ON remediation_executions
    USING (organization_id = ariadne_current_organization_id())
    WITH CHECK (organization_id = ariadne_current_organization_id());

ALTER TABLE remediation_run_runtime_targets ENABLE ROW LEVEL SECURITY;
ALTER TABLE remediation_run_runtime_targets FORCE ROW LEVEL SECURITY;
CREATE POLICY remediation_run_runtime_targets_isolation
    ON remediation_run_runtime_targets
    USING (organization_id = ariadne_current_organization_id())
    WITH CHECK (organization_id = ariadne_current_organization_id());

ALTER TABLE remediation_approvals ENABLE ROW LEVEL SECURITY;
ALTER TABLE remediation_approvals FORCE ROW LEVEL SECURITY;
CREATE POLICY remediation_approvals_organization_isolation
    ON remediation_approvals
    USING (organization_id = ariadne_current_organization_id())
    WITH CHECK (organization_id = ariadne_current_organization_id());

ALTER TABLE merge_requests ENABLE ROW LEVEL SECURITY;
ALTER TABLE merge_requests FORCE ROW LEVEL SECURITY;
CREATE POLICY merge_requests_organization_isolation ON merge_requests
    USING (organization_id = ariadne_current_organization_id())
    WITH CHECK (organization_id = ariadne_current_organization_id());

ALTER TABLE audit_events ENABLE ROW LEVEL SECURITY;
ALTER TABLE audit_events FORCE ROW LEVEL SECURITY;
CREATE POLICY audit_events_organization_isolation ON audit_events
    USING (organization_id = ariadne_current_organization_id())
    WITH CHECK (organization_id = ariadne_current_organization_id());