import { index, integer, sqliteTable, text } from 'drizzle-orm/sqlite-core';

export const projects = sqliteTable('projects', {
  id: text('id').primaryKey(),
  name: text('name').notNull(),
  projectNumber: text('project_number').notNull().default(''),
  location: text('location').notNull().default(''),
  active: integer('active', { mode: 'boolean' }).notNull().default(true),
  createdBy: text('created_by').notNull(),
  createdAt: text('created_at').notNull(),
}, (table) => [index('idx_projects_active_name').on(table.active, table.name)]);

export const reports = sqliteTable('reports', {
  id: text('id').primaryKey(),
  projectId: text('project_id').notNull().references(() => projects.id),
  userId: text('user_id').notNull(),
  userEmail: text('user_email').notNull(),
  userName: text('user_name').notNull(),
  reportDate: text('report_date').notNull(),
  workers: integer('workers').notNull(),
  startTime: text('start_time').notNull(),
  endTime: text('end_time').notNull(),
  weather: text('weather').notNull().default(''),
  temperature: text('temperature').notNull().default(''),
  workOriginal: text('work_original').notNull(),
  workGerman: text('work_german').notNull().default(''),
  obstaclesOriginal: text('obstacles_original').notNull().default(''),
  obstaclesGerman: text('obstacles_german').notNull().default(''),
  materialsOriginal: text('materials_original').notNull().default(''),
  materialsGerman: text('materials_german').notNull().default(''),
  translationStatus: text('translation_status').notNull().default('pending'),
  createdAt: text('created_at').notNull(),
}, (table) => [
  index('idx_reports_project_date').on(table.projectId, table.reportDate),
  index('idx_reports_user_date').on(table.userId, table.reportDate),
]);

export const photos = sqliteTable('photos', {
  id: text('id').primaryKey(),
  reportId: text('report_id').notNull().references(() => reports.id, { onDelete: 'cascade' }),
  objectKey: text('object_key').notNull(),
  fileName: text('file_name').notNull(),
  contentType: text('content_type').notNull(),
  size: integer('size').notNull(),
  createdAt: text('created_at').notNull(),
}, (table) => [index('idx_photos_report').on(table.reportId)]);