db.sql

CREATE DATABASE ShortUrl;

CREATE TABLE IF NOT EXISTS users (
    id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email TEXT NOT NULL UNIQUE,
    password TEXT NOT NULL,
    role INTEGER DEFAULT 2 NOT NULL
);

CREATE TABLE IF NOT EXISTS password_reset_guids (
    id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    id_user INTEGER,
    guid VARCHAR(36) NOT NULL,
    expiration_datetime TIMESTAMP NOT NULL,

    FOREIGN KEY (id_user) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS urls (
    id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    id_user INTEGER,
    original_url TEXT NOT NULL,
    short_url TEXT NOT NULL,
    clicks INTEGER DEFAULT 0,
    last_click TIMESTAMP,
    expiration_datetime TIMESTAMP NOT NULL,

    FOREIGN KEY (id_user) REFERENCES users(id) ON DELETE CASCADE
);