import { pgTable, uuid, varchar, text, numeric, integer, boolean, date, timestamp, jsonb, pgEnum } from 'drizzle-orm/pg-core'
import { accounts } from './accounts.js'
import { locations } from './stores.js'
import { users } from './users.js'
import { transactions } from './pos.js'

// Enums
export const invoiceStatusEnum = pgEnum('invoice_status', ['draft', 'sent', 'paid', 'partial', 'overdue', 'void', 'written_off'])
export const accountTypeEnum = pgEnum('account_type', ['asset', 'liability', 'revenue', 'contra_revenue', 'cogs', 'expense'])
export const normalBalanceEnum = pgEnum('normal_balance', ['debit', 'credit'])
export const journalLineTypeEnum = pgEnum('journal_line_type', ['debit', 'credit'])
export const billingRunStatusEnum = pgEnum('billing_run_status', ['pending', 'running', 'completed', 'failed'])

// Core: Invoice
export const invoices = pgTable('invoice', {
  id: uuid('id').primaryKey().defaultRandom(),
  invoiceNumber: varchar('invoice_number', { length: 50 }).notNull().unique(),
  accountId: uuid('account_id').notNull().references(() => accounts.id),
  locationId: uuid('location_id').references(() => locations.id),
  status: invoiceStatusEnum('status').notNull().default('draft'),
  issueDate: date('issue_date').notNull().defaultNow(),
  dueDate: date('due_date').notNull().defaultNow(),
  sourceType: varchar('source_type', { length: 50 }),
  sourceId: uuid('source_id'),
  subtotal: numeric('subtotal', { precision: 10, scale: 2 }).notNull().default('0'),
  discountTotal: numeric('discount_total', { precision: 10, scale: 2 }).notNull().default('0'),
  taxTotal: numeric('tax_total', { precision: 10, scale: 2 }).notNull().default('0'),
  total: numeric('total', { precision: 10, scale: 2 }).notNull().default('0'),
  amountPaid: numeric('amount_paid', { precision: 10, scale: 2 }).notNull().default('0'),
  balance: numeric('balance', { precision: 10, scale: 2 }).notNull().default('0'),
  refundOfInvoiceId: uuid('refund_of_invoice_id'),
  notes: text('notes'),
  createdBy: uuid('created_by').references(() => users.id),
  createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
})

// Core: Invoice Line Item
export const invoiceLineItems = pgTable('invoice_line_item', {
  id: uuid('id').primaryKey().defaultRandom(),
  invoiceId: uuid('invoice_id').notNull().references(() => invoices.id, { onDelete: 'cascade' }),
  description: varchar('description', { length: 255 }).notNull(),
  qty: integer('qty').notNull().default(1),
  unitPrice: numeric('unit_price', { precision: 10, scale: 2 }).notNull(),
  discountAmount: numeric('discount_amount', { precision: 10, scale: 2 }).notNull().default('0'),
  taxRate: numeric('tax_rate', { precision: 5, scale: 4 }).notNull().default('0'),
  taxAmount: numeric('tax_amount', { precision: 10, scale: 2 }).notNull().default('0'),
  lineTotal: numeric('line_total', { precision: 10, scale: 2 }).notNull().default('0'),
  accountCodeId: uuid('account_code_id'),
  sourceType: varchar('source_type', { length: 50 }),
  sourceId: uuid('source_id'),
  createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
})

// Core: Payment Application
export const paymentApplications = pgTable('payment_application', {
  id: uuid('id').primaryKey().defaultRandom(),
  invoiceId: uuid('invoice_id').notNull().references(() => invoices.id),
  transactionId: uuid('transaction_id').references(() => transactions.id),
  amount: numeric('amount', { precision: 10, scale: 2 }).notNull(),
  appliedAt: timestamp('applied_at', { withTimezone: true }).notNull().defaultNow(),
  appliedBy: uuid('applied_by').notNull().references(() => users.id),
  notes: text('notes'),
  createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
})

// Core: Account Balance (materialized AR)
export const accountBalances = pgTable('account_balance', {
  id: uuid('id').primaryKey().defaultRandom(),
  accountId: uuid('account_id').notNull().unique().references(() => accounts.id),
  currentBalance: numeric('current_balance', { precision: 10, scale: 2 }).notNull().default('0'),
  lastInvoiceDate: date('last_invoice_date'),
  lastPaymentDate: date('last_payment_date'),
  updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
})

// Accounting module: Chart of Accounts
export const accountCodes = pgTable('account_code', {
  id: uuid('id').primaryKey().defaultRandom(),
  code: varchar('code', { length: 10 }).notNull().unique(),
  name: varchar('name', { length: 255 }).notNull(),
  accountType: accountTypeEnum('account_type').notNull(),
  normalBalance: normalBalanceEnum('normal_balance').notNull(),
  isSystem: boolean('is_system').notNull().default(true),
  isActive: boolean('is_active').notNull().default(true),
  createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
})

// Accounting module: Journal Entry
export const journalEntries = pgTable('journal_entry', {
  id: uuid('id').primaryKey().defaultRandom(),
  entryNumber: varchar('entry_number', { length: 20 }).notNull().unique(),
  entryDate: date('entry_date').notNull().defaultNow(),
  entryType: varchar('entry_type', { length: 50 }).notNull(),
  sourceType: varchar('source_type', { length: 50 }),
  sourceId: uuid('source_id'),
  description: text('description').notNull(),
  totalDebits: numeric('total_debits', { precision: 10, scale: 2 }).notNull(),
  totalCredits: numeric('total_credits', { precision: 10, scale: 2 }).notNull(),
  isVoid: boolean('is_void').notNull().default(false),
  voidReason: text('void_reason'),
  voidedBy: uuid('voided_by').references(() => users.id),
  voidedAt: timestamp('voided_at', { withTimezone: true }),
  reversalOfId: uuid('reversal_of_id'),
  createdBy: uuid('created_by').notNull().references(() => users.id),
  createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
})

// Accounting module: Journal Entry Line
export const journalEntryLines = pgTable('journal_entry_line', {
  id: uuid('id').primaryKey().defaultRandom(),
  journalEntryId: uuid('journal_entry_id').notNull().references(() => journalEntries.id, { onDelete: 'cascade' }),
  accountCodeId: uuid('account_code_id').notNull().references(() => accountCodes.id),
  lineType: journalLineTypeEnum('line_type').notNull(),
  amount: numeric('amount', { precision: 10, scale: 2 }).notNull(),
  description: text('description'),
  entityType: varchar('entity_type', { length: 50 }),
  entityId: uuid('entity_id'),
  createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
})

// Lessons module: Billing Run
export const billingRuns = pgTable('billing_run', {
  id: uuid('id').primaryKey().defaultRandom(),
  runDate: date('run_date').notNull(),
  status: billingRunStatusEnum('status').notNull().default('pending'),
  enrollmentsProcessed: integer('enrollments_processed').notNull().default(0),
  invoicesGenerated: integer('invoices_generated').notNull().default(0),
  totalAmount: numeric('total_amount', { precision: 10, scale: 2 }).notNull().default('0'),
  errors: jsonb('errors'),
  startedAt: timestamp('started_at', { withTimezone: true }),
  completedAt: timestamp('completed_at', { withTimezone: true }),
  createdBy: uuid('created_by').references(() => users.id),
  createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
})

// Type exports
export type Invoice = typeof invoices.$inferSelect
export type InvoiceInsert = typeof invoices.$inferInsert
export type InvoiceLineItem = typeof invoiceLineItems.$inferSelect
export type PaymentApplication = typeof paymentApplications.$inferSelect
export type AccountBalance = typeof accountBalances.$inferSelect
export type AccountCode = typeof accountCodes.$inferSelect
export type JournalEntry = typeof journalEntries.$inferSelect
export type JournalEntryLine = typeof journalEntryLines.$inferSelect
export type BillingRun = typeof billingRuns.$inferSelect
