import Dexie from 'dexie'

export interface SettingEntry {
  key: string
  value: string
}

export interface ProjectDocRecord {
  projectId: number
  projectName: string
  updatedAt: string
  sessionCount: number
  markdown: string
}

export interface SessionRecord {
  id?: number
  projectId: number
  date: string
  title: string
  fileName: string
  fileType: string
  fileSize: number
  rawText: string
  processedAt: string
}

export interface SessionSummaryRecord {
  sessionId: number
  summary: string
  objectives: string
  tasks: string
  commitments: string
  decisions: string
  keyPoints: string
  modelUsed: string
}

export interface ProjectStateRecord {
  projectId: number
  summary: string
  objectives: string
  tasks: string
  commitments: string
  updatedAt: string
}

export interface HuDraftRecord {
  id: string
  projectId: number
  title: string
  description: string
  acceptanceCriteria: string
  priority: string
  story_points?: number
  type: string        // 'U' = HU, 'E' = Epic, 'F' = Feature, 'T' = Task, 'B' = Bug
  metadata: string    // JSON opcional: { linkedHuTitles?: string[], estimatedStart?: string, estimatedEnd?: string }
  sourceSessionId?: number
  syncStatus: 'draft' | 'pushing' | 'pushed'
  kappaId?: number
  createdAt: string
}

export interface QAPlanRecord {
  id: string
  projectId: number
  huId: string
  huTitle: string
  plan: string
  createdAt: string
}

export interface UserRecord {
  id: number
  email: string
  firstName: string
  lastName: string
  fullName: string
  username: string | null
  isActive: boolean
  isStaff: boolean
  roleId: number | null
  cellId: number | null
  seniority: string | null
  companyId: number | null
  phone: string | null
  position: string | null
  lastActive: string | null
  createdAt: string | null
  updatedAt: string
}

export interface LookupRecord {
  type: string   // 'role' | 'company' | 'seniority'
  id: number
  name: string
  description: string | null
}

export interface CellRecord {
  id: string       // UUID
  name: string
  description: string
  type: 'normal' | 'transversal'
  createdAt: string
}

export interface CellMemberRecord {
  cellId: string
  userId: number
  roleInCell: string   // 'dev' | 'qa' | 'ba' | 'pm' | 'tl'
  addedAt: string
}

export interface DexieUserStory {
  id: number
  initiative_id: number
  title: string
  status: string | null
  priority: string | null
  assigned_to: number | null
  code: string | null
  description: string | null
  acceptance_criteria: string | null
  story_points: number | null
  sprint: string | null
  has_impairment: boolean | number
  created_at: string | null
}

export interface PromptRecord {
  key: string
  content: string
  label: string
  updatedAt: string
}

export interface DexieBlocker {
  id?: number
  projectId: number
  huId?: number
  huTitle?: string
  date: string
  category: string
  description: string
  spAffected: number
  resolvedAt?: string
  resolution?: string
  timeLostHours: number
  createdBy?: string
  createdAt: string
}

const db = new Dexie('alpha-core') as Dexie & {
  settings: Dexie.Table<SettingEntry, string>
  project_docs: Dexie.Table<ProjectDocRecord, number>
  sessions: Dexie.Table<SessionRecord, number>
  session_summaries: Dexie.Table<SessionSummaryRecord, number>
  project_state: Dexie.Table<ProjectStateRecord, number>
  hu_drafts: Dexie.Table<HuDraftRecord, string>
  qa_plans: Dexie.Table<QAPlanRecord, string>
  users: Dexie.Table<UserRecord, number>
  lookups: Dexie.Table<LookupRecord, string>
  cells: Dexie.Table<CellRecord, string>
  cell_members: Dexie.Table<CellMemberRecord, string>
  user_stories: Dexie.Table<DexieUserStory, number>
  prompts: Dexie.Table<PromptRecord, string>
  blockers: Dexie.Table<DexieBlocker, number>
}

db.version(10).stores({
  settings: '&key',
  project_docs: '&projectId, projectName, updatedAt',
  sessions: '++id, projectId, date',
  session_summaries: '&sessionId',
  project_state: '&projectId',
  hu_drafts: '&id, projectId, syncStatus',
  qa_plans: '&id, projectId',
  users: '&id',
  lookups: '&[type+id], type',
  cells: '&id',
  cell_members: '[cellId+userId], cellId, userId',
  user_stories: '&id, initiative_id',
  prompts: '&key',
  blockers: '++id, projectId, category',
})

export default db
