-- ============================================================================
-- 04 Officials, Champions, claims, votes, groups, follows
-- ============================================================================

-- Offices (President, Governor of X, Senator for Y district, Commissioner for Z ...)
CREATE TABLE offices (
    id          integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title       varchar(120) NOT NULL,
    level       varchar(8)   NOT NULL CHECK (level IN ('federal','state','lga')),
    branch      varchar(12)  NOT NULL CHECK (branch IN ('executive','legislative','agency')),
    state_id    smallint     REFERENCES states(id),
    lga_id      integer      REFERENCES lgas(id),
    constituency varchar(120),
    created_at  timestamptz  NOT NULL DEFAULT now(),
    UNIQUE NULLS NOT DISTINCT (title, state_id, lga_id, constituency)
);
CREATE INDEX offices_state_idx ON offices (state_id, lga_id);
CREATE INDEX offices_title_trgm ON offices USING gin (title gin_trgm_ops);

-- People who hold offices. A record can exist before the official joins; they
-- later "claim" it by verifying who they are (official email, ID, manual review).
CREATE TABLE public_officials (
    id              integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    office_id       integer      NOT NULL REFERENCES offices(id) ON DELETE CASCADE,
    full_name       varchar(150) NOT NULL,
    party           varchar(60),
    photo_path      varchar(255),
    term_start      date,
    term_end        date,
    is_current      boolean      NOT NULL DEFAULT true,
    user_id         bigint       UNIQUE REFERENCES users(id) ON DELETE SET NULL,   -- set after verified claim
    verification_status varchar(12) NOT NULL DEFAULT 'unclaimed'
        CHECK (verification_status IN ('unclaimed','pending','verified','rejected')),
    verified_at     timestamptz,
    verified_by_staff_id bigint  REFERENCES staff_users(id),
    created_at      timestamptz  NOT NULL DEFAULT now(),
    updated_at      timestamptz  NOT NULL DEFAULT now(),
    CONSTRAINT verified_requires_user CHECK (verification_status <> 'verified' OR user_id IS NOT NULL)
);
CREATE INDEX public_officials_office_idx ON public_officials (office_id) WHERE is_current;
CREATE INDEX public_officials_name_trgm ON public_officials USING gin (full_name gin_trgm_ops);

-- Officials a post author tags as responsible
CREATE TABLE post_official_tags (
    post_id     bigint  NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    official_id integer NOT NULL REFERENCES public_officials(id) ON DELETE CASCADE,
    PRIMARY KEY (post_id, official_id)
);
CREATE TABLE post_office_tags (
    post_id     bigint  NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    office_id   integer NOT NULL REFERENCES offices(id) ON DELETE CASCADE,
    PRIMARY KEY (post_id, office_id)
);

-- Champions: anyone who takes responsibility for fixing an absurdity
CREATE TABLE champions (
    id              bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id         bigint      NOT NULL UNIQUE REFERENCES users(id) ON DELETE CASCADE,
    kind            varchar(12) NOT NULL
        CHECK (kind IN ('official','politician','celebrity','individual','organisation')),
    official_id     integer     REFERENCES public_officials(id) ON DELETE SET NULL,
    headline        varchar(160),
    verified_badge  boolean     NOT NULL DEFAULT false,      -- only after staff verification
    verified_at     timestamptz,
    approve_score   integer     NOT NULL DEFAULT 0,          -- cached weighted totals
    disapprove_score integer    NOT NULL DEFAULT 0,
    claims_total    integer     NOT NULL DEFAULT 0,
    claims_resolved integer     NOT NULL DEFAULT 0,
    created_at      timestamptz NOT NULL DEFAULT now(),
    updated_at      timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT official_champion_needs_record CHECK (kind <> 'official' OR official_id IS NOT NULL)
);
CREATE INDEX champions_kind_idx ON champions (kind);

-- Stages: Claimed -> In progress -> Fix reported (with evidence) -> Resolved (community confirmed)
CREATE TABLE champion_claims (
    id              bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    post_id         bigint      NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    champion_id     bigint      NOT NULL REFERENCES champions(id) ON DELETE CASCADE,
    status          varchar(14) NOT NULL DEFAULT 'claimed'
        CHECK (status IN ('claimed','in_progress','fix_reported','resolved','withdrawn','lapsed')),
    statement       varchar(1000),
    target_date     date,
    last_update_at  timestamptz NOT NULL DEFAULT now(),       -- lapses automatically if stale
    resolved_at     timestamptz,
    like_count      integer     NOT NULL DEFAULT 0,
    comment_count   integer     NOT NULL DEFAULT 0,
    share_count     integer     NOT NULL DEFAULT 0,
    created_at      timestamptz NOT NULL DEFAULT now(),
    updated_at      timestamptz NOT NULL DEFAULT now(),
    UNIQUE (post_id, champion_id)
);
CREATE INDEX champion_claims_champion_idx ON champion_claims (champion_id, status);
CREATE INDEX champion_claims_stale_idx ON champion_claims (last_update_at) WHERE status IN ('claimed','in_progress');

CREATE TABLE claim_updates (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    claim_id    bigint      NOT NULL REFERENCES champion_claims(id) ON DELETE CASCADE,
    status      varchar(14) NOT NULL
        CHECK (status IN ('claimed','in_progress','fix_reported','resolved','withdrawn','lapsed')),
    body        varchar(2000),
    evidence_media jsonb    NOT NULL DEFAULT '[]'::jsonb,
    created_at  timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX claim_updates_claim_idx ON claim_updates (claim_id, created_at);

-- Community confirmation that a reported fix is real
CREATE TABLE claim_resolution_votes (
    claim_id    bigint  NOT NULL REFERENCES champion_claims(id) ON DELETE CASCADE,
    user_id     bigint  NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    confirms_fix boolean NOT NULL,
    created_at  timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (claim_id, user_id)
);

CREATE TABLE claim_reactions (
    claim_id    bigint      NOT NULL REFERENCES champion_claims(id) ON DELETE CASCADE,
    user_id     bigint      NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    type        varchar(20) NOT NULL CHECK (type IN ('support','needs_evidence','not_helpful')),
    created_at  timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (claim_id, user_id)
);

CREATE TABLE claim_comments (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    claim_id    bigint      NOT NULL REFERENCES champion_claims(id) ON DELETE CASCADE,
    user_id     bigint      NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    parent_id   bigint      REFERENCES claim_comments(id) ON DELETE CASCADE,
    body        varchar(2000) NOT NULL,
    status      varchar(10) NOT NULL DEFAULT 'visible' CHECK (status IN ('visible','hidden','removed')),
    deleted_at  timestamptz,
    purge_after timestamptz,
    created_at  timestamptz NOT NULL DEFAULT now()
);

-- Approve / Disapprove votes on a Champion. One vote each; weight reduces brigading.
CREATE TABLE champion_votes (
    champion_id bigint      NOT NULL REFERENCES champions(id) ON DELETE CASCADE,
    user_id     bigint      NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    vote        varchar(10) NOT NULL CHECK (vote IN ('approve','disapprove')),
    weight      numeric(4,2) NOT NULL DEFAULT 1.00,       -- from account age, verification, trust level
    created_at  timestamptz NOT NULL DEFAULT now(),
    updated_at  timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (champion_id, user_id)
);

-- ----------------------------------------------------------------------------
-- Groups (movements such as the Ratel movement)
-- ----------------------------------------------------------------------------
CREATE TABLE groups (
    id              bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    uuid            uuid         NOT NULL DEFAULT gen_random_uuid() UNIQUE,
    name            citext       NOT NULL UNIQUE,
    slug            citext       NOT NULL UNIQUE CHECK (slug ~ '^[A-Za-z0-9_-]{3,40}$'),   -- custom URL
    description     varchar(1000),
    avatar_path     varchar(255),
    cover_path      varchar(255),
    privacy         varchar(8)   NOT NULL DEFAULT 'open' CHECK (privacy IN ('open','invite')),
    status          varchar(10)  NOT NULL DEFAULT 'active' CHECK (status IN ('active','disabled','suspended')),
    created_by_user_id bigint    NOT NULL REFERENCES users(id),
    member_count    integer      NOT NULL DEFAULT 0,
    deleted_at      timestamptz,
    deleted_by_type varchar(8)   CHECK (deleted_by_type IN ('user','staff','system')),
    deleted_by_id   bigint,
    purge_after     timestamptz,
    created_at      timestamptz  NOT NULL DEFAULT now(),
    updated_at      timestamptz  NOT NULL DEFAULT now()
);
CREATE INDEX groups_name_trgm ON groups USING gin ((name::text) gin_trgm_ops);

CREATE TABLE group_members (
    group_id    bigint      NOT NULL REFERENCES groups(id) ON DELETE CASCADE,
    user_id     bigint      NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    role        varchar(10) NOT NULL DEFAULT 'member' CHECK (role IN ('owner','admin','moderator','member')),
    joined_at   timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (group_id, user_id)
);
CREATE INDEX group_members_user_idx ON group_members (user_id);

CREATE TABLE group_join_requests (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    group_id    bigint      NOT NULL REFERENCES groups(id) ON DELETE CASCADE,
    user_id     bigint      NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    status      varchar(10) NOT NULL DEFAULT 'pending' CHECK (status IN ('pending','approved','declined')),
    decided_by_user_id bigint REFERENCES users(id),
    created_at  timestamptz NOT NULL DEFAULT now(),
    UNIQUE (group_id, user_id)
);

CREATE TABLE group_invitations (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    group_id    bigint      NOT NULL REFERENCES groups(id) ON DELETE CASCADE,
    invited_by_user_id bigint NOT NULL REFERENCES users(id),
    invitee_user_id bigint  REFERENCES users(id) ON DELETE CASCADE,
    token_hash  char(64)    UNIQUE,
    expires_at  timestamptz NOT NULL,
    accepted_at timestamptz,
    created_at  timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE group_posts (
    group_id    bigint NOT NULL REFERENCES groups(id) ON DELETE CASCADE,
    post_id     bigint NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    added_by_user_id bigint REFERENCES users(id),
    created_at  timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (group_id, post_id)
);

-- ----------------------------------------------------------------------------
-- Follows: users, champions, groups, states/LGAs, categories
-- ----------------------------------------------------------------------------
CREATE TABLE follows (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id     bigint      NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    target_type varchar(10) NOT NULL CHECK (target_type IN ('user','champion','group','state','lga','category','official')),
    target_id   bigint      NOT NULL,
    created_at  timestamptz NOT NULL DEFAULT now(),
    UNIQUE (user_id, target_type, target_id)
);
CREATE INDEX follows_target_idx ON follows (target_type, target_id);

CREATE TRIGGER public_officials_updated BEFORE UPDATE ON public_officials FOR EACH ROW EXECUTE FUNCTION set_updated_at();
CREATE TRIGGER champions_updated        BEFORE UPDATE ON champions        FOR EACH ROW EXECUTE FUNCTION set_updated_at();
CREATE TRIGGER champion_claims_updated  BEFORE UPDATE ON champion_claims  FOR EACH ROW EXECUTE FUNCTION set_updated_at();
CREATE TRIGGER champion_votes_updated   BEFORE UPDATE ON champion_votes   FOR EACH ROW EXECUTE FUNCTION set_updated_at();
CREATE TRIGGER groups_updated           BEFORE UPDATE ON groups           FOR EACH ROW EXECUTE FUNCTION set_updated_at();