-- ShortLink database schema

CREATE TABLE IF NOT EXISTS links (
    id          SERIAL PRIMARY KEY,
    code        VARCHAR(20)  UNIQUE NOT NULL,
    original_url TEXT        NOT NULL,
    title       VARCHAR(255),
    created_at  TIMESTAMP   NOT NULL DEFAULT NOW(),
    created_by_ip VARCHAR(45)
);

CREATE TABLE IF NOT EXISTS visits (
    id          SERIAL PRIMARY KEY,
    link_id     INTEGER     NOT NULL REFERENCES links(id) ON DELETE CASCADE,
    visited_at  TIMESTAMP   NOT NULL DEFAULT NOW(),
    ip_address  VARCHAR(45),
    user_agent  TEXT,
    referer     TEXT
);

CREATE INDEX IF NOT EXISTS idx_links_code      ON links(code);
CREATE INDEX IF NOT EXISTS idx_visits_link_id  ON visits(link_id);
CREATE INDEX IF NOT EXISTS idx_visits_visited  ON visits(visited_at);