import { eq } from "drizzle-orm";
import { drizzle } from "drizzle-orm/mysql2";
import { InsertUser, users, templates, templateFields, protocols, protocolValues } from "../drizzle/schema";
import type { InsertTemplate, InsertTemplateField, InsertProtocol } from "../drizzle/schema";
import { ENV } from './_core/env';

let _db: ReturnType<typeof drizzle> | null = null;

// Lazily create the drizzle instance so local tooling can run without a DB.
export async function getDb() {
  if (!_db && process.env.DATABASE_URL) {
    try {
      _db = drizzle(process.env.DATABASE_URL);
    } catch (error) {
      console.warn("[Database] Failed to connect:", error);
      _db = null;
    }
  }
  return _db;
}

export async function upsertUser(user: InsertUser): Promise<void> {
  if (!user.openId) {
    throw new Error("User openId is required for upsert");
  }

  const db = await getDb();
  if (!db) {
    console.warn("[Database] Cannot upsert user: database not available");
    return;
  }

  try {
    const values: InsertUser = {
      openId: user.openId,
    };
    const updateSet: Record<string, unknown> = {};

    const textFields = ["name", "email", "loginMethod"] as const;
    type TextField = (typeof textFields)[number];

    const assignNullable = (field: TextField) => {
      const value = user[field];
      if (value === undefined) return;
      const normalized = value ?? null;
      values[field] = normalized;
      updateSet[field] = normalized;
    };

    textFields.forEach(assignNullable);

    if (user.lastSignedIn !== undefined) {
      values.lastSignedIn = user.lastSignedIn;
      updateSet.lastSignedIn = user.lastSignedIn;
    }
    if (user.role !== undefined) {
      values.role = user.role;
      updateSet.role = user.role;
    } else if (user.openId === ENV.ownerOpenId) {
      values.role = 'admin';
      updateSet.role = 'admin';
    }

    if (!values.lastSignedIn) {
      values.lastSignedIn = new Date();
    }

    if (Object.keys(updateSet).length === 0) {
      updateSet.lastSignedIn = new Date();
    }

    await db.insert(users).values(values).onDuplicateKeyUpdate({
      set: updateSet,
    });
  } catch (error) {
    console.error("[Database] Failed to upsert user:", error);
    throw error;
  }
}

export async function getUserByOpenId(openId: string) {
  const db = await getDb();
  if (!db) {
    console.warn("[Database] Cannot get user: database not available");
    return undefined;
  }

  const result = await db.select().from(users).where(eq(users.openId, openId)).limit(1);

  return result.length > 0 ? result[0] : undefined;
}

// Templates queries
export async function getTemplatesByUserId(userId: number) {
  const db = await getDb();
  if (!db) return [];
  return db.select().from(templates).where(eq(templates.userId, userId));
}

export async function getTemplateById(templateId: number) {
  const db = await getDb();
  if (!db) return undefined;
  const result = await db.select().from(templates).where(eq(templates.id, templateId)).limit(1);
  return result.length > 0 ? result[0] : undefined;
}

export async function createTemplate(template: InsertTemplate) {
  const db = await getDb();
  if (!db) throw new Error("Database not available");
  const result = await db.insert(templates).values(template);
  return result;
}

export async function updateTemplate(templateId: number, updates: Partial<InsertTemplate>) {
  const db = await getDb();
  if (!db) throw new Error("Database not available");
  return db.update(templates).set(updates).where(eq(templates.id, templateId));
}

export async function deleteTemplate(templateId: number) {
  const db = await getDb();
  if (!db) throw new Error("Database not available");
  return db.delete(templates).where(eq(templates.id, templateId));
}

// Template fields queries
export async function getTemplateFields(templateId: number) {
  const db = await getDb();
  if (!db) return [];
  return db.select().from(templateFields).where(eq(templateFields.templateId, templateId)).orderBy(templateFields.order);
}

export async function addTemplateField(field: InsertTemplateField) {
  const db = await getDb();
  if (!db) throw new Error("Database not available");
  return db.insert(templateFields).values(field);
}

export async function deleteTemplateField(fieldId: number) {
  const db = await getDb();
  if (!db) throw new Error("Database not available");
  return db.delete(templateFields).where(eq(templateFields.id, fieldId));
}

// Protocols queries
export async function getProtocolsByUserId(userId: number) {
  const db = await getDb();
  if (!db) return [];
  return db.select().from(protocols).where(eq(protocols.userId, userId));
}

export async function getProtocolById(protocolId: number) {
  const db = await getDb();
  if (!db) return undefined;
  const result = await db.select().from(protocols).where(eq(protocols.id, protocolId)).limit(1);
  return result.length > 0 ? result[0] : undefined;
}

export async function createProtocol(protocol: InsertProtocol) {
  const db = await getDb();
  if (!db) throw new Error("Database not available");
  const result = await db.insert(protocols).values(protocol);
  return result;
}

export async function updateProtocol(protocolId: number, updates: Partial<InsertProtocol>) {
  const db = await getDb();
  if (!db) throw new Error("Database not available");
  return db.update(protocols).set(updates).where(eq(protocols.id, protocolId));
}

// Protocol values queries
export async function getProtocolValues(protocolId: number) {
  const db = await getDb();
  if (!db) return [];
  return db.select().from(protocolValues).where(eq(protocolValues.protocolId, protocolId));
}

export async function setProtocolValue(protocolId: number, fieldId: number, value: string) {
  const db = await getDb();
  if (!db) throw new Error("Database not available");
  
  // Check if value already exists
  const existing = await db.select().from(protocolValues)
    .where(eq(protocolValues.protocolId, protocolId) && eq(protocolValues.fieldId, fieldId))
    .limit(1);
  
  if (existing.length > 0) {
    return db.update(protocolValues).set({ value }).where(eq(protocolValues.id, existing[0].id));
  } else {
    return db.insert(protocolValues).values({ protocolId, fieldId, value });
  }
}