const { columns } = require("mssql");
const pool = require("../config");

const getHistoryAlarmDb = async (searchParams = {}) => {
  let queryParams = [];

  if (searchParams.limit) {
    const page = Number(searchParams.page ?? 1) - 1;
    queryParams = [Number(searchParams.limit ?? 10), page];
  }

  const { whereOrConditions, whereParamOr } = pool.buildStringOrIlike(
    [
      "b.tag_name",
      "a.tagnum"
    ],
    searchParams.criteria,
    queryParams
  );

  if (whereParamOr) queryParams = whereParamOr;

  const { whereConditions, whereParamAnd } = pool.buildFilterQuery(
    [
      { column: "b.tag_name", param: searchParams.name, type: "string" },
      { column: "b.tag_number", param: searchParams.name, type: "number" },
    ],
    queryParams
  );

  if (whereParamAnd) queryParams = whereParamAnd;

  const queryText = `
    SELECT 
      COUNT(*) OVER() AS total_data, 
      a.*, 
      b.tag_name, 
      b.tag_number,
      b.lim_low_crash,
      b.lim_low,
      b.lim_high,
      b.lim_high_crash,
      c.status_color
    FROM alarm_history a
    LEFT JOIN m_tags b ON a.tagnum = b.tag_number AND b.deleted_at IS NULL
    LEFT JOIN m_status c ON a.status = c.status_number AND c.deleted_at IS NULL
    WHERE a.datetime IS NOT NULL
      ${whereConditions.length > 0 ? ` AND ${whereConditions.join(" AND ")}` : ""}
      ${whereOrConditions ? ` ${whereOrConditions}` : ""}
    ORDER BY a.datetime DESC
     ${searchParams.limit ? `OFFSET $2 * $1 ROWS FETCH NEXT $1 ROWS ONLY` : ''}
  `;

  const result = await pool.query(queryText, queryParams);

  const total =
    result?.recordset?.length > 0
      ? parseInt(result.recordset[0].total_data, 10)
      : 0;

  return { data: result.recordset, total };
};

const getHistoryEventDb = async (searchParams = {}) => {
  let queryParams = [];

  if (searchParams.limit) {
    const page = Number(searchParams.page ?? 1) - 1;
    queryParams = [Number(searchParams.limit ?? 10), page];
  }

  const { whereOrConditions, whereParamOr } = pool.buildStringOrIlike(
    [
      "b.tag_name",
      "a.tagnum"
    ],
    searchParams.criteria,
    queryParams
  );

  if (whereParamOr) queryParams = whereParamOr;

  const { whereConditions, whereParamAnd } = pool.buildFilterQuery(
    [
      { column: "b.tag_name", param: searchParams.name, type: "string" },
      { column: "b.tag_number", param: searchParams.name, type: "number" },
    ],
    queryParams
  );

  if (whereParamAnd) queryParams = whereParamAnd;

  const queryText = `
    SELECT 
      COUNT(*) OVER() AS total_data, 
      a.*,
      c.status_color
    FROM event_alarm a
    LEFT JOIN m_status c ON a.status = c.status_number AND c.deleted_at IS NULL
    WHERE a.datetime IS NOT NULL
      ${whereConditions.length > 0 ? ` AND ${whereConditions.join(" AND ")}` : ""}
      ${whereOrConditions ? ` ${whereOrConditions}` : ""}
    ORDER BY a.datetime DESC
     ${searchParams.limit ? `OFFSET $2 * $1 ROWS FETCH NEXT $1 ROWS ONLY` : ''}
  `;

  const result = await pool.query(queryText, queryParams);

  const total =
    result?.recordset?.length > 0
      ? parseInt(result.recordset[0].total_data, 10)
      : 0;

  return { data: result.recordset, total };
};

const checkTableNamedDb = async (tableName) => {
  try {
    if (!tableName || !/^[a-zA-Z0-9_]+$/.test(tableName)) {
      throw new Error('Invalid table name format');
    }

    const queryText = `
      SELECT TABLE_NAME, TABLE_SCHEMA, TABLE_TYPE
      FROM INFORMATION_SCHEMA.TABLES
      WHERE TABLE_NAME = $1
    `;
    
    const result = await pool.query(queryText, [tableName]);
    return result.recordset;
  } catch (error) {
    console.error('Error in checkTableNamedDb:', error);
    throw error;
  }
};

const getHistoryValueReportDb = async (tableName, searchParams = {}) => {
  try {
    if (!tableName || !/^[a-zA-Z0-9_]+$/.test(tableName)) {
      throw new Error('Invalid table name format');
    }

    let queryParams = [];
    
    if (searchParams.limit) {
      const page = Number(searchParams.page ?? 1) - 1;
      queryParams = [Number(searchParams.limit ?? 10), page];
    }

    const { whereOrConditions, whereParamOr } = pool.buildStringOrIlike(
      ["b.tag_name", "CAST(a.tagnum AS VARCHAR)"],
      searchParams.criteria,
      queryParams
    );

    if (whereParamOr) queryParams = whereParamOr;

    const { whereConditions, whereParamAnd } = pool.buildFilterQuery(
      [
        { column: "b.tag_name", param: searchParams.name, type: "string" },
        { column: "b.tag_number", param: searchParams.name, type: "number" },
        { column: "a.datetime", param: [searchParams.from, searchParams.to], type: "between" },
      ],
      queryParams
    );

    if (whereParamAnd) queryParams = whereParamAnd;

    const queryText = `
      SELECT 
        COUNT(*) OVER() AS total_data, 
        a.*, 
        b.tag_name, 
        b.tag_number,
        b.lim_low_crash,
        b.lim_low,
        b.lim_high,
        b.lim_high_crash,
        c.status_color
      FROM ${tableName} a
      LEFT JOIN m_tags b ON a.tagnum = b.tag_number AND b.deleted_at IS NULL
      LEFT JOIN m_status c ON a.status = c.status_number AND c.deleted_at IS NULL
      WHERE a.datetime IS NOT NULL AND b.is_report = 1
        ${whereConditions.length > 0 ? ` AND ${whereConditions.join(" AND ")}` : ""}
        ${whereOrConditions ? ` ${whereOrConditions}` : ""}
      ORDER BY a.datetime DESC
      ${searchParams.limit ? `OFFSET $2 * $1 ROWS FETCH NEXT $1 ROWS ONLY` : ''}
    `;

    const result = await pool.query(queryText, queryParams);

    const total = result.recordset?.length > 0
      ? parseInt(result.recordset[0].total_data, 10)
      : 0;

    return { data: result.recordset, total };
  } catch (error) {
    console.error('Error in getHistoryValueReportDb:', error);
    throw error;
  }
};

const getHistoryValueReportPivotDb = async (tableName, searchParams = {}) => {
  try {
    if (!tableName || !/^[a-zA-Z0-9_]+$/.test(tableName)) {
      throw new Error('Invalid table name format');
    }

    let from = searchParams.from || '';
    let to = searchParams.to || '';
    const interval = Math.max(1, Math.min(1440, Number(searchParams.interval ?? 10)));

    if (from.length === 10 && /^\d{4}-\d{2}-\d{2}$/.test(from)) {
      from += ' 00:00:00';
    }
    if (to.length === 10 && /^\d{4}-\d{2}-\d{2}$/.test(to)) {
      to += ' 23:59:59';
    }

    console.log('Table:', tableName);
    console.log('From:', from, '| To:', to, '| Interval:', interval);
    console.log('Filters:', searchParams);

    const dateRegex = /^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$/;
    if (!dateRegex.test(from) || !dateRegex.test(to)) {
      throw new Error('Invalid date format. Expected: YYYY-MM-DD or YYYY-MM-DD HH:MM:SS');
    }

    const fromDate = new Date(from);
    const toDate = new Date(to);
    const daysDiff = (toDate - fromDate) / (1000 * 60 * 60 * 24);
    
    if (daysDiff > 365) {
      throw new Error('Date range cannot exceed 1 year');
    }
    if (daysDiff < 0) {
      throw new Error('From date must be before to date');
    }

    let tagQueryParams = [];
    let tagWhereConditions = [];

    if (searchParams.plant_sub_section_id) {
      tagWhereConditions.push(`plant_sub_section_id = $${tagQueryParams.length + 1}`);
      tagQueryParams.push(searchParams.plant_sub_section_id);
    }

    if (searchParams.plant_section_id) {
      tagWhereConditions.push(`plant_section_id = $${tagQueryParams.length + 1}`);
      tagQueryParams.push(searchParams.plant_section_id);
    }

    if (searchParams.name) {
      const nameFilter = `(tag_name LIKE $${tagQueryParams.length + 1} OR CAST(tag_number AS VARCHAR) LIKE $${tagQueryParams.length + 2})`;
      tagWhereConditions.push(nameFilter);
      tagQueryParams.push(`%${searchParams.name}%`, `%${searchParams.name}%`);
    }

    if (searchParams.criteria) {
      const criteriaFilter = `(tag_name LIKE $${tagQueryParams.length + 1} OR CAST(tag_number AS VARCHAR) LIKE $${tagQueryParams.length + 2})`;
      tagWhereConditions.push(criteriaFilter);
      tagQueryParams.push(`%${searchParams.criteria}%`, `%${searchParams.criteria}%`);
    }

    const tagWhereClause = tagWhereConditions.length > 0 
      ? ` AND ${tagWhereConditions.join(" AND ")}` 
      : '';

    const tagsQuery = `
      SELECT tag_name, tag_number
      FROM m_tags 
      WHERE is_report = 1 AND deleted_at IS NULL
        ${tagWhereClause}
      ORDER BY tag_name
    `;
    
    console.log('Tags Query:', tagsQuery);
    console.log('Tags Query Params:', tagQueryParams);
    
    const tagsResult = await pool.query(tagsQuery, tagQueryParams);

    console.log('Tags found:', tagsResult.recordset.length);

    if (tagsResult.recordset.length === 0) {
      return { data: [], column: '' };
    }

    const tagNames = tagsResult.recordset.map(r => `[${r.tag_name}]`).join(', ');
    const tagNamesColumn = tagsResult.recordset.map(r => r.tag_name).join(', ');
    const tagNumbers = tagsResult.recordset.map(r => r.tag_number);

    console.log('Filtered tag numbers:', tagNumbers);
    console.log('Filtered tag names:', tagNamesColumn);

    const tagNumbersFilter = tagNumbers.length > 0 
      ? ` AND a.tagnum IN (${tagNumbers.join(',')})` 
      : '';

    const queryText = `
      DECLARE 
        @fromParam DATETIME = $1,
        @toParam DATETIME = $2,
        @intervalParam INT = $3;

      SELECT TOP 10
          'DEBUG_AVERAGING' as info,
          b.tag_name,
          DATEADD(MINUTE, 
              (DATEDIFF(MINUTE, @fromParam, CAST(a.datetime AS DATETIME)) / @intervalParam) * @intervalParam, 
              @fromParam
          ) AS waktu_group,
          COUNT(*) as data_points,
          AVG(CAST(a.val AS FLOAT)) as avg_val,
          MIN(CAST(a.val AS FLOAT)) as min_val,
          MAX(CAST(a.val AS FLOAT)) as max_val
      FROM ${tableName} a
      INNER JOIN m_tags b ON a.tagnum = b.tag_number 
          AND b.deleted_at IS NULL 
          AND b.is_report = 1
      WHERE CAST(a.datetime AS DATETIME) BETWEEN @fromParam AND @toParam
          AND a.val IS NOT NULL
          ${tagNumbersFilter}
      GROUP BY 
          b.tag_name,
          DATEADD(MINUTE, 
              (DATEDIFF(MINUTE, @fromParam, CAST(a.datetime AS DATETIME)) / @intervalParam) * @intervalParam, 
              @fromParam
          )
      ORDER BY b.tag_name, waktu_group;

      ;WITH TimeSeries AS (
          SELECT @fromParam AS waktu
          UNION ALL
          SELECT DATEADD(MINUTE, @intervalParam, waktu)
          FROM TimeSeries
          WHERE DATEADD(MINUTE, @intervalParam, waktu) <= @toParam
      ),
      CleanData AS (
          SELECT 
              CAST(a.datetime AS DATETIME) as datetime_clean,
              a.tagnum,
              CAST(a.val AS FLOAT) as val,
              b.tag_name
          FROM ${tableName} a
          INNER JOIN m_tags b ON a.tagnum = b.tag_number 
              AND b.deleted_at IS NULL 
              AND b.is_report = 1
          WHERE ISDATE(a.datetime) = 1
              AND a.val IS NOT NULL
              AND CAST(a.datetime AS DATETIME) BETWEEN @fromParam AND @toParam
              ${tagNumbersFilter}
      ),
      Averaged AS (
          SELECT 
              DATEADD(MINUTE, 
                  (DATEDIFF(MINUTE, @fromParam, datetime_clean) / @intervalParam) * @intervalParam, 
                  @fromParam
              ) AS waktu_group,
              tag_name,
              AVG(val) AS avg_val
          FROM CleanData
          GROUP BY 
              DATEADD(MINUTE, 
                  (DATEDIFF(MINUTE, @fromParam, datetime_clean) / @intervalParam) * @intervalParam, 
                  @fromParam
              ),
              tag_name
      ),
      Pivoted AS (
          SELECT 
              waktu_group,
              ${tagNames}
          FROM Averaged
          PIVOT (
              MAX(avg_val)
              FOR tag_name IN (${tagNames})
          ) AS p
      )
      SELECT 
          CONVERT(VARCHAR(19), ts.waktu, 120) AS waktu,
          ${tagNames}
      FROM TimeSeries ts
      LEFT JOIN Pivoted p ON ts.waktu = p.waktu_group
      ORDER BY ts.waktu
      OPTION (MAXRECURSION 0);
    `;

    const result = await pool.query(queryText, [from, to, interval]);

    if (result.recordsets && result.recordsets.length >= 2) {
      console.log('Sample averaging data:');
      result.recordsets[0].slice(0, 10).forEach(row => {
        console.log(`${row.tag_name} @ ${row.waktu_group}: avg=${row.avg_val}, min=${row.min_val}, max=${row.max_val}, points=${row.data_points}`);
      });
      
      console.log('\nPivot result sample:');
      if (result.recordsets[1] && result.recordsets[1].length > 0) {
        result.recordsets[1].slice(0, 5).forEach(row => {
          console.log(JSON.stringify(row, null, 2));
        });
      }
    }

    const rows = result.recordsets?.[1] || result.recordset;

    if (!rows || rows.length === 0) {
      console.log('No pivot data');
      return { data: [], column: tagNamesColumn };
    }

    const timeKey = 'waktu';
    const tagList = Object.keys(rows[0]).filter(k => k !== timeKey);

    const nivoData = tagList.map(tag => ({
      id: tag,
      data: rows.map(row => ({
        x: row[timeKey],
        y: row[tag] !== null && row[tag] !== undefined ? Number(row[tag]) : null
      }))
    }));

    nivoData.forEach(series => {
      const nonNull = series.data.filter(d => d.y !== null && d.y !== 0);
      const sampleVals = nonNull.slice(0, 3).map(d => d.y);
      console.log(`${series.id}: ${nonNull.length} non-zero values, sample: [${sampleVals.join(', ')}]`);
    });

    return { data: nivoData, column: tagNamesColumn };
  } catch (error) {
    console.error('Error in getHistoryValueReportPivotDb:', error);
    throw error;
  }
};

const getHistoryValueTrendingPivotDb = async (tableName, searchParams = {}) => {
  let from = searchParams.from;
  let to = searchParams.to;

  // pastikan interval berupa number dan ada nilai default
  const interval = Number(searchParams.interval) > 0 ? Number(searchParams.interval) : 10;

  // --- Normalisasi tanggal (kalau cuma tanggal tanpa jam)
  if (from.length === 10) from += ' 00:00:00';
  if (to.length === 10) to += ' 23:59:59';

  let tagQueryParams = [];
  let tagWhereConditions = [];

  if (searchParams.plant_sub_section_id) {
    tagWhereConditions.push(`plant_sub_section_id = $${tagQueryParams.length + 1}`);
    tagQueryParams.push(searchParams.plant_sub_section_id);
  }

  if (searchParams.plant_section_id) {
    tagWhereConditions.push(`plant_section_id = $${tagQueryParams.length + 1}`);
    tagQueryParams.push(searchParams.plant_section_id);
  }

  if (searchParams.name) {
    const nameFilter = `(tag_name LIKE $${tagQueryParams.length + 1} OR CAST(tag_number AS VARCHAR) LIKE $${tagQueryParams.length + 2})`;
    tagWhereConditions.push(nameFilter);
    tagQueryParams.push(`%${searchParams.name}%`, `%${searchParams.name}%`);
  }

  if (searchParams.criteria) {
    const criteriaFilter = `(tag_name LIKE $${tagQueryParams.length + 1} OR CAST(tag_number AS VARCHAR) LIKE $${tagQueryParams.length + 2})`;
    tagWhereConditions.push(criteriaFilter);
    tagQueryParams.push(`%${searchParams.criteria}%`, `%${searchParams.criteria}%`);
  }

  const tagWhereClause = tagWhereConditions.length > 0 
    ? ` AND ${tagWhereConditions.join(" AND ")}` 
    : '';

  const tagsQuery = `
    SELECT tag_name, tag_number
    FROM m_tags 
    WHERE is_report = 1 AND deleted_at IS NULL
      ${tagWhereClause}
    ORDER BY tag_name
  `;

  const tags = await pool.query(tagsQuery, tagQueryParams);

  if (tags.recordset.length === 0) {
    return { data: [] };
  }

  const tagNames = tags.recordset.map(r => `[${r.tag_name}]`).join(', ');
  const tagNumbers = tags.recordset.map(r => r.tag_number);

  const tagNumbersFilter = tagNumbers.length > 0 
    ? ` AND a.tagnum IN (${tagNumbers.join(',')})` 
    : '';

  const queryText = `
    DECLARE 
      @fromParam DATETIME = '${from}',
      @toParam DATETIME = '${to}',
      @intervalParam INT = ${interval};

    ;WITH TimeSeries AS (
        SELECT @fromParam AS waktu
        UNION ALL
        SELECT DATEADD(MINUTE, @intervalParam, waktu)
        FROM TimeSeries
        WHERE DATEADD(MINUTE, @intervalParam, waktu) <= @toParam
    ),
    Averaged AS (
        SELECT 
            DATEADD(MINUTE, DATEDIFF(MINUTE, 0, CAST(a.datetime AS DATETIME)) / @intervalParam * @intervalParam, 0) AS waktu_group,
            b.tag_name,
            ROUND(AVG(a.val), 4) AS avg_val
        FROM ${tableName} a
        LEFT JOIN m_tags b ON a.tagnum = b.tag_number AND b.deleted_at IS NULL
        WHERE a.datetime BETWEEN @fromParam AND @toParam
          ${tagNumbersFilter}
        GROUP BY 
            DATEADD(MINUTE, DATEDIFF(MINUTE, 0, CAST(a.datetime AS DATETIME)) / @intervalParam * @intervalParam, 0),
            b.tag_name
    ),
    Pivoted AS (
        SELECT 
            waktu_group,
            ${tagNames}
        FROM Averaged
        PIVOT (
            MAX(avg_val)
            FOR tag_name IN (${tagNames})
        ) AS p
    )
    SELECT 
        CONVERT(VARCHAR(16), ts.waktu, 120) AS waktu,
        ${tagNames}
    FROM TimeSeries ts
    LEFT JOIN Pivoted p ON ts.waktu = p.waktu_group
    ORDER BY ts.waktu
    OPTION (MAXRECURSION 0);
  `;

  const result = await pool.query(queryText);
  const rows = result.recordset;

  if (!rows || rows.length === 0) return { data: [] };

  // --- Bentuk data untuk Nivo Chart
  const timeKey = 'waktu';
  const tagList = Object.keys(rows[0]).filter(k => k !== timeKey);

  const nivoData = tagList.map(tag => ({
    id: tag,
    data: rows.map(row => ({
      x: row[timeKey],
      y: row[tag] !== null ? Number(row[tag]) : null
    }))
  }));

  return { data: nivoData };
};

module.exports = {
  getHistoryAlarmDb,
  getHistoryEventDb,
  checkTableNamedDb,
  getHistoryValueReportDb,
  getHistoryValueReportPivotDb,
  getHistoryValueTrendingPivotDb
};