import { eq, desc, and } from "drizzle-orm";
import { drizzle } from "drizzle-orm/mysql2";
import { InsertUser, users, services, faqs, testimonials, galleryImages, blogPosts, contactSubmissions, Service, FAQ, Testimonial, GalleryImage, BlogPost, ContactSubmission, InsertService, InsertFAQ, InsertTestimonial, InsertGalleryImage, InsertBlogPost, InsertContactSubmission } 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;
}

// ===== SERVICES =====
export async function getAllServices(): Promise<Service[]> {
  const db = await getDb();
  if (!db) return [];
  
  return await db
    .select()
    .from(services)
    .where(eq(services.isActive, true))
    .orderBy(services.order);
}

export async function getServiceBySlug(slug: string): Promise<Service | undefined> {
  const db = await getDb();
  if (!db) return undefined;
  
  const result = await db
    .select()
    .from(services)
    .where(and(eq(services.slug, slug), eq(services.isActive, true)))
    .limit(1);
  
  return result.length > 0 ? result[0] : undefined;
}

export async function createService(service: InsertService): Promise<Service> {
  const db = await getDb();
  if (!db) throw new Error("Database not available");
  
  const result = await db.insert(services).values(service);
  const id = result[0].insertId;
  
  const created = await db.select().from(services).where(eq(services.id, id as number)).limit(1);
  return created[0];
}

// ===== FAQs =====
export async function getFAQsByCategory(category: string): Promise<FAQ[]> {
  const db = await getDb();
  if (!db) return [];
  
  return await db
    .select()
    .from(faqs)
    .where(and(eq(faqs.category, category as any), eq(faqs.isActive, true)))
    .orderBy(faqs.order);
}

export async function getAllFAQs(): Promise<FAQ[]> {
  const db = await getDb();
  if (!db) return [];
  
  return await db
    .select()
    .from(faqs)
    .where(eq(faqs.isActive, true))
    .orderBy(faqs.category, faqs.order);
}

export async function createFAQ(faq: InsertFAQ): Promise<FAQ> {
  const db = await getDb();
  if (!db) throw new Error("Database not available");
  
  const result = await db.insert(faqs).values(faq);
  const id = result[0].insertId;
  
  const created = await db.select().from(faqs).where(eq(faqs.id, id as number)).limit(1);
  return created[0];
}

// ===== TESTIMONIALS =====
export async function getAllTestimonials(): Promise<Testimonial[]> {
  const db = await getDb();
  if (!db) return [];
  
  return await db
    .select()
    .from(testimonials)
    .where(eq(testimonials.isActive, true))
    .orderBy(testimonials.order);
}

export async function createTestimonial(testimonial: InsertTestimonial): Promise<Testimonial> {
  const db = await getDb();
  if (!db) throw new Error("Database not available");
  
  const result = await db.insert(testimonials).values(testimonial);
  const id = result[0].insertId;
  
  const created = await db.select().from(testimonials).where(eq(testimonials.id, id as number)).limit(1);
  return created[0];
}

// ===== GALLERY =====
export async function getAllGalleryImages(): Promise<GalleryImage[]> {
  const db = await getDb();
  if (!db) return [];
  
  return await db
    .select()
    .from(galleryImages)
    .where(eq(galleryImages.isActive, true))
    .orderBy(galleryImages.order);
}

export async function getGalleryImagesByCategory(category: string): Promise<GalleryImage[]> {
  const db = await getDb();
  if (!db) return [];
  
  return await db
    .select()
    .from(galleryImages)
    .where(and(eq(galleryImages.category, category), eq(galleryImages.isActive, true)))
    .orderBy(galleryImages.order);
}

export async function createGalleryImage(image: InsertGalleryImage): Promise<GalleryImage> {
  const db = await getDb();
  if (!db) throw new Error("Database not available");
  
  const result = await db.insert(galleryImages).values(image);
  const id = result[0].insertId;
  
  const created = await db.select().from(galleryImages).where(eq(galleryImages.id, id as number)).limit(1);
  return created[0];
}

// ===== BLOG POSTS =====
export async function getAllPublishedBlogPosts(): Promise<BlogPost[]> {
  const db = await getDb();
  if (!db) return [];
  
  return await db
    .select()
    .from(blogPosts)
    .where(eq(blogPosts.isPublished, true))
    .orderBy(desc(blogPosts.publishedAt));
}

export async function getBlogPostBySlug(slug: string): Promise<BlogPost | undefined> {
  const db = await getDb();
  if (!db) return undefined;
  
  const result = await db
    .select()
    .from(blogPosts)
    .where(and(eq(blogPosts.slug, slug), eq(blogPosts.isPublished, true)))
    .limit(1);
  
  return result.length > 0 ? result[0] : undefined;
}

export async function getBlogPostsByCategory(category: string): Promise<BlogPost[]> {
  const db = await getDb();
  if (!db) return [];
  
  return await db
    .select()
    .from(blogPosts)
    .where(and(eq(blogPosts.category, category), eq(blogPosts.isPublished, true)))
    .orderBy(desc(blogPosts.publishedAt));
}

export async function createBlogPost(post: InsertBlogPost): Promise<BlogPost> {
  const db = await getDb();
  if (!db) throw new Error("Database not available");
  
  const result = await db.insert(blogPosts).values(post);
  const id = result[0].insertId;
  
  const created = await db.select().from(blogPosts).where(eq(blogPosts.id, id as number)).limit(1);
  return created[0];
}

// ===== CONTACT SUBMISSIONS =====
export async function createContactSubmission(submission: InsertContactSubmission): Promise<ContactSubmission> {
  const db = await getDb();
  if (!db) throw new Error("Database not available");
  
  const result = await db.insert(contactSubmissions).values(submission);
  const id = result[0].insertId;
  
  const created = await db.select().from(contactSubmissions).where(eq(contactSubmissions.id, id as number)).limit(1);
  return created[0];
}

export async function getContactSubmissions(): Promise<ContactSubmission[]> {
  const db = await getDb();
  if (!db) return [];
  
  return await db
    .select()
    .from(contactSubmissions)
    .orderBy(desc(contactSubmissions.createdAt));
}