-- Sorun kategorileri
CREATE TABLE issue_categories (
    id        BIGSERIAL PRIMARY KEY,
    parent_id BIGINT REFERENCES issue_categories(id) ON DELETE RESTRICT,
    name      TEXT NOT NULL,
    slug      TEXT NOT NULL UNIQUE,
    icon      TEXT
);

CREATE INDEX idx_issue_categories_parent ON issue_categories(parent_id);

-- Sorun durumu
CREATE TYPE issue_status AS ENUM (
    'open', 'in_progress', 'resolved', 'rejected', 'duplicate'
);

-- Sorunlar
CREATE TABLE issues (
    id                   BIGSERIAL PRIMARY KEY,
    title                TEXT NOT NULL,
    body                 TEXT NOT NULL,
    category_id          BIGINT NOT NULL REFERENCES issue_categories(id),
    location_id          BIGINT NOT NULL REFERENCES locations(id),
    reporter_id          BIGINT NOT NULL REFERENCES users(id),
    status               issue_status NOT NULL DEFAULT 'open',
    resolution_threshold SMALLINT NOT NULL DEFAULT 70,   -- % çözüldü oyu eşiği
    resolved_at          TIMESTAMPTZ,
    created_at           TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at           TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_issues_category ON issues(category_id);
CREATE INDEX idx_issues_location ON issues(location_id);
CREATE INDEX idx_issues_reporter ON issues(reporter_id);
CREATE INDEX idx_issues_status   ON issues(status);

-- Sorun ↔ yetkili eşlemesi
CREATE TYPE assignment_source AS ENUM ('system', 'moderator', 'official_claim', 'ai');

CREATE TABLE issue_officials (
    id          BIGSERIAL PRIMARY KEY,
    issue_id    BIGINT NOT NULL REFERENCES issues(id) ON DELETE CASCADE,
    person_id   BIGINT REFERENCES persons(id) ON DELETE CASCADE,
    org_id      BIGINT REFERENCES organizations(id) ON DELETE CASCADE,
    unit_id     BIGINT REFERENCES administrative_units(id) ON DELETE CASCADE,
    assigned_by assignment_source NOT NULL DEFAULT 'system',
    confidence  SMALLINT CHECK (confidence BETWEEN 0 AND 100),
    assigned_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT at_least_one_target CHECK (
        person_id IS NOT NULL OR org_id IS NOT NULL OR unit_id IS NOT NULL
    )
);

CREATE INDEX idx_issue_officials_issue  ON issue_officials(issue_id);
CREATE INDEX idx_issue_officials_person ON issue_officials(person_id);
CREATE INDEX idx_issue_officials_unit   ON issue_officials(unit_id);

-- Oylar
CREATE TYPE vote_value AS ENUM ('resolved', 'ongoing');

CREATE TABLE issue_votes (
    id         BIGSERIAL PRIMARY KEY,
    issue_id   BIGINT NOT NULL REFERENCES issues(id) ON DELETE CASCADE,
    user_id    BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    vote       vote_value NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (issue_id, user_id)
);

CREATE INDEX idx_issue_votes_issue ON issue_votes(issue_id);
CREATE INDEX idx_issue_votes_user  ON issue_votes(user_id);

-- Yorumlar
CREATE TABLE issue_comments (
    id         BIGSERIAL PRIMARY KEY,
    issue_id   BIGINT NOT NULL REFERENCES issues(id) ON DELETE CASCADE,
    user_id    BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    parent_id  BIGINT REFERENCES issue_comments(id) ON DELETE CASCADE,
    body       TEXT NOT NULL,
    is_deleted BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_issue_comments_issue  ON issue_comments(issue_id);
CREATE INDEX idx_issue_comments_parent ON issue_comments(parent_id);