import {
    pgTable,
    uuid,
    bigint,
    timestamp,
    integer,
    text,
    boolean,
    numeric,
} from "drizzle-orm/pg-core"

import { tenants } from "./tenants"
import { authUsers } from "./auth_users"
import { timesStateEnum } from "./enums"
import {sql} from "drizzle-orm";

export const stafftimeentries = pgTable("staff_time_entries", {
    id: uuid("id").primaryKey().defaultRandom(),

    tenant_id: bigint("tenant_id", { mode: "number" })
        .notNull()
        .references(() => tenants.id),

    user_id: uuid("user_id")
        .notNull()
        .references(() => authUsers.id, { onDelete: "cascade" }),

    started_at: timestamp("started_at", { withTimezone: true }).notNull(),
    stopped_at: timestamp("stopped_at", { withTimezone: true }),

    duration_minutes: integer("duration_minutes").generatedAlwaysAs(
        sql`CASE 
      WHEN stopped_at IS NOT NULL 
      THEN (EXTRACT(epoch FROM (stopped_at - started_at)) / 60)
      ELSE NULL
    END`
    ),

    type: text("type").default("work"),

    description: text("description"),

    created_at: timestamp("created_at", { withTimezone: true }).defaultNow(),
    updated_at: timestamp("updated_at", { withTimezone: true }).defaultNow(),

    archived: boolean("archived").notNull().default(false),

    updated_by: uuid("updated_by").references(() => authUsers.id),

    source: text("source"),

    state: timesStateEnum("state").notNull().default("draft"),

    device: uuid("device"),

    internal_note: text("internal_note"),

    vacation_reason: text("vacation_reason"),
    vacation_days: numeric("vacation_days", { precision: 5, scale: 2 }),

    approved_by: uuid("approved_by").references(() => authUsers.id),
    approved_at: timestamp("approved_at", { withTimezone: true }),

    sick_reason: text("sick_reason"),
})

export type StaffTimeEntry = typeof stafftimeentries.$inferSelect
export type NewStaffTimeEntry = typeof stafftimeentries.$inferInsert