Function bodies 601 total

  async generatePessimistPosition(data: {
    topic: string;
    contextData: unknown;
    domain: string;
    thesis: {
      position: string;
      reasoning: string;
      evidence: string[];
      confidence: number;
    };
    catsCommand: {
      context: string;
      task: string;
      successCriteria: string;
    };
  }): Promise<{
    position: string;
    reasoning: string;
    evidence: string[];
    confidence: number;
    suggestedActions: string[];
  }> {

  async generateMediatorSynthesis(data: {
    topic: string;
    contextData: unknown;
    domain: string;
    thesis: {
      position: string;
      reasoning: string;
      evidence: string[];
      confidence: number;
    };
    antithesis: {
      position: string;
      reasoning: string;
      evidence: string[];
      confidence: number;
    };
    catsCommand: {
      context: string;
      task: string;
      successCriteria: string;
    };
  }): Promise<{
    position: string;
    reasoning: string;
    evidence: string[];
    confidence: number;
    suggestedActions: string[];
    dissent?: string;
  }> {

  async performQAReview(debate: {
    topic: string;
    domain: string;
    thesis?: { position: string; reasoning: string; confidence: number };
    antithesis?: { position: string; reasoning: string; confidence: number };
    synthesis?: { position: string; reasoning: string; suggestedActions?: string[] };
  }): Promise<{
    approved: boolean;
    score: number;
    issues: string[];
    recommendations: string[];
  }> {

  async performComplianceReview(debate: {
    topic: string;
    domain: string;
    fullContent: string;
  }): Promise<{
    approved: boolean;
    score: number;
    violations: string[];
    recommendations: string[];
  }> {

  private parseDebateResponse(
    text: string,
    role: 'optimist' | 'pessimist' | 'mediator'
  ): {
    position: string;
    reasoning: string;
    evidence: string[];
    confidence: number;
    suggestedActions: string[];
    dissent?: string;
  } {

  private getDefaultPosition(role: 'optimist' | 'pessimist' | 'mediator'): string {
    const defaults = {
      optimist: '기회 요인이 발견되어 추가 검토가 권장됩니다.',
      pessimist: '주의가 필요한 리스크 요인이 있습니다.',
      mediator: '양측 의견을 종합하여 균형 잡힌 접근이 필요합니다.',
    };
    return defaults[role];
  }

  private async getAuthClient(): Promise<sheets_v4.Sheets> {
    if (this.sheetsClient) return this.sheetsClient;

    // Method 1: GOOGLE_SERVICE_ACCOUNT_JSON env var
    const serviceAccountJson = process.env.GOOGLE_SERVICE_ACCOUNT_JSON;
    if (serviceAccountJson) {
      const credentials = JSON.parse(serviceAccountJson);
      const auth = new google.auth.GoogleAuth({
        credentials,
        scopes: ['https://www.googleapis.com/auth/spreadsheets.readonly'],
      });
      this.sheetsClient = google.sheets({ version: 'v4', auth: await auth.getClient() as any });
      return this.sheetsClient;
    }

    // Method 2: Service account key file
    const keyPath = process.env.GOOGLE_SERVICE_ACCOUNT_KEY_PATH;
    if (keyPath) {
      const resolved = path.resolve(keyPath);
      if (fs.existsSync(resolved)) {
        const credentials = JSON.parse(fs.readFileSync(resolved, 'utf-8'));
        const auth = new google.auth.GoogleAuth({
          credentials,
          scopes: ['http

  private async fetchSheetByApi(spreadsheetId: string, sheetName: string): Promise<string[][]> {
    try {
      const client = await this.getAuthClient();
      const response = await client.spreadsheets.values.get({
        spreadsheetId,
        range: `'${sheetName}'!A:Z`,
      });
      const rows = response.data.values;
      if (!rows || rows.length === 0) return [];
      return rows.map(row => row.map(cell => String(cell ?? '')));
    } catch (error) {
      console.error(`Failed to fetch sheet "${sheetName}" via API:`, error);
      return [];
    }
  }

  private parseCSV(csv: string): string[][] {
    const lines: string[][] = [];
    let currentLine: string[] = [];
    let currentField = '';
    let inQuotes = false;

    for (let i = 0; i < csv.length; i++) {
      const char = csv[i];
      const nextChar = csv[i + 1];

      if (inQuotes) {
        if (char === '"' && nextChar === '"') {
          currentField += '"';
          i++; // Skip next quote
        } else if (char === '"') {
          inQuotes = false;
        } else {
          currentField += char;
        }
      } else {
        if (char === '"') {
          inQuotes = true;
        } else if (char === ',') {
          currentLine.push(currentField.trim());
          currentField = '';
        } else if (char === '\n' || (char === '\r' && nextChar === '\n')) {
          currentLine.push(currentField.trim());
          if (currentLine.some(f => f !== '')) {
            lines.push(currentLine);
          }
          currentLine = [];
          currentField = '';
    

  private parseNumber(value: string): number {
    if (!value || value.trim() === '' || value.trim() === '-') return 0;
    const cleaned = value.replace(/,/g, '').replace(/\s/g, '');
    const num = parseFloat(cleaned);
    return isNaN(num) ? 0 : num;
  }

  private parseDate(value: string): string {
    if (!value || value.trim() === '') return '';
    const s = value.trim().replace(/\//g, '-');
    const m = s.match(/^(\d{4})-(\d{1,2})-(\d{1,2})/);
    if (!m) return s;
    return `${m[1]}-${m[2].padStart(2, '0')}-${m[3].padStart(2, '0')}`;
  }

  private async fetchSheetByName(sheetName: string, spreadsheetId?: string): Promise<string[][]> {
    const baseUrl = spreadsheetId
      ? `https://docs.google.com/spreadsheets/d/${spreadsheetId}/gviz/tq?tqx=out:csv`
      : this.baseUrl;
    const url = `${baseUrl}&sheet=${encodeURIComponent(sheetName)}`;

    try {
      const response = await fetch(url, { headers: { Accept: 'text/csv' } });
      if (!response.ok) {
        throw new Error(`HTTP ${response.status}: ${response.statusText}`);
      }
      const csv = await response.text();
      return this.parseCSV(csv);
    } catch (error) {
      console.error(`Failed to fetch sheet "${sheetName}":`, error);
      return [];
    }
  }

  private async fetchSheet(gid: number): Promise<string[][]> {
    const url = `${this.baseUrl}&gid=${gid}`;

    try {
      const response = await fetch(url, {
        headers: {
          Accept: 'text/csv',
        },
      });

      if (!response.ok) {
        throw new Error(`HTTP ${response.status}: ${response.statusText}`);
      }

      const csv = await response.text();
      return this.parseCSV(csv);
    } catch (error) {
      console.error(`Failed to fetch sheet gid=${gid}:`, error);
      return [];
    }
  }

  async fetchDailySales(): Promise<DailySalesData[]> {
    const rows = await this.fetchSheet(SHEET_GIDS.dailySales);
    const results: DailySalesData[] = [];

    // 첫 번째 행은 헤더, 데이터는 2행부터
    // 빈 행과 합계 행 제외
    for (let i = 1; i < rows.length; i++) {
      const row = rows[i];
      const date = this.parseDate(row[0]);

      // 날짜가 없는 행(합계 행 등) 제외
      if (!date || !date.match(/^\d{4}-\d{2}-\d{2}$/)) continue;

      results.push({
        date,
        jasaPrice: this.parseNumber(row[1]),
        coupangPrice: this.parseNumber(row[2]),
        kurlyPrice: this.parseNumber(row[3]),
        totalRevenue: this.parseNumber(row[4]),
        frozenSoup: this.parseNumber(row[5]),
        etc: this.parseNumber(row[6]),
        bibimbap: this.parseNumber(row[7]),
        jasaHalf: this.parseNumber(row[8]),
        coupangHalf: this.parseNumber(row[9]),
        kurlyHalf: this.parseNumber(row[10]),
        frozenHalf: this.parseNumber(row[11]),
        etcHalf: this.parseNumber(row[12]),
 

  async fetchSalesDetail(): Promise<SalesDetailData[]> {
    const rows = await this.fetchSheetByName('판매');
    if (rows.length < 2) return [];

    // 헤더 행에서 컬럼 인덱스 자동 감지
    const header = rows[0].map(h => (h || '').trim());
    const col = {
      date: header.findIndex(h => h.includes('일별') || h === '날짜'),
      customer: header.findIndex(h => h.includes('거래처')),
      productName: header.findIndex(h => h === '품목별' || h === '품목명'),
      productCode: header.findIndex(h => h.includes('품목코드')),
      quantity: header.findIndex(h => h === '수량'),
      supplyAmount: header.findIndex(h => h.includes('공급가액')),
      vat: header.findIndex(h => h.includes('부가세')),
      total: header.findIndex(h => h === '합계'),
      recommendedRevenue: header.findIndex(h => h.includes('권장판매')),
    };

    // 필수 컬럼 검증
    if (col.date < 0 || col.supplyAmount < 0) {
      console.error('[GoogleSheetAdapter] 판매 시트 헤더 매핑 실패:', header);
      return [];
    }

    console.log('[GoogleSheetAdapter] 판매 시트 컬럼 매

  async fetchProduction(): Promise<ProductionData[]> {
    const rows = await this.fetchSheet(SHEET_GIDS.production);
    const results: ProductionData[] = [];

    // 복잡한 헤더 구조 - 실제 데이터는 컬럼 인덱스로 매핑
    // 헤더 생산일, 생산수량(EA) 일반반찬, 전전처리, 냉동국, 소스, 비빔밥, 합계,
    // 생산량(KG) 일반반찬, 전전처리, 냉동국, 소스, 합계,
    // 완제품폐기량(EA), 비율(%), 반제품폐기량(KG), 반제품비율
    for (let i = 1; i < rows.length; i++) {
      const row = rows[i];
      const date = this.parseDate(row[0]);

      if (!date || !date.match(/^\d{4}-\d{2}-\d{2}$/)) continue;

      results.push({
        date,
        prodQtyNormal: this.parseNumber(row[1]),
        prodQtyPreprocess: this.parseNumber(row[2]),
        prodQtyFrozen: this.parseNumber(row[3]),
        prodQtySauce: this.parseNumber(row[4]),
        prodQtyBibimbap: this.parseNumber(row[5]),
        prodQtyTotal: this.parseNumber(row[6]),
        prodKgNormal: this.parseNumber(row[7]),
        prodKgPreprocess: this.parseNumber(row[8]),
        prodKgFrozen: this.parseNumber(row[9]),
 

  async fetchPurchases(): Promise<PurchaseData[]> {
    const rows = await this.fetchSheet(SHEET_GIDS.purchases);
    const results: PurchaseData[] = [];

    for (let i = 1; i < rows.length; i++) {
      const row = rows[i];
      const rawDate = (row[0] || '').trim();

      // YYYY/MM/DD-N 또는 YYYY/MM/DD 형식에서 날짜 부분만 추출
      const dateMatch = rawDate.match(/^(\d{4}\/\d{2}\/\d{2})/);
      if (!dateMatch) continue;

      const date = dateMatch[1].replace(/\//g, '-');

      results.push({
        date,
        productName: row[1] || '',
        productCode: row[2] || '',
        quantity: this.parseNumber(row[3]),
        unitPrice: this.parseNumber(row[4]),
        supplyAmount: this.parseNumber(row[5]),
        vat: this.parseNumber(row[6]),
        total: this.parseNumber(row[7]),
        supplierName: row[8] || '',
      });
    }

    return results;
  }

  async fetchUtilities(): Promise<UtilityData[]> {
    const rows = await this.fetchSheet(SHEET_GIDS.utilities);
    const results: UtilityData[] = [];

    // 복잡한 헤더 - 컬럼 인덱스로 매핑
    // 날짜, 전기(전일검침, 당일검침, 공백, 공백, 공백, 일일사용량, 사용금액),
    // 수도(전일검침, 당일검침, 일일사용량, 사용금액),
    // 가스(전일검침, 당일검침, 일일사용량, 사용금액)
    for (let i = 2; i < rows.length; i++) {
      // 헤더가 2행에 걸쳐있을 수 있음
      const row = rows[i];
      const date = this.parseDate(row[0]);

      if (!date || !date.match(/^\d{4}-\d{2}-\d{2}$/)) continue;

      results.push({
        date,
        elecPrev: this.parseNumber(row[1]),
        elecCurr: this.parseNumber(row[2]),
        elecUsage: this.parseNumber(row[6]),
        elecCost: this.parseNumber(row[7]),
        waterPrev: this.parseNumber(row[8]),
        waterCurr: this.parseNumber(row[9]),
        waterUsage: this.parseNumber(row[10]),
        waterCost: this.parseNumber(row[11]),
        gasPrev: this.parseNumber(row[12]),
        gasCurr: this.parseNumber(row[13]),
      

  async fetchLabor(): Promise<LaborData[]> {
    const rows = await this.fetchSheetByName('노무비');
    const results: LaborData[] = [];

    // CSV 첫 행이 헤더, 데이터는 2행부터
    for (let i = 1; i < rows.length; i++) {
      const row = rows[i];
      if (!row[0] || !row[1]) continue;

      // 날짜 정규화: "2026. 1. 1." → "2026-01-01"
      const rawDate = (row[1] || '').trim();
      const dateMatch = rawDate.match(/(\d{4})\.\s*(\d{1,2})\.\s*(\d{1,2})/);
      if (!dateMatch) continue;

      const date = `${dateMatch[1]}-${dateMatch[2].padStart(2, '0')}-${dateMatch[3].padStart(2, '0')}`;
      const department = (row[2] || '').trim();
      if (!department) continue;

      results.push({
        week: row[0] || '',
        date,
        department,
        headcount: this.parseNumber(row[3]),
        weekdayRegularHours: this.parseNumber(row[4]),
        weekdayOvertimeHours: this.parseNumber(row[5]),
        weekdayNightHours: this.parseNumber(row[6]),
        weekdayTotalHours: this.parseNumbe

  async fetchBom(sheetName: string, source: 'SAN' | 'ZIP'): Promise<BomSheetData[]> {
    const rows = await this.fetchSheetByApi(getBomSpreadsheetId(), sheetName);
    const results: BomSheetData[] = [];

    // 헤더가 2행, 데이터는 3행부터 (CSV 인덱스 기준 1부터 시작이므로 i=2)
    for (let i = 2; i < rows.length; i++) {
      const row = rows[i];
      const productCode = (row[0] || '').trim();
      if (!productCode) continue; // 빈 productCode 행 스킵

      results.push({
        source,
        productCode,
        productName: (row[1] || '').trim(),
        bomVersion: (row[2] || '').trim(),
        isExistingBom: (row[3] || '').trim() === 'Y' || (row[3] || '').trim() === '기존',
        productionQty: this.parseNumber(row[4]),
        materialCode: (row[5] || '').trim(),
        materialName: (row[6] || '').trim(),
        materialBomVersion: (row[7] || '').trim(),
        consumptionQty: this.parseNumber(row[8]),
        location: (row[9] || '').trim(),
        remark: (row[10] || '').trim(),
        add

  async fetchMaterialMaster(): Promise<MaterialMasterData[]> {
    const rows = await this.fetchSheetByName('1.자재정보', getBomSpreadsheetId());
    const results: MaterialMasterData[] = [];

    // 헤더가 2행, 데이터는 3행부터 (CSV 인덱스 기준 i=2)
    for (let i = 2; i < rows.length; i++) {
      const row = rows[i];
      const materialCode = (row[3] || '').trim();
      if (!materialCode) continue; // 빈 자재코드 행 스킵

      results.push({
        no: this.parseNumber(row[0]),
        category: (row[1] || '').trim(),
        issueType: (row[2] || '').trim(),
        materialCode,
        materialName: (row[4] || '').trim(),
        preprocessYield: this.parseNumber(row[5]),
        spec: (row[6] || '').trim(),
        unit: (row[7] || '').trim(),
        unitPrice: this.parseNumber(row[8]),
        safetyStock: this.parseNumber(row[9]),
        excessStock: this.parseNumber(row[10]),
        leadTimeDays: Math.round(this.parseNumber(row[11])),
        recentOutputQty: this.parseNumber(row[12]),
        da

  async syncAllData() {
    console.log('Syncing Google Sheet data...');

    const [dailySales, salesDetail, production, purchases, utilities, labor, sanBom, zipBom] = await Promise.all([
      this.fetchDailySales(),
      this.fetchSalesDetail(),
      this.fetchProduction(),
      this.fetchPurchases(),
      this.fetchUtilities(),
      this.fetchLabor(),
      this.fetchBom('3. SAN_BOM', 'SAN'),
      this.fetchBom('4. ZIP_BOM', 'ZIP'),
    ]);

    console.log('Google Sheet sync complete:', {
      dailySales: dailySales.length,
      salesDetail: salesDetail.length,
      production: production.length,
      purchases: purchases.length,
      utilities: utilities.length,
      labor: labor.length,
      sanBom: sanBom.length,
      zipBom: zipBom.length,
    });

    return {
      dailySales,
      salesDetail,
      production,
      purchases,
      utilities,
      labor,
      sanBom,
      zipBom,
      syncedAt: new Date().toISOString(),
    };
  }

export class GoogleSheetsAdapter {
  private sheets: sheets_v4.Sheets | null = null;
  private spreadsheetId: string;
  private credentials: any = null;

  constructor(spreadsheetId: string = '1GUo9wmwmm14zhb_gtpoNrDBfJ5DqEm5pf4jpRsto-JI') {
    this.spreadsheetId = spreadsheetId;
  }

  setCredentials(credentials: any): void {
    this.credentials = credentials;
    this.sheets = null; // Reset to reconnect with new credentials
  }

  private async getClient(): Promise<sheets_v4.Sheets> {
    if (this.sheets) return this.sheets;

    // Method 0: Try GOOGLE_SERVICE_ACCOUNT_JSON env var (for cloud deployments)
    const serviceAccountJson = process.env.GOOGLE_SERVICE_ACCOUNT_JSON;
    if (serviceAccountJson) {
      try {
        const credentials = JSON.parse(serviceAccountJson);
        const auth = new google.auth.GoogleAuth({
          credentials,
          scopes: ['https://www.googleapis.com/auth/spreadsheets'],
        });

        const authClient = await auth.getClient();
   

  constructor(spreadsheetId: string = '1GUo9wmwmm14zhb_gtpoNrDBfJ5DqEm5pf4jpRsto-JI') {
    this.spreadsheetId = spreadsheetId;
  }

  setCredentials(credentials: any): void {
    this.credentials = credentials;
    this.sheets = null; // Reset to reconnect with new credentials
  }

  private async getClient(): Promise<sheets_v4.Sheets> {
    if (this.sheets) return this.sheets;

    // Method 0: Try GOOGLE_SERVICE_ACCOUNT_JSON env var (for cloud deployments)
    const serviceAccountJson = process.env.GOOGLE_SERVICE_ACCOUNT_JSON;
    if (serviceAccountJson) {
      try {
        const credentials = JSON.parse(serviceAccountJson);
        const auth = new google.auth.GoogleAuth({
          credentials,
          scopes: ['https://www.googleapis.com/auth/spreadsheets'],
        });

        const authClient = await auth.getClient();
        this.sheets = google.sheets({
          version: 'v4',
          auth: authClient as any,
        });
        console.log('Google Sheets connected via GOOGLE_SERVICE_ACCOUNT_JSON env var');
        return this.sheets;
      } catch (error: any) {
        console.error('Error parsing GOOGLE_SERVICE_ACCOUNT_JSON:', error.message);
      }
    }

    // Method 1: Try service account JSON key file first
    const serviceAccountPath =

  async fetchSheetData(sheetName: string): Promise<SheetRow[]> {
    try {
      const client = await this.getClient();

      const response = await client.spreadsheets.values.get({
        spreadsheetId: this.spreadsheetId,
        range: `${sheetName}!A:Z`, // Fetch all columns
      });

      const rows = response.data.values;
      if (!rows || rows.length === 0) {
        console.log(`No data found in sheet: ${sheetName}`);
        return [];
      }

      // First row is headers
      const headers = rows[0] as string[];
      const dataRows = rows.slice(1);

      return dataRows.map(row => {
        const obj: SheetRow = {};
        headers.forEach((header, idx) => {
          const value = row[idx] || '';
          // Try to parse as number
          const numValue = parseFloat(value.toString().replace(/,/g, ''));
          obj[header] = isNaN(numValue) ? value : numValue;
        });
        return obj;
      });
    } catch (error: any) {
      console.error(`Error fetchi

  async fetchAllCostData(): Promise<CostReportData> {
    console.log('Fetching cost data from Google Sheets...');

    const [sales, expenses, labor, waste] = await Promise.all([
      this.fetchSheetData('DB_매출'),
      this.fetchSheetData('DB_경비'),
      this.fetchSheetData('DB_노무비'),
      this.fetchSheetData('DB_폐기'),
    ]);

    console.log(
      `Fetched: 매출 ${sales.length}, 경비 ${expenses.length}, 노무비 ${labor.length}, 폐기 ${waste.length} rows`
    );

    return { sales, expenses, labor, waste };
  }

  calculateMonthlySummary(data: CostReportData): MonthlyCostSummary[] {
    const monthlyData = new Map<string, MonthlyCostSummary>();

    // Helper to extract month from date field
    const getMonth = (row: SheetRow): string | null => {
      // Try common date field names
      const dateFields = ['날짜', '일자', 'date', 'Date', '년월', '월'];
      for (const field of dateFields) {
        if (row[field]) {
          const dateStr = row[field].toString();
          // Try to extract YYYY-MM format
          const match = dateStr.match(/(\d{4})[-\/.]?(\d{2})/);
          if (match) {
            return `${match[1]}-${match[2]}`;
          }
        }
      }
      return null;
    };

    // Helper to get amount from row
    const getAmount = (row: SheetRow): number => {
      const amountFields = ['금액', '매출액', '비용', '원가', 'amount', 'Amount', '합계'];
      for (const field of amountFields) {
        if (row[field] !== undefined) {
          const val = row[field];
          return typeof v

export class MultiSpreadsheetAdapter {
  private baseAdapter: GoogleSheetsAdapter;
  private _salesPurchaseSpreadsheetId: string | null = null;
  private _bomSpreadsheetId: string | null = null;

  constructor() {
    this.baseAdapter = new GoogleSheetsAdapter();
  }

  // Lazy load env vars (in case dotenv hasn't run yet at module load time)
  private get salesPurchaseSpreadsheetId(): string {
    if (this._salesPurchaseSpreadsheetId === null) {
      this._salesPurchaseSpreadsheetId = process.env.GOOGLE_SALES_PURCHASE_SPREADSHEET_ID || '';
    }
    return this._salesPurchaseSpreadsheetId;
  }

  private get bomSpreadsheetId(): string {
    if (this._bomSpreadsheetId === null) {
      this._bomSpreadsheetId = process.env.GOOGLE_BOM_SPREADSHEET_ID || '';
    }
    return this._bomSpreadsheetId;
  }

  private async getClient(): Promise<sheets_v4.Sheets> {
    // Reuse the base adapter's client initialization logic
    return (this.baseAdapter as any).getClient();
  }

  async fetchShe

  constructor() {
    this.baseAdapter = new GoogleSheetsAdapter();
  }

  private async getClient(): Promise<sheets_v4.Sheets> {
    // Reuse the base adapter's client initialization logic
    return (this.baseAdapter as any).getClient();
  }

  async fetchSheetData(spreadsheetId: string, sheetName: string): Promise<SheetRow[]> {
    try {
      const client = await this.getClient();

      const response = await client.spreadsheets.values.get({
        spreadsheetId: spreadsheetId,
        range: `${sheetName}!A:Z`,
      });

      const rows = response.data.values;
      if (!rows || rows.length === 0) {
        console.log(`No data found in sheet: ${sheetName} (spreadsheet: ${spreadsheetId})`);
        return [];
      }

      const headers = rows[0] as string[];
      const dataRows = rows.slice(1);

      return dataRows.map(row => {
        const obj: SheetRow = {};
        headers.forEach((header, idx) => {
          const value = row[idx] || '';
          const numValue = parseFloat(value.toString().replace(/,/g, ''));
          obj[header] = isNaN(numValue) ? value : numValue;
        });
        return obj;
      });
    } catch (error: any) {
      console.error(`Error fetching sheet ${sheetName}:`, error.messag

  async listSheets(spreadsheetId: string): Promise<string[]> {
    try {
      const client = await this.getClient();
      const response = await client.spreadsheets.get({ spreadsheetId });
      return response.data.sheets?.map(s => s.properties?.title || '') || [];
    } catch (error: any) {
      console.error(`Error listing sheets:`, error.message);
      return [];
    }
  }

  async fetchSalesTransactions(): Promise<SalesTransaction[]> {
    if (!this.salesPurchaseSpreadsheetId) {
      console.warn('Sales/Purchase spreadsheet ID not configured');
      return [];
    }

    // Try different sheet names for sales data
    const possibleSheetNames = ['판매현황', '판매', 'Sales', 'DB_판매', '매출'];
    const sheets = await this.listSheets(this.salesPurchaseSpreadsheetId);
    console.log(`[MultiSpreadsheetAdapter] 판매/구매 시트 목록: ${sheets.join(', ')}`);

    let salesSheet = sheets.find(s => possibleSheetNames.some(n => s.includes(n)));
    if (!salesSheet && sheets.length > 0) {
      // Use first sheet if no match found
      salesSheet = sheets[0];
    }

    if (!salesSheet) {
      console.warn('No sales sheet found');
      return [];
    }

    console.log(`[MultiSpreadsheetAdapter] 판매 시트 사용: ${salesSheet}`);
    const rawData = await this.fetchSheetData(this.salesPurchaseSpreadsheetId, salesSheet);

    return rawData.map(row => ({
      date: String(row['일자'] |

  async fetchPurchaseTransactions(): Promise<PurchaseTransaction[]> {
    if (!this.salesPurchaseSpreadsheetId) {
      console.warn('Sales/Purchase spreadsheet ID not configured');
      return [];
    }

    const possibleSheetNames = ['구매현황', '구매', 'Purchase', 'DB_구매', '매입'];
    const sheets = await this.listSheets(this.salesPurchaseSpreadsheetId);

    let purchaseSheet = sheets.find(s => possibleSheetNames.some(n => s.includes(n)));
    if (!purchaseSheet) {
      // Try second sheet
      purchaseSheet = sheets[1] || sheets[0];
    }

    if (!purchaseSheet) {
      console.warn('No purchase sheet found');
      return [];
    }

    console.log(`[MultiSpreadsheetAdapter] 구매 시트 사용: ${purchaseSheet}`);
    const rawData = await this.fetchSheetData(this.salesPurchaseSpreadsheetId, purchaseSheet);

    return rawData.map(row => ({
      date: String(row['일자'] || row['날짜'] || row['date'] || ''),
      supplierCode: String(row['거래처코드'] || row['거래처 코드'] || ''),
      supplierName: Str

  async fetchBomData(): Promise<BomItem[]> {
    if (!this.bomSpreadsheetId) {
      console.warn('BOM spreadsheet ID not configured');
      return [];
    }

    const sheets = await this.listSheets(this.bomSpreadsheetId);
    console.log(`[MultiSpreadsheetAdapter] BOM 시트 목록: ${sheets.join(', ')}`);

    // Try to find BOM sheet
    const possibleSheetNames = ['BOM', 'bom', '자재명세서', '소요량', 'DB_BOM'];
    let bomSheet = sheets.find(s =>
      possibleSheetNames.some(n => s.toLowerCase().includes(n.toLowerCase()))
    );
    if (!bomSheet && sheets.length > 0) {
      bomSheet = sheets[0];
    }

    if (!bomSheet) {
      console.warn('No BOM sheet found');
      return [];
    }

    console.log(`[MultiSpreadsheetAdapter] BOM 시트 사용: ${bomSheet}`);
    const rawData = await this.fetchSheetData(this.bomSpreadsheetId, bomSheet);

    return rawData.map(row => ({
      parentItemCode: String(
        row['모품목코드'] || row['완제품코드'] || row['생산품목코드'] || row['상위품목'] || ''
      ),
      parent

  async fetchAllCostAnalysisData(): Promise<CostAnalysisData> {
    console.log('[MultiSpreadsheetAdapter] 원가 분석 데이터 수집 시작...');

    const [sales, purchases, bom] = await Promise.all([
      this.fetchSalesTransactions(),
      this.fetchPurchaseTransactions(),
      this.fetchBomData(),
    ]);

    console.log(
      `[MultiSpreadsheetAdapter] 수집 완료: 판매 ${sales.length}, 구매 ${purchases.length}, BOM ${bom.length} 건`
    );

    return {
      sales,
      purchases,
      bom,
      fetchedAt: new Date().toISOString(),
    };
  }

  private getClient(): SupabaseClient {
    if (!this.client) {
      if (!this.url || !this.key) {
        throw new Error('SUPABASE_URL and SUPABASE_KEY must be set in environment variables');
      }
      this.client = createClient(this.url, this.key);
    }
    return this.client;
  }

  isConfigured(): boolean {
    return !!(this.url && this.key);
  }

  private async fetchAllPaginated<T>(
    table: string,
    options?: {
      orderBy?: string;
      ascending?: boolean;
      dateFrom?: string;
      dateTo?: string;
      dateColumn?: string;
    }
  ): Promise<T[]> {
    const client = this.getClient();
    const pageSize = 1000;
    const allData: T[] = [];
    let from = 0;

    while (true) {
      let query = client
        .from(table)
        .select('*')
        .order(options?.orderBy || 'date', { ascending: options?.ascending ?? false })
        .range(from, from + pageSize - 1);

      if (options?.dateFrom) query = query.gte(options.dateColumn || 'date', options.dateFrom);
      if (options?.dateTo) query = query.lte(options.dateColumn || 'date', options.dateTo);

      const { data, error } = await query;
      if (error) throw new Error(`${table} query failed: ${error.message}`);
      if (!data || data.length === 0) break;

      allData.push(...(data as T[]));
      if (data.length < pageSize) break; // 마지막 페이지
 

  async upsertDailySales(rows: DailySalesRow[]): Promise<number> {
    if (rows.length === 0) return 0;
    const client = this.getClient();

    const { data, error } = await client
      .from('daily_sales')
      .upsert(
        rows.map(r => ({ ...r, synced_at: new Date().toISOString() })),
        { onConflict: 'date' }
      );

    if (error) throw new Error(`daily_sales upsert failed: ${error.message}`);
    return rows.length;
  }

  async getDailySales(dateFrom?: string, dateTo?: string): Promise<DailySalesRow[]> {
    return this.fetchAllPaginated<DailySalesRow>('daily_sales', { dateFrom, dateTo });
  }

  async upsertSalesDetail(rows: SalesDetailRow[]): Promise<number> {
    if (rows.length === 0) return 0;
    const client = this.getClient();

    // 날짜별 그룹핑 → RPC로 원자적 DELETE+INSERT (트랜잭션 안전)
    const byDate = new Map<string, SalesDetailRow[]>();
    for (const r of rows) {
      if (!r.date) continue;
      const arr = byDate.get(r.date) || [];
      arr.push(r);
      byDate.set(r.date, arr);
    }

    let totalInserted = 0;
    const errors: string[] = [];
    const syncedAt = new Date().toISOString();

    for (const [date, dateRows] of byDate) {
      try {
        const jsonRows = dateRows.map(r => ({ ...r, synced_at: syncedAt }));
        const { data, error } = await client.rpc('upsert_sales_detail_by_date', {
          p_date: date,
          p_rows: jsonRows,
        });
        if (error) throw error;
        totalInserted += (data as number) ?? dateRows.length;
      } catch (e: unknown) {
        const msg = e instanceof Error ? e.message : String(e);
        errors.pu

  async getSalesDetail(dateFrom?: string, dateTo?: string): Promise<SalesDetailRow[]> {
    return this.fetchAllPaginated<SalesDetailRow>('sales_detail', { dateFrom, dateTo });
  }

  async upsertProductionDaily(rows: ProductionDailyRow[]): Promise<number> {
    if (rows.length === 0) return 0;
    const client = this.getClient();

    const { error } = await client
      .from('production_daily')
      .upsert(
        rows.map(r => ({ ...r, synced_at: new Date().toISOString() })),
        { onConflict: 'date' }
      );

    if (error) throw new Error(`production_daily upsert failed: ${error.message}`);
    return rows.length;
  }

  async getProductionDaily(dateFrom?: string, dateTo?: string): Promise<ProductionDailyRow[]> {
    return this.fetchAllPaginated<ProductionDailyRow>('production_daily', { dateFrom, dateTo });
  }

  async upsertPurchases(rows: PurchaseRow[]): Promise<number> {
    if (rows.length === 0) return 0;
    const client = this.getClient();

    // 날짜별 그룹핑 → RPC로 원자적 DELETE+INSERT (트랜잭션 안전)
    const byDate = new Map<string, PurchaseRow[]>();
    for (const r of rows) {
      if (!r.date) continue;
      const arr = byDate.get(r.date) || [];
      arr.push(r);
      byDate.set(r.date, arr);
    }

    let totalInserted = 0;
    const errors: string[] = [];
    const syncedAt = new Date().toISOString();

    for (const [date, dateRows] of byDate) {
      try {
        const jsonRows = dateRows.map(r => ({ ...r, synced_at: syncedAt }));
        const { data, error } = await client.rpc('upsert_purchases_by_date', {
          p_date: date,
          p_rows: jsonRows,
        });
        if (error) throw error;
        totalInserted += (data as number) ?? dateRows.length;
      } catch (e: unknown) {
        const msg = e instanceof Error ? e.message : String(e);
        errors.push(`${date}

  async getPurchases(dateFrom?: string, dateTo?: string): Promise<PurchaseRow[]> {
    return this.fetchAllPaginated<PurchaseRow>('purchases', { dateFrom, dateTo });
  }

  async upsertInventory(rows: InventoryRow[]): Promise<number> {
    if (rows.length === 0) return 0;
    const client = this.getClient();

    const today = new Date().toISOString().slice(0, 10);
    const { error } = await client
      .from('inventory')
      .upsert(
        rows.map(r => ({
          ...r,
          snapshot_date: r.snapshot_date || today,
          synced_at: new Date().toISOString(),
        })),
        { onConflict: 'product_code,warehouse_code,snapshot_date' }
      );

    if (error) throw new Error(`inventory upsert failed: ${error.message}`);
    return rows.length;
  }