-- Create Postgres tables
create table if not exists oauth2_state_storage (
    id uuid PRIMARY KEY default gen_random_uuid(),
    csrf_state text NOT NULL,
    pkce_code_verifier text NOT NULL,
    return_url text NOT NULL
);

create table if not exists user_sessions (
    id uuid PRIMARY KEY default gen_random_uuid(),
    user_id uuid NOT NULL,
    session_token_p1 text NOT NULL,
    session_token_p2 text NOT NULL,
    created_at timestamp NOT NULL,
    expires_at timestamp NOT NULL
);

create table if not exists users (
    id uuid PRIMARY KEY default gen_random_uuid(),
    created_at timestamp NOT NULL default now(),
    created_by uuid NOT NULL,
    updated_at timestamp NOT NULL default now(),
    updated_by uuid NOT NULL,
    email text NOT NULL UNIQUE,
    "name" text NOT NULL,
    family_name text NOT NULL,
    given_name text NOT NULL
);

create table IF NOT EXISTS roles (
    id uuid PRIMARY KEY default gen_random_uuid(),
    created_at timestamp NOT NULL default now(),
    created_by uuid NOT NULL,
    updated_at timestamp NOT NULL default now(),
    updated_by uuid NOT NULL,
    "name" TEXT NOT NULL,
    "description" TEXT
);

create table IF NOT EXISTS user_roles (
    id uuid PRIMARY KEY default gen_random_uuid(),
    created_at timestamp NOT NULL default now(),
    created_by uuid NOT NULL,
    updated_at timestamp NOT NULL default now(),
    updated_by uuid NOT NULL,
    user_id uuid NOT NULL,
    role_id uuid NOT NULL
);

create unique index if not exists unique_user_role on user_roles(user_id, role_id);

create table IF NOT EXISTS role_permissions (
    id uuid PRIMARY KEY default gen_random_uuid(),
    created_at timestamp NOT NULL default now(),
    created_by uuid NOT NULL,
    updated_at timestamp NOT NULL default now(),
    updated_by uuid NOT NULL,
    role_id uuid NOT NULL,
    item text NOT NULL
);

create table if not exists wishlist_items (
    id uuid PRIMARY KEY default gen_random_uuid(),
    created_at timestamp not null default now(),
    created_by uuid null,
    updated_at timestamp null default now(),
    updated_by uuid null,
    user_id uuid null,
    item varchar(512) null,
    item_url varchar(1024) null,
    purchased_by uuid null,
    received_at timestamp null
);

create table if not exists gift_exchange (
    id uuid PRIMARY KEY default gen_random_uuid(),
    created_at timestamp not null default now(),
    created_by uuid not null,
    updated_at timestamp not null default now(),
    updated_by uuid not null,
    "name" varchar(255) not null,
    exchange_date timestamp not null,
    "status" INTEGER not null default 0
);

create table if not exists gift_exchange_participants (
    id uuid PRIMARY KEY default gen_random_uuid(),
    created_at timestamp not null default now(),
    created_by uuid not null,
    updated_at timestamp not null default now(),
    updated_by uuid not null,
    exchange_id uuid not null,
    participant_id uuid not null,
    gifter_id uuid not null
);

-- Calendars
-- 1 - Cottage
-- 2 - Family tree
create table if not exists calendar (
    id uuid PRIMARY KEY default gen_random_uuid(),
    created_at timestamp not null default now(),
    created_by uuid not null,
    updated_at timestamp null default now(),
    updated_by uuid not null,
    "name" varchar(255) not null
);

-- Event types
-- 1 - Rental
-- 2 - Life event
create table if not exists calendar_event_types (
    id uuid PRIMARY KEY default gen_random_uuid(),
    created_at timestamp not null default now(),
    created_by uuid not null,
    updated_at timestamp null default now(),
    updated_by uuid not null,
    "name" varchar(255) not null
);

create table if not exists calendar_events (
    id uuid PRIMARY KEY default gen_random_uuid(),
    created_at timestamp not null default now(),
    created_by uuid not null,
    updated_at timestamp null default now(),
    updated_by uuid not null,
    calendar_id uuid not null,
    event_type_id uuid not null,
    title varchar(255) not null,
    "description" varchar(255) null,
    start_time timestamp null,
    end_time timestamp null,
    repeat_type integer not null default 0,
    -- 0 - None, 1 - Daily, 2 - Weekly, 3 - Monthly, 4 - Yearly, 5 - Day of week, 6 - Day of month
    repeat_interval integer not null default 0,
    celebrate boolean not null default true
);

do $$

declare user_uuid uuid := gen_random_uuid();

begin -- Initial user
insert into users (
        id,
        "name",
        created_by,
        updated_by,
        email,
        family_name,
        given_name
    )
values
    (
        user_uuid,
        'admin',
        user_uuid,
        user_uuid,
        'admin@jean-marie.ca',
        '',
        'admin'
    );

-- Initial roles
INSERT INTO
    roles (created_by, updated_by, "name", "description")
VALUES
    (
        user_uuid,
        user_uuid,
        'public',
        'Users with only anonymous access'
    );

INSERT INTO
    roles (created_by, updated_by, "name", "description")
VALUES
    (
        user_uuid,
        user_uuid,
        'normal',
        'Users with no elevated privileges'
    );

INSERT INTO
    roles (created_by, updated_by, "name", "description")
VALUES
    (
        user_uuid,
        user_uuid,
        'editor',
        'Users with basic elevated privileges'
    );

INSERT INTO
    roles (created_by, updated_by, "name", "description")
VALUES
    (
        user_uuid,
        user_uuid,
        'admin',
        'Users with full administrative privileges'
    );

INSERT INTO
    roles (created_by, updated_by, "name", "description")
VALUES
    (
        user_uuid,
        user_uuid,
        'calendar',
        'Users with access to the calendar'
    );

-- Initial permissions
INSERT INTO
    role_permissions (created_by, updated_by, role_id, item)
VALUES
    (
        user_uuid,
        user_uuid,
        (
            SELECT
                id
            FROM
                roles
            WHERE
                "name" = 'public'
        ),
        '/'
    );

INSERT INTO
    role_permissions (created_by, updated_by, role_id, item)
VALUES
    (
        user_uuid,
        user_uuid,
        (
            SELECT
                id
            FROM
                roles
            WHERE
                "name" = 'public'
        ),
        '/login'
    );

INSERT INTO
    role_permissions (created_by, updated_by, role_id, item)
VALUES
    (
        user_uuid,
        user_uuid,
        (
            SELECT
                id
            FROM
                roles
            WHERE
                "name" = 'public'
        ),
        '/logout'
    );

INSERT INTO
    role_permissions (created_by, updated_by, role_id, item)
VALUES
    (
        user_uuid,
        user_uuid,
        (
            SELECT
                id
            FROM
                roles
            WHERE
                "name" = 'normal'
        ),
        '/dashboard'
    );

INSERT INTO
    role_permissions (created_by, updated_by, role_id, item)
VALUES
    (
        user_uuid,
        user_uuid,
        (
            SELECT
                id
            FROM
                roles
            WHERE
                "name" = 'normal'
        ),
        '/profile'
    );

INSERT INTO
    role_permissions (created_by, updated_by, role_id, item)
VALUES
    (
        user_uuid,
        user_uuid,
        (
            SELECT
                id
            FROM
                roles
            WHERE
                "name" = 'admin'
        ),
        '/useradmin'
    );

INSERT INTO
    role_permissions (created_by, updated_by, role_id, item)
VALUES
    (
        user_uuid,
        user_uuid,
        (
            SELECT
                id
            FROM
                roles
            WHERE
                "name" = 'admin'
        ),
        '/users'
    );

INSERT INTO
    role_permissions (created_by, updated_by, role_id, item)
VALUES
    (
        user_uuid,
        user_uuid,
        (
            SELECT
                id
            FROM
                roles
            WHERE
                "name" = 'calendar'
        ),
        '/calendar'
    );

INSERT INTO
    role_permissions (created_by, updated_by, role_id, item)
VALUES
    (
        user_uuid,
        user_uuid,
        (
            SELECT
                id
            FROM
                roles
            WHERE
                "name" = 'normal'
        ),
        '/wishlist'
    );

INSERT INTO
    role_permissions (created_by, updated_by, role_id, item)
VALUES
    (
        user_uuid,
        user_uuid,
        (
            SELECT
                id
            FROM
                roles
            WHERE
                "name" = 'normal'
        ),
        '/giftexchange'
    );

end $$;