-- +goose Up
CREATE TABLE users (
    id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    username varchar(20) UNIQUE NOT NULL CONSTRAINT non_blank_username CHECK(length(username) > 0),
    password text NOT NULL CONSTRAINT valid_hash CHECK (length(password) = 60)
);

CREATE TABLE favourites (
    id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    user_id int NOT NULL,
    event_guid uuid,
    event_id int,
    UNIQUE(user_id, event_guid, event_id),
    CONSTRAINT require_event_detail CHECK (event_guid IS NOT NULL OR event_id IS NOT NULL),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE calendars (
    id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    user_id int NOT NULL,
    name text NOT NULL CONSTRAINT non_blank_name CHECK(length(name) > 0),
    key text NOT NULL,
    UNIQUE(user_id),
    UNIQUE(name),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);