-- Migration: Initial Schema
-- Generated by Drizzle Kit
-- This is an example migration file showing the structure
-- Actual migrations should be generated with: drizzle-kit generate

-- Create users table
CREATE TABLE `users` (
  `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
  `email` text NOT NULL,
  `name` text NOT NULL,
  `bio` text,
  `created_at` integer NOT NULL,
  `updated_at` integer
);

-- Create unique index on email
CREATE UNIQUE INDEX `users_email_unique` ON `users` (`email`);

-- Create index on email for faster lookups
CREATE INDEX `users_email_idx` ON `users` (`email`);

-- Create index on created_at for sorting
CREATE INDEX `users_created_at_idx` ON `users` (`created_at`);

-- Create posts table
CREATE TABLE `posts` (
  `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
  `title` text NOT NULL,
  `slug` text NOT NULL,
  `content` text NOT NULL,
  `published` integer DEFAULT false NOT NULL,
  `author_id` integer NOT NULL,
  `created_at` integer NOT NULL,
  `updated_at` integer,
  FOREIGN KEY (`author_id`) REFERENCES `users`(`id`) ON DELETE cascade
);

-- Create unique index on slug
CREATE UNIQUE INDEX `posts_slug_unique` ON `posts` (`slug`);

-- Create index on slug for URL lookups
CREATE INDEX `posts_slug_idx` ON `posts` (`slug`);

-- Create index on author_id for user's posts
CREATE INDEX `posts_author_idx` ON `posts` (`author_id`);

-- Create composite index on published + created_at
CREATE INDEX `posts_published_created_idx` ON `posts` (`published`, `created_at`);

-- Create comments table
CREATE TABLE `comments` (
  `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
  `content` text NOT NULL,
  `post_id` integer NOT NULL,
  `author_id` integer NOT NULL,
  `created_at` integer NOT NULL,
  FOREIGN KEY (`post_id`) REFERENCES `posts`(`id`) ON DELETE cascade,
  FOREIGN KEY (`author_id`) REFERENCES `users`(`id`) ON DELETE cascade
);

-- Create index on post_id for post's comments
CREATE INDEX `comments_post_idx` ON `comments` (`post_id`);

-- Create index on author_id for user's comments
CREATE INDEX `comments_author_idx` ON `comments` (`author_id`);

-- Optimize database
PRAGMA optimize;