CREATE TABLE IF NOT EXISTS shortened_urls (
    id                 BIGSERIAL    PRIMARY KEY,
    code               VARCHAR(16)  NOT NULL UNIQUE,
    long_url           TEXT         NOT NULL,

    path               TEXT         NOT NULL,
    content_type       VARCHAR(32)  NOT NULL,
    book_code          VARCHAR(16),
    division           VARCHAR(16),
    chapter            VARCHAR(64),
    verse              VARCHAR(32),
    slug               VARCHAR(255),

    has_highlight      BOOLEAN      NOT NULL DEFAULT FALSE,
    highlight_param    TEXT,

    creator_ip         INET,
    creator_ua         TEXT,
    creator_referer    TEXT,
    creator_country    CHAR(2),

    created_at         TIMESTAMPTZ  NOT NULL DEFAULT now(),
    expires_at         TIMESTAMPTZ,
    disabled           BOOLEAN      NOT NULL DEFAULT FALSE,
    disabled_reason    TEXT,
    disabled_at        TIMESTAMPTZ,

    click_count        BIGINT       NOT NULL DEFAULT 0,
    human_click_count  BIGINT       NOT NULL DEFAULT 0,
    first_clicked_at   TIMESTAMPTZ,
    last_clicked_at    TIMESTAMPTZ
);

CREATE INDEX IF NOT EXISTS shortened_urls_created_at_idx   ON shortened_urls (created_at DESC);
CREATE INDEX IF NOT EXISTS shortened_urls_content_type_idx ON shortened_urls (content_type);
CREATE INDEX IF NOT EXISTS shortened_urls_book_code_idx    ON shortened_urls (book_code)        WHERE book_code  IS NOT NULL;
CREATE INDEX IF NOT EXISTS shortened_urls_creator_ip_idx   ON shortened_urls (creator_ip)       WHERE creator_ip IS NOT NULL;
CREATE INDEX IF NOT EXISTS shortened_urls_expires_at_idx   ON shortened_urls (expires_at)       WHERE expires_at IS NOT NULL;
CREATE INDEX IF NOT EXISTS shortened_urls_click_count_idx  ON shortened_urls (click_count DESC) WHERE NOT disabled;