// ✅ CORRECT - Drizzle
export const users = pgTable("users", {
  id: uuid("id").primaryKey().defaultRandom(),
  tenant_id: uuid("tenant_id").notNull(), // REQUIRED!
  created_at: timestamp("created_at").defaultNow().notNull(),
  updated_at: timestamp("updated_at").defaultNow().notNull(),
  // ... other fields
});

# ✅ CORRECT - SQLModel
class User(SQLModel, table=True):
    __tablename__ = "users"
    
    id: UUID = Field(default_factory=uuid4, primary_key=True)
    tenant_id: UUID = Field(foreign_key="tenants.id", index=True) # REQUIRED!
    created_at: datetime = Field(default_factory=datetime.utcnow)
    updated_at: datetime = Field(default_factory=datetime.utcnow)
    # ... other fields

// ✅ CORRECT
email_address: text("email_address")
created_at: timestamp("created_at")
is_active: boolean("is_active")
tenant_id: uuid("tenant_id")

// ❌ WRONG
emailAddress: text("emailAddress")  // WRONG!
createdAt: timestamp("createdAt")   // WRONG!

// Every table should have:
id: uuid("id").primaryKey().defaultRandom()
created_at: timestamp("created_at").defaultNow().notNull()
updated_at: timestamp("updated_at").defaultNow().notNull()
tenant_id: uuid("tenant_id").notNull()
deleted_at: timestamp("deleted_at") // For soft deletes (optional)

// Drizzle
export const tenants = pgTable("tenants", {
  id: uuid("id").primaryKey().defaultRandom(),
  name: text("name").notNull(),
  slug: text("slug").notNull().unique(),
  is_active: boolean("is_active").default(true).notNull(),
  created_at: timestamp("created_at").defaultNow().notNull(),
  updated_at: timestamp("updated_at").defaultNow().notNull(),
});

# SQLModel
class Tenant(SQLModel, table=True):
    __tablename__ = "tenants"
    
    id: UUID = Field(default_factory=uuid4, primary_key=True)
    name: str = Field(max_length=255)
    slug: str = Field(max_length=100, unique=True)
    is_active: bool = Field(default=True)
    created_at: datetime = Field(default_factory=datetime.utcnow)
    updated_at: datetime = Field(default_factory=datetime.utcnow)

// Drizzle
export const users = pgTable("users", {
  id: uuid("id").primaryKey().defaultRandom(),
  tenant_id: uuid("tenant_id").notNull(),
  email_address: text("email_address").notNull().unique(),
  full_name: text("full_name").notNull(),
  is_active: boolean("is_active").default(true).notNull(),
  created_at: timestamp("created_at").defaultNow().notNull(),
  updated_at: timestamp("updated_at").defaultNow().notNull(),
  deleted_at: timestamp("deleted_at"),
});

// Index for tenant_id
export const usersTenantIndex = index("users_tenant_id_idx").on(users.tenant_id);

# SQLModel
class User(SQLModel, table=True):
    __tablename__ = "users"
    
    id: UUID = Field(default_factory=uuid4, primary_key=True)
    tenant_id: UUID = Field(foreign_key="tenants.id", index=True)
    email_address: str = Field(max_length=255, unique=True)
    full_name: str = Field(max_length=255)
    is_active: bool = Field(default=True)
    created_at: datetime = Field(default_factory=datetime.utcnow)
    updated_at: datetime = Field(default_factory=datetime.utcnow)
    deleted_at: Optional[datetime] = None

// Drizzle - User has many Posts
export const posts = pgTable("posts", {
  id: uuid("id").primaryKey().defaultRandom(),
  tenant_id: uuid("tenant_id").notNull(),
  user_id: uuid("user_id").notNull(),
  title: text("title").notNull(),
  // ... other fields
});

// Relations
export const usersRelations = relations(users, ({ many }) => ({
  posts: many(posts),
}));

export const postsRelations = relations(posts, ({ one }) => ({
  user: one(users, {
    fields: [posts.user_id],
    references: [users.id],
  }),
}));

// Drizzle - User has many Roles through UserRoles
export const user_roles = pgTable("user_roles", {
  id: uuid("id").primaryKey().defaultRandom(),
  tenant_id: uuid("tenant_id").notNull(),
  user_id: uuid("user_id").notNull(),
  role_id: uuid("role_id").notNull(),
  created_at: timestamp("created_at").defaultNow().notNull(),
});

// Indexes for join table
export const userRolesUserIndex = index("user_roles_user_id_idx").on(user_roles.user_id);
export const userRolesRoleIndex = index("user_roles_role_id_idx").on(user_roles.role_id);

-- Enable RLS
ALTER TABLE users ENABLE ROW LEVEL SECURITY;

-- Tenant isolation policy
CREATE POLICY "tenant_isolation"
ON users
FOR ALL
USING (tenant_id = current_setting('app.tenant_id')::uuid);

-- Admin override policy
CREATE POLICY "admin_override"
ON users
FOR ALL
TO admin_role
USING (true);

// ALWAYS index tenant_id
export const usersTenantIndex = index("users_tenant_id_idx").on(users.tenant_id);

// Index foreign keys
export const postsUserIndex = index("posts_user_id_idx").on(posts.user_id);

// Composite indexes for common queries
export const postsCompositeIndex = index("posts_tenant_user_idx")
  .on(posts.tenant_id, posts.user_id);

# Generate migration
bun run db:generate

# Apply migration
bun run db:migrate

# Rollback migration (manual)

# Generate migration
alembic revision --autogenerate -m "add users table"

# Apply migration
alembic upgrade head

# Rollback migration
alembic downgrade -1