scriptshare-cursor/temp_api_backup/db/schema.ts

import { mysqlTable, varchar, text, timestamp, int, boolean, json, index } from 'drizzle-orm/mysql-core';
import { relations } from 'drizzle-orm';

// Users table
export const users = mysqlTable('users', {
  id: varchar('id', { length: 255 }).primaryKey(),
  email: varchar('email', { length: 255 }).notNull().unique(),
  username: varchar('username', { length: 100 }).notNull().unique(),
  displayName: varchar('display_name', { length: 100 }).notNull(),
  avatarUrl: varchar('avatar_url', { length: 500 }),
  bio: text('bio'),
  isAdmin: boolean('is_admin').default(false),
  isModerator: boolean('is_moderator').default(false),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().onUpdateNow().notNull(),
}, (table) => ({
  emailIdx: index('email_idx').on(table.email),
  usernameIdx: index('username_idx').on(table.username),
}));

// Scripts table
export const scripts = mysqlTable('scripts', {
  id: varchar('id', { length: 255 }).primaryKey(),
  name: varchar('name', { length: 200 }).notNull(),
  description: text('description').notNull(),
  content: text('content').notNull(),
  compatibleOs: json('compatible_os').$type<string[]>().notNull(),
  categories: json('categories').$type<string[]>().notNull(),
  tags: json('tags').$type<string[]>(),
  gitRepositoryUrl: varchar('git_repository_url', { length: 500 }),
  authorId: varchar('author_id', { length: 255 }).notNull(),
  authorName: varchar('author_name', { length: 100 }).notNull(),
  viewCount: int('view_count').default(0).notNull(),
  downloadCount: int('download_count').default(0).notNull(),
  rating: int('rating').default(0).notNull(),
  ratingCount: int('rating_count').default(0).notNull(),
  isApproved: boolean('is_approved').default(false).notNull(),
  isPublic: boolean('is_public').default(true).notNull(),
  version: varchar('version', { length: 20 }).default('1.0.0').notNull(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().onUpdateNow().notNull(),
}, (table) => ({
  authorIdx: index('author_idx').on(table.authorId),
  approvedIdx: index('approved_idx').on(table.isApproved),
  publicIdx: index('public_idx').on(table.isPublic),
  createdAtIdx: index('created_at_idx').on(table.createdAt),
}));

// Script versions table
export const scriptVersions = mysqlTable('script_versions', {
  id: varchar('id', { length: 255 }).primaryKey(),
  scriptId: varchar('script_id', { length: 255 }).notNull(),
  version: varchar('version', { length: 20 }).notNull(),
  content: text('content').notNull(),
  changelog: text('changelog'),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  createdBy: varchar('created_by', { length: 255 }).notNull(),
}, (table) => ({
  scriptIdx: index('script_idx').on(table.scriptId),
  versionIdx: index('version_idx').on(table.version),
}));



// Ratings table
export const ratings = mysqlTable('ratings', {
  id: varchar('id', { length: 255 }).primaryKey(),
  scriptId: varchar('script_id', { length: 255 }).notNull(),
  userId: varchar('user_id', { length: 255 }).notNull(),
  rating: int('rating').notNull(), // 1-5 stars
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().onUpdateNow().notNull(),
}, (table) => ({
  scriptIdx: index('script_idx').on(table.scriptId),
  userIdx: index('user_idx').on(table.userId),
  uniqueRating: index('unique_rating').on(table.scriptId, table.userId),
}));

// Script collections table
export const scriptCollections = mysqlTable('script_collections', {
  id: varchar('id', { length: 255 }).primaryKey(),
  name: varchar('name', { length: 200 }).notNull(),
  description: text('description'),
  authorId: varchar('author_id', { length: 255 }).notNull(),
  isPublic: boolean('is_public').default(true).notNull(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().onUpdateNow().notNull(),
}, (table) => ({
  authorIdx: index('author_idx').on(table.authorId),
  publicIdx: index('public_idx').on(table.isPublic),
}));

// Collection scripts junction table
export const collectionScripts = mysqlTable('collection_scripts', {
  id: varchar('id', { length: 255 }).primaryKey(),
  collectionId: varchar('collection_id', { length: 255 }).notNull(),
  scriptId: varchar('script_id', { length: 255 }).notNull(),
  addedAt: timestamp('added_at').defaultNow().notNull(),
}, (table) => ({
  collectionIdx: index('collection_idx').on(table.collectionId),
  scriptIdx: index('script_idx').on(table.scriptId),
}));

// Script analytics table
export const scriptAnalytics = mysqlTable('script_analytics', {
  id: varchar('id', { length: 255 }).primaryKey(),
  scriptId: varchar('script_id', { length: 255 }).notNull(),
  eventType: varchar('event_type', { length: 50 }).notNull(), // view, download, share
  userId: varchar('user_id', { length: 255 }),
  userAgent: text('user_agent'),
  ipAddress: varchar('ip_address', { length: 45 }),
  referrer: varchar('referrer', { length: 500 }),
  createdAt: timestamp('created_at').defaultNow().notNull(),
}, (table) => ({
  scriptIdx: index('script_idx').on(table.scriptId),
  eventIdx: index('event_idx').on(table.eventType),
  userIdx: index('user_idx').on(table.userId),
  createdAtIdx: index('created_at_idx').on(table.createdAt),
}));

// Define relationships
export const usersRelations = relations(users, ({ many }) => ({
  scripts: many(scripts),
  ratings: many(ratings),
  collections: many(scriptCollections),
}));

export const scriptsRelations = relations(scripts, ({ one, many }) => ({
  author: one(users, {
    fields: [scripts.authorId],
    references: [users.id],
  }),
  versions: many(scriptVersions),
  ratings: many(ratings),
  analytics: many(scriptAnalytics),
}));

export const scriptVersionsRelations = relations(scriptVersions, ({ one }) => ({
  script: one(scripts, {
    fields: [scriptVersions.scriptId],
    references: [scripts.id],
  }),
}));



export const ratingsRelations = relations(ratings, ({ one }) => ({
  script: one(scripts, {
    fields: [ratings.scriptId],
    references: [scripts.id],
  }),
  user: one(users, {
    fields: [ratings.userId],
    references: [users.id],
  }),
}));

export const scriptCollectionsRelations = relations(scriptCollections, ({ one, many }) => ({
  author: one(users, {
    fields: [scriptCollections.authorId],
    references: [users.id],
  }),
  scripts: many(collectionScripts),
}));

export const collectionScriptsRelations = relations(collectionScripts, ({ one }) => ({
  collection: one(scriptCollections, {
    fields: [collectionScripts.collectionId],
    references: [scriptCollections.id],
  }),
  script: one(scripts, {
    fields: [collectionScripts.scriptId],
    references: [scripts.id],
  }),
}));

export const scriptAnalyticsRelations = relations(scriptAnalytics, ({ one }) => ({
  script: one(scripts, {
    fields: [scriptAnalytics.scriptId],
    references: [scripts.id],
  }),
  user: one(users, {
    fields: [scriptAnalytics.userId],
    references: [users.id],
  }),
}));