CREATE TABLE IF NOT EXISTS "accounts" (
    "id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    "provider" text,
    "provider_account_id" text,
    "refresh_token" text,
    "access_token" text,
    "expires_at" timestamptz,
    "created_at" timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS "users" (
    "id" SERIAL PRIMARY KEY,
    "account_id" uuid,
    "first_name" text,
    "last_name" text,
    "age" INTEGER,
    "email" text UNIQUE null,
    "degree_program" text,
    "degree_term" text,
    "school" text,
    "gender" text,
    "sexual_orientation" text,
    "is_admin" boolean NOT NULL DEFAULT false,
    "updated_at" timestamptz NOT NULL DEFAULT now(),
    "created_at" timestamptz NOT NULL DEFAULT now(),
    FOREIGN KEY ("account_id") REFERENCES "accounts"("id")
);

CREATE TABLE IF NOT EXISTS "sessions" (
    "id" SERIAL PRIMARY KEY,
    "user_id" INTEGER NOT NULL,
    "access_token" text NOT NULL,
    "refresh_token" text NOT NULL,
    "access_token_expires_at" timestamptz NOT NULL,
    "refresh_token_expires_at" timestamptz NOT NULL,
    "updated_at" timestamptz NOT NULL DEFAULT now(),
    "created_at" timestamptz NOT NULL DEFAULT now(),
    FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS "sessions_user_id_idx" ON "sessions" ("user_id");
CREATE UNIQUE INDEX IF NOT EXISTS "sessions_access_token_key" ON "sessions" ("access_token");
CREATE UNIQUE INDEX IF NOT EXISTS "sessions_refresh_token_key" ON "sessions" ("refresh_token");

CREATE TABLE IF NOT EXISTS "bars" (
    "bar_id" SERIAL PRIMARY KEY,
    "name" text NOT NULL,
    "description" text,
    "google_place_id" text,
    "address" text,
    "latitude" float,
    "longitude" float,
    "is_active" boolean NOT NULL DEFAULT TRUE,
    "created_at" timestamptz NOT NULL DEFAULT now(),
    "updated_at" timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS "reports" (
    "report_id" SERIAL PRIMARY KEY,
    "user_id" INTEGER,
    "bar_id" INTEGER NOT NULL,
    "wait_time" INTEGER,
    "busyness" INTEGER,
    "music" text[],
    "report_time" timestamptz DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE CASCADE,
    FOREIGN KEY ("bar_id") REFERENCES "bars"("bar_id") ON DELETE CASCADE
);