import {
  pgTable,
  text,
  timestamp,
  uuid,
  varchar,
  integer,
  boolean,
  index,
  uniqueIndex,
  pgEnum,
  check,
} from "drizzle-orm/pg-core";
import { sql, relations } from "drizzle-orm";
 
// ALWAYS use UUID v7 for primary keys (time-sortable)
// ALWAYS include createdAt and updatedAt timestamps
// ALWAYS use enums for fixed value sets
 
export const userRoleEnum = pgEnum("user_role", ["admin", "editor", "viewer"]);
 
export const users = pgTable(
  "users",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    email: varchar("email", { length: 255 }).notNull(),
    name: varchar("name", { length: 100 }).notNull(),
    role: userRoleEnum("role").notNull().default("viewer"),
    avatarUrl: text("avatar_url"),
    emailVerifiedAt: timestamp("email_verified_at", { withTimezone: true }),
    createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp("updated_at", { withTimezone: true })
      .notNull()
      .defaultNow()
      .$onUpdate(() => new Date()),
  },
  (table) => [
    uniqueIndex("users_email_idx").on(table.email),
    index("users_role_idx").on(table.role),
    index("users_created_at_idx").on(table.createdAt),
  ]
);
 
export const posts = pgTable(
  "posts",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    title: varchar("title", { length: 255 }).notNull(),
    slug: varchar("slug", { length: 300 }).notNull(),
    content: text("content").notNull().default(""),
    excerpt: varchar("excerpt", { length: 500 }),
    status: varchar("status", { length: 20, enum: ["draft", "published", "archived"] })
      .notNull()
      .default("draft"),
    authorId: uuid("author_id")
      .notNull()
      .references(() => users.id, { onDelete: "cascade" }),
    publishedAt: timestamp("published_at", { withTimezone: true }),
    createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp("updated_at", { withTimezone: true })
      .notNull()
      .defaultNow()
      .$onUpdate(() => new Date()),
  },
  (table) => [
    uniqueIndex("posts_slug_idx").on(table.slug),
    index("posts_author_id_idx").on(table.authorId),
    index("posts_status_published_at_idx").on(table.status, table.publishedAt),
    // Partial index: only index published posts for the public listing query
    index("posts_published_idx")
      .on(table.publishedAt)
      .where(sql`status = 'published'`),
  ]
);
 
// Relations for Drizzle query builder
export const usersRelations = relations(users, ({ many }) => ({
  posts: many(posts),
}));
 
export const postsRelations = relations(posts, ({ one }) => ({
  author: one(users, {
    fields: [posts.authorId],
    references: [users.id],
  }),
}));

-- ALWAYS: Index foreign keys
-- ALWAYS: Index columns used in WHERE, JOIN, ORDER BY
-- ALWAYS: Use partial indexes when queries filter on a specific value
-- ALWAYS: Create indexes CONCURRENTLY in production migrations
 
-- GOOD: Composite index matching query pattern
-- Query: SELECT * FROM posts WHERE author_id = ? AND status = 'published' ORDER BY published_at DESC
CREATE INDEX CONCURRENTLY idx_posts_author_status_date
ON posts (author_id, status, published_at DESC)
WHERE status = 'published';
 
-- GOOD: Covering index to avoid heap fetch
-- Query: SELECT id, title, slug FROM posts WHERE status = 'published'
CREATE INDEX CONCURRENTLY idx_posts_published_covering
ON posts (status, published_at DESC)
INCLUDE (id, title, slug)
WHERE status = 'published';
 
-- GOOD: GIN index for JSONB queries
CREATE INDEX CONCURRENTLY idx_users_metadata
ON users USING gin (metadata jsonb_path_ops);
 
-- GOOD: GIN index for full-text search
CREATE INDEX CONCURRENTLY idx_posts_search
ON posts USING gin (to_tsvector('english', title || ' ' || content));

-- ALWAYS: Add columns as nullable first, then backfill, then add NOT NULL
-- Step 1: Add nullable column
ALTER TABLE users ADD COLUMN phone varchar(20);
 
-- Step 2: Backfill data (in batches for large tables)
UPDATE users SET phone = '' WHERE phone IS NULL AND id > $last_id LIMIT 10000;
 
-- Step 3: Add NOT NULL constraint (after all rows have values)
ALTER TABLE users ALTER COLUMN phone SET NOT NULL;
 
-- ALWAYS: Create indexes concurrently
CREATE INDEX CONCURRENTLY idx_users_phone ON users (phone);
 
-- NEVER: Drop columns directly in production (use expand-contract)
-- Step 1: Stop writing to the column in application code
-- Step 2: Deploy code that ignores the column
-- Step 3: Drop the column in a later migration
ALTER TABLE users DROP COLUMN legacy_field;
 
-- NEVER: Rename columns directly (breaks running code)
-- Instead: Add new column, dual-write, migrate readers, drop old column
 
-- ALWAYS: Set lock timeout for DDL statements
SET lock_timeout = '5s';
ALTER TABLE users ADD COLUMN bio text;

-- ALWAYS: Use EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) to check query plans
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT p.*, u.name as author_name
FROM posts p
JOIN users u ON u.id = p.author_id
WHERE p.status = 'published'
ORDER BY p.published_at DESC
LIMIT 20;
 
-- Watch for:
-- Seq Scan on large tables (missing index)
-- Nested Loop with high row counts (consider Hash Join)
-- Sort with high memory usage (add index for ORDER BY)
-- Bitmap Heap Scan with many recheck conditions (index not selective enough)
 
-- GOOD: Cursor-based pagination (consistent performance)
SELECT * FROM posts
WHERE published_at < $cursor_date
  AND status = 'published'
ORDER BY published_at DESC
LIMIT 20;
 
-- BAD: Offset pagination (slow for deep pages)
SELECT * FROM posts ORDER BY published_at DESC LIMIT 20 OFFSET 10000;
 
-- GOOD: Use EXISTS instead of IN for subqueries with large result sets
SELECT * FROM users u
WHERE EXISTS (
  SELECT 1 FROM posts p WHERE p.author_id = u.id AND p.status = 'published'
);