// server/storage.ts
import bcrypt from "bcrypt";
import { eq, desc } from "drizzle-orm";
import {
  users,
  forms,
  formHistory,
  // Types
  type User,
  type Form,
  type FormHistory,
  type InsertUser,
  type InsertForm,
  type InsertFormHistory,
} from "../shared/schema.js";

/**
 * لایه‌ی دسترسی به دیتابیس (نسخهٔ لوکال)
 * ⚠️ نمونه‌ی این کلاس در routes.ts ساخته می‌شود:
 *    storage = new LocalStorage(db)
 */
export type SignaturePayload = {
  userId: string;
  username?: string | null;
  fullName?: string | null;
  signatureText?: string | null;
  signatureImageUrl?: string | null;
};
 
export class LocalStorage {
  constructor(private db: any) {}

  // User operations
  async verifyPassword(username: string, password: string): Promise<User | null> {
    const [user] = await this.db.select().from(users).where(eq(users.username, username));
    if (!user) return null;

    const isValid = await bcrypt.compare(password, user.password);
    return isValid ? user : null;
  }

  async getUserById(id: string): Promise<User | null> {
    const [user] = await this.db.select().from(users).where(eq(users.id, id));
    return user || null;
  }

  async getUserByUsername(username: string): Promise<User | null> {
    const [user] = await this.db.select().from(users).where(eq(users.username, username));
    return user || null;
  }

  async createUser(userData: InsertUser): Promise<User> {
    const hashedPassword = await bcrypt.hash(userData.password, 10);
    const [user] = await this.db
      .insert(users)
      .values({
        ...userData,
        password: hashedPassword,
      })
      .returning();
    return user;
  }

  async updateUser(id: string, updates: Partial<InsertUser>): Promise<User> {
    const updateData: any = { 
      ...updates, 
      updatedAt: new Date()
    };

    if (updates.password) {
      updateData.password = await bcrypt.hash(updates.password, 10);
    }

    if (updates.signatureImageUrl !== undefined) {
      updateData.signatureImageUrl = updates.signatureImageUrl;
    }

    const [user] = await this.db
      .update(users)
      .set(updateData)
      .where(eq(users.id, id))
      .returning();
    return user;
  }

  async deleteUser(id: string): Promise<void> {
    await this.db.delete(users).where(eq(users.id, id));
  }

  async getUsersByDepartment(dept: string): Promise<User[]> {
  // فقط کاربران فعال
  const allUsers = await this.db.select().from(users); // اگر ستون isActive داری می‌تونی فیلتر هم بکنی

  function parseAccess(val: unknown): string[] {
    if (Array.isArray(val)) return val as string[];
    if (typeof val === "string" && val.trim()) {
      try { return JSON.parse(val); } catch { return []; }
    }
    return [];
  }

  return allUsers.filter((u: any) => {
    const access = parseAccess(u.departmentAccess);
    return u.department === dept || access.includes(dept);
  });
}

  async getAllUsers(): Promise<User[]> {
    return await this.db.select().from(users).orderBy(desc(users.createdAt));
  }

  /**
   * ذخیره آدرس تصویر امضا در پروفایل کاربر
   * ستون: users.signatureImageUrl (DB: signature_image_url)
   */
  // ذخیره تصویر امضا در پروفایل کاربر
  async updateUserSignature(userId: string, signatureImageUrl: string): Promise<void> {
    await this.db
      .update(users)
      .set({ signatureImageUrl })
      .where(eq(users.id, userId));
  }

  // اعمال امضای دیجیتال روی یک فیلد از فرم
  async applyDigitalSignature(
    formId: string,
    fieldName: string,
    payload: SignaturePayload
  ): Promise<void> {
    const updates: Record<string, any> = {};

    // متن امضا (مثلا managerApproval)
    if (payload.signatureText && payload.signatureText.trim() !== "") {
      updates[fieldName] = payload.signatureText.trim();
    }

    // تصویر امضا -> فیلد تصویری متناظر (مثلا managerApprovalImageUrl)
    if (payload.signatureImageUrl && payload.signatureImageUrl.trim() !== "") {
      const imageFieldName = `${fieldName}ImageUrl`;
      updates[imageFieldName] = payload.signatureImageUrl.trim();
    }

    if (Object.keys(updates).length === 0) return;

    await this.db
      .update(forms)
      .set(updates)
      .where(eq(forms.id, formId));
  }
  

  // Form operations
  async createForm(formData: any): Promise<Form> {
    console.log("Storage: Creating form with data:", formData);
    
    try {
      // Ensure required fields and proper data types that match the actual database schema
      const cleanFormData = {
        formNumber: formData.formNumber,
        currentDepartment: formData.currentDepartment || 'engineering',
        status: formData.status || 'pending',
        createdBy: formData.createdBy,
        creatorName: formData.creatorName || formData.createdBy,
        productName: formData.productName || 'فرم جدید',
        indicatorNumber: formData.indicatorNumber || `IND-${Date.now().toString().slice(-6)}`,
        partsTable: Array.isArray(formData.partsTable) ? formData.partsTable : []
      };
      
      console.log("Storage: Cleaned form data for creation:", cleanFormData);
      
      const [form] = await this.db.insert(forms).values(cleanFormData).returning();
      console.log("Storage: Form created successfully:", form);
      
      // Add form history - با error handling بهتر
      try {
        await this.addFormHistory({
          formId: form.id,
          toDepartment: form.currentDepartment,
          action: 'created',
          userId: form.createdBy,
          notes: 'فرم ایجاد شد'
        });
      } catch (historyError: any) {
        console.error("Storage: Error adding form history (non-critical):", historyError);
        // Don't fail form creation if history fails
      }
      
      return form;
    } catch (error: any) {
      console.error("Storage: Error creating form:", error);
      console.error("Storage: Error details:", error?.message || 'Unknown error');
      console.error("Storage: Full error stack:", error?.stack);
      
      // More specific error information
      if (error.code) {
        console.error("Storage: Database error code:", error.code);
      }
      if (error.detail) {
        console.error("Storage: Database error detail:", error.detail);
      }
      
      throw new Error(`خطا در ایجاد فرم: ${error?.message || 'خطای ناشناخته'}`);
    }
  }

  async getForm(id: string): Promise<Form | undefined> {
    const [form] = await this.db.select().from(forms).where(eq(forms.id, id));
    return form;
  }

  async getAllForms(): Promise<Form[]> {
    return await this.db.select().from(forms).orderBy(desc(forms.createdAt));
  }

  async getFormsByDepartment(department: string): Promise<Form[]> {
    return await this.db.select().from(forms).where(eq(forms.currentDepartment, department));
  }

  async updateForm(id: string, updates: Partial<InsertForm>): Promise<Form> {
    const [form] = await this.db
      .update(forms)
      .set(updates)
      .where(eq(forms.id, id))
      .returning();
    return form;
  }

  async transferForm(formId: string, toDepartment: string, userId: string, notes?: string): Promise<Form> {
    const [form] = await this.db
      .update(forms)
      .set({ 
        currentDepartment: toDepartment,
        status: 'pending'
      })
      .where(eq(forms.id, formId))
      .returning();

    await this.addFormHistory({
      formId,
      toDepartment,
      action: 'transferred',
      userId,
      notes: notes || `انتقال به ${toDepartment}`
    });

    return form;
  }

  async approveForm(formId: string, userId: string, notes?: string): Promise<Form> {
    const [form] = await this.db
      .update(forms)
      .set({ status: 'approved' })
      .where(eq(forms.id, formId))
      .returning();

    await this.addFormHistory({
      formId,
      action: 'approved',
      userId,
      notes: notes || 'فرم تأیید شد'
    });

    return form;
  }

  async rejectForm(formId: string, userId: string, notes?: string): Promise<Form> {
    const [form] = await this.db
      .update(forms)
      .set({ status: 'rejected' })
      .where(eq(forms.id, formId))
      .returning();

    await this.addFormHistory({
      formId,
      action: 'rejected',
      userId,
      notes: notes || 'فرم رد شد'
    });

    return form;
  }

  async getArchivedForms(): Promise<any[]> {
    console.log("Querying archived forms from database...");
    const result = await this.db.select().from(forms).where(eq(forms.currentDepartment, 'archived'));
    console.log("Raw archived forms result:", result);
    return result;
  }

  async addFormHistory(historyData: InsertFormHistory): Promise<FormHistory> {
    try {
      console.log("Storage: Adding form history:", historyData);
      const [history] = await this.db.insert(formHistory).values(historyData).returning();
      console.log("Storage: Form history added successfully:", history);
      return history;
    } catch (error: any) {
      console.error("Storage: Error adding form history:", error);
      console.error("Storage: History data was:", historyData);
      throw error;
    }
  }

  async getFormHistory(formId: string): Promise<FormHistory[]> {
    return await this.db
      .select()
      .from(formHistory)
      .where(eq(formHistory.formId, formId))
      .orderBy(desc(formHistory.createdAt));
  }

  async applySignature(formId: string, userId: string, signatureImageUrl: string): Promise<Form> {
    try {
      console.log("Storage: Applying signature for form:", formId, "by user:", userId);
      const [form] = await this.db
        .update(forms)
        .set({ managerApprovalImageUrl: signatureImageUrl })
        .where(eq(forms.id, formId))
        .returning();

      await this.addFormHistory({
        formId,
        action: "signed",
        userId,
        notes: "امضا توسط کاربر ثبت شد"
      });

      return form;
    } catch (error: any) {
      console.error("Storage: Error applying signature:", error);
      throw error;
    }
  }

  async getDashboardStats(): Promise<any> {
    const totalForms = await this.db.select().from(forms);
    const pendingForms = totalForms.filter((f: any) => f.status === 'pending');
    const completedForms = totalForms.filter((f: any) => f.status === 'completed');
    const archivedForms = totalForms.filter((f: any) => f.currentDepartment === 'archived');

    return {
      totalForms: totalForms.length,
      pendingForms: pendingForms.length,
      completedForms: completedForms.length,
      archivedForms: archivedForms.length
    };
  }
}