import { query, queryOne } from "../database/db";

/**
 * 템플릿 표준 관리 서비스
 */
export class TemplateStandardService {
  /**
   * 템플릿 목록 조회
   */
  async getTemplates(params: {
    active?: string;
    category?: string;
    search?: string;
    company_code?: string;
    is_public?: string;
    page?: number;
    limit?: number;
  }) {
    const {
      active = "Y",
      category,
      search,
      company_code,
      is_public = "Y",
      page = 1,
      limit = 50,
    } = params;

    const skip = (page - 1) * limit;

    // 동적 WHERE 조건 생성
    const conditions: string[] = [];
    const values: any[] = [];
    let paramIndex = 1;

    if (active && active !== "all") {
      conditions.push(`is_active = $${paramIndex++}`);
      values.push(active);
    }

    if (category && category !== "all") {
      conditions.push(`category = $${paramIndex++}`);
      values.push(category);
    }

    if (search) {
      conditions.push(
        `(template_name ILIKE $${paramIndex} OR template_name_eng ILIKE $${paramIndex} OR description ILIKE $${paramIndex})`
      );
      values.push(`%${search}%`);
      paramIndex++;
    }

    // 회사별 필터링
    if (company_code) {
      conditions.push(`(is_public = 'Y' OR company_code = $${paramIndex++})`);
      values.push(company_code);
    } else if (is_public === "Y") {
      conditions.push(`is_public = $${paramIndex++}`);
      values.push("Y");
    }

    const whereClause =
      conditions.length > 0 ? `WHERE ${conditions.join(" AND ")}` : "";

    const [templates, totalResult] = await Promise.all([
      query<any>(
        `SELECT * FROM template_standards 
         ${whereClause}
         ORDER BY sort_order ASC, template_name ASC
         LIMIT $${paramIndex} OFFSET $${paramIndex + 1}`,
        [...values, limit, skip]
      ),
      queryOne<{ count: string }>(
        `SELECT COUNT(*) as count FROM template_standards ${whereClause}`,
        values
      ),
    ]);

    const total = parseInt(totalResult?.count || "0");

    return { templates, total };
  }

  /**
   * 템플릿 상세 조회
   */
  async getTemplate(templateCode: string) {
    return await queryOne<any>(
      `SELECT * FROM template_standards WHERE template_code = $1`,
      [templateCode]
    );
  }

  /**
   * 템플릿 생성
   */
  async createTemplate(templateData: any) {
    // 템플릿 코드 중복 확인
    const existing = await queryOne<any>(
      `SELECT * FROM template_standards WHERE template_code = $1`,
      [templateData.template_code]
    );

    if (existing) {
      throw new Error(
        `템플릿 코드 '${templateData.template_code}'는 이미 존재합니다.`
      );
    }

    return await queryOne<any>(
      `INSERT INTO template_standards 
       (template_code, template_name, template_name_eng, description, category, 
        icon_name, default_size, layout_config, preview_image, sort_order, 
        is_active, is_public, company_code, created_by, updated_by, created_at, updated_at)
       VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, NOW(), NOW())
       RETURNING *`,
      [
        templateData.template_code,
        templateData.template_name,
        templateData.template_name_eng,
        templateData.description,
        templateData.category,
        templateData.icon_name,
        templateData.default_size,
        templateData.layout_config,
        templateData.preview_image,
        templateData.sort_order || 0,
        templateData.is_active || "Y",
        templateData.is_public || "N",
        templateData.company_code,
        templateData.created_by,
        templateData.updated_by,
      ]
    );
  }

  /**
   * 템플릿 수정
   */
  async updateTemplate(templateCode: string, templateData: any) {
    // 동적 UPDATE 쿼리 생성
    const updateFields: string[] = ["updated_at = NOW()"];
    const values: any[] = [];
    let paramIndex = 1;

    if (templateData.template_name !== undefined) {
      updateFields.push(`template_name = $${paramIndex++}`);
      values.push(templateData.template_name);
    }
    if (templateData.template_name_eng !== undefined) {
      updateFields.push(`template_name_eng = $${paramIndex++}`);
      values.push(templateData.template_name_eng);
    }
    if (templateData.description !== undefined) {
      updateFields.push(`description = $${paramIndex++}`);
      values.push(templateData.description);
    }
    if (templateData.category !== undefined) {
      updateFields.push(`category = $${paramIndex++}`);
      values.push(templateData.category);
    }
    if (templateData.icon_name !== undefined) {
      updateFields.push(`icon_name = $${paramIndex++}`);
      values.push(templateData.icon_name);
    }
    if (templateData.default_size !== undefined) {
      updateFields.push(`default_size = $${paramIndex++}`);
      values.push(templateData.default_size);
    }
    if (templateData.layout_config !== undefined) {
      updateFields.push(`layout_config = $${paramIndex++}`);
      values.push(templateData.layout_config);
    }
    if (templateData.preview_image !== undefined) {
      updateFields.push(`preview_image = $${paramIndex++}`);
      values.push(templateData.preview_image);
    }
    if (templateData.sort_order !== undefined) {
      updateFields.push(`sort_order = $${paramIndex++}`);
      values.push(templateData.sort_order);
    }
    if (templateData.is_active !== undefined) {
      updateFields.push(`is_active = $${paramIndex++}`);
      values.push(templateData.is_active);
    }
    if (templateData.is_public !== undefined) {
      updateFields.push(`is_public = $${paramIndex++}`);
      values.push(templateData.is_public);
    }
    if (templateData.updated_by !== undefined) {
      updateFields.push(`updated_by = $${paramIndex++}`);
      values.push(templateData.updated_by);
    }

    try {
      return await queryOne<any>(
        `UPDATE template_standards 
         SET ${updateFields.join(", ")}
         WHERE template_code = $${paramIndex}
         RETURNING *`,
        [...values, templateCode]
      );
    } catch (error: any) {
      return null; // 템플릿을 찾을 수 없음
    }
  }

  /**
   * 템플릿 삭제
   */
  async deleteTemplate(templateCode: string) {
    try {
      await query(`DELETE FROM template_standards WHERE template_code = $1`, [
        templateCode,
      ]);
      return true;
    } catch (error: any) {
      return false; // 템플릿을 찾을 수 없음
    }
  }

  /**
   * 템플릿 정렬 순서 일괄 업데이트
   */
  async updateSortOrder(
    templates: { template_code: string; sort_order: number }[]
  ) {
    const updatePromises = templates.map((template) =>
      query(
        `UPDATE template_standards 
         SET sort_order = $1, updated_at = NOW()
         WHERE template_code = $2`,
        [template.sort_order, template.template_code]
      )
    );

    await Promise.all(updatePromises);
  }

  /**
   * 템플릿 복제
   */
  async duplicateTemplate(params: {
    originalCode: string;
    newCode: string;
    newName: string;
    company_code: string;
    created_by: string;
  }) {
    const { originalCode, newCode, newName, company_code, created_by } = params;

    // 원본 템플릿 조회
    const originalTemplate = await this.getTemplate(originalCode);
    if (!originalTemplate) {
      throw new Error("원본 템플릿을 찾을 수 없습니다.");
    }

    // 새 템플릿 코드 중복 확인
    const existing = await this.getTemplate(newCode);
    if (existing) {
      throw new Error(`템플릿 코드 '${newCode}'는 이미 존재합니다.`);
    }

    // 템플릿 복제
    return await this.createTemplate({
      template_code: newCode,
      template_name: newName,
      template_name_eng: originalTemplate.template_name_eng
        ? `${originalTemplate.template_name_eng} (Copy)`
        : undefined,
      description: originalTemplate.description,
      category: originalTemplate.category,
      icon_name: originalTemplate.icon_name,
      default_size: originalTemplate.default_size,
      layout_config: originalTemplate.layout_config,
      preview_image: originalTemplate.preview_image,
      sort_order: 0,
      is_active: "Y",
      is_public: "N", // 복제된 템플릿은 기본적으로 비공개
      company_code,
      created_by,
      updated_by: created_by,
    });
  }

  /**
   * 템플릿 카테고리 목록 조회
   */
  async getCategories(companyCode: string) {
    const categories = await query<{ category: string }>(
      `SELECT DISTINCT category 
       FROM template_standards 
       WHERE (is_public = $1 OR company_code = $2) 
         AND is_active = $3
       ORDER BY category ASC`,
      ["Y", companyCode, "Y"]
    );

    return categories.map((item) => item.category).filter(Boolean);
  }

  /**
   * 기본 템플릿 데이터 삽입 (초기 설정용)
   */
  async seedDefaultTemplates() {
    const defaultTemplates = [
      {
        template_code: "advanced-data-table",
        template_name: "고급 데이터 테이블",
        template_name_eng: "Advanced Data Table",
        description:
          "컬럼 설정, 필터링, 페이지네이션이 포함된 완전한 데이터 테이블",
        category: "table",
        icon_name: "table",
        default_size: { width: 1000, height: 680 },
        layout_config: {
          components: [
            {
              type: "datatable",
              label: "데이터 테이블",
              position: { x: 0, y: 0 },
              size: { width: 1000, height: 680 },
              style: {
                border: "1px solid #e5e7eb",
                borderRadius: "8px",
                backgroundColor: "#ffffff",
                padding: "16px",
              },
            },
          ],
        },
        sort_order: 1,
        is_active: "Y",
        is_public: "Y",
        company_code: "*",
        created_by: "system",
        updated_by: "system",
      },
      {
        template_code: "universal-button",
        template_name: "버튼",
        template_name_eng: "Universal Button",
        description:
          "다양한 기능을 설정할 수 있는 범용 버튼. 상세설정에서 기능을 선택하세요.",
        category: "button",
        icon_name: "mouse-pointer",
        default_size: { width: 80, height: 36 },
        layout_config: {
          components: [
            {
              type: "widget",
              widgetType: "button",
              label: "버튼",
              position: { x: 0, y: 0 },
              size: { width: 80, height: 36 },
              style: {
                backgroundColor: "#3b82f6",
                color: "#ffffff",
                border: "none",
                borderRadius: "6px",
                fontSize: "14px",
                fontWeight: "500",
              },
            },
          ],
        },
        sort_order: 2,
        is_active: "Y",
        is_public: "Y",
        company_code: "*",
        created_by: "system",
        updated_by: "system",
      },
      {
        template_code: "file-upload",
        template_name: "파일 첨부",
        template_name_eng: "File Upload",
        description: "드래그앤드롭 파일 업로드 영역",
        category: "file",
        icon_name: "upload",
        default_size: { width: 300, height: 120 },
        layout_config: {
          components: [
            {
              type: "widget",
              widgetType: "file",
              label: "파일 첨부",
              position: { x: 0, y: 0 },
              size: { width: 300, height: 120 },
              style: {
                border: "2px dashed #d1d5db",
                borderRadius: "8px",
                backgroundColor: "#f9fafb",
                display: "flex",
                alignItems: "center",
                justifyContent: "center",
                fontSize: "14px",
                color: "#6b7280",
              },
            },
          ],
        },
        sort_order: 3,
        is_active: "Y",
        is_public: "Y",
        company_code: "*",
        created_by: "system",
        updated_by: "system",
      },
    ];

    // 기존 데이터가 있는지 확인 후 삽입
    for (const template of defaultTemplates) {
      const existing = await this.getTemplate(template.template_code);
      if (!existing) {
        await this.createTemplate(template);
      }
    }
  }
}

export const templateStandardService = new TemplateStandardService();
