-- Migration generated by Prax

-- Enum types
DO $$ BEGIN
    CREATE TYPE "role" AS ENUM ('User', 'Admin', 'Moderator');
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;

DO $$ BEGIN
    CREATE TYPE "post_status" AS ENUM ('Draft', 'Published', 'Archived');
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;


-- Tables
CREATE TABLE IF NOT EXISTS "users" (
    "id" SERIAL,
    "email" TEXT UNIQUE NOT NULL,
    "name" TEXT,
    "role" "role" NOT NULL DEFAULT 'User',
    "active" BOOLEAN NOT NULL DEFAULT TRUE,
    "created_at" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP WITH TIME ZONE NOT NULL,
    PRIMARY KEY ("id")
);

CREATE TABLE IF NOT EXISTS "posts" (
    "id" SERIAL,
    "title" TEXT NOT NULL,
    "content" TEXT,
    "status" "post_status" NOT NULL DEFAULT 'Draft',
    "published" BOOLEAN NOT NULL DEFAULT FALSE,
    "views" INTEGER NOT NULL DEFAULT 0,
    "created_at" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP WITH TIME ZONE NOT NULL,
    "author_id" INTEGER NOT NULL,
    PRIMARY KEY ("id")
);

CREATE TABLE IF NOT EXISTS "comments" (
    "id" SERIAL,
    "content" TEXT NOT NULL,
    "created_at" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP WITH TIME ZONE NOT NULL,
    "author_id" INTEGER,
    "post_id" INTEGER NOT NULL,
    PRIMARY KEY ("id")
);

