
const { INTEGER, STRING, TEXT, FLOAT, TINYINT, CHAR, DECIMAL, Sequelize, BIGINT } = require("sequelize");
const sequelize = require("../config/dbconnection");

const SplitEstimate = sequelize.define('split_estimates', {
    id: { type: BIGINT, allowNull: false, primaryKey: true, autoIncrement: true },
    name: { type: TEXT, allowNull: true ,defaultValue:null},
    description: { type: TEXT, allowNull: true ,defaultValue:null},
    origin_facility_id: { type: INTEGER, allowNull: true ,defaultValue:null},
    to_facility_id: { type: INTEGER, allowNull: true ,defaultValue:null},
    status: { type: INTEGER, allowNull: false ,defaultValue:1},
    created_at: { type: INTEGER(20), allowNull: true ,defaultValue:null},
    timestamp: { type: INTEGER(20), allowNull: true ,defaultValue:null},
    added_by: { type: INTEGER, allowNull: true ,defaultValue:null},
    total_cost: { type: FLOAT(12,2), allowNull: true ,defaultValue:0},
    reason: { type: TEXT, allowNull: true ,defaultValue:null},
    isFromDesignOrder: { type: INTEGER, allowNull: true, defaultValue: null },
  }, { timestamps: false })
const SplitEstimateAttachments = sequelize.define('split_estimates_attachments', {
    id: { type: INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
    url: { type: TEXT, allowNull: true ,defaultValue:null},
    file_type: { type: TEXT, allowNull: true ,defaultValue:null},
    split_estimate_id: { type: BIGINT, allowNull: false },
    status: { type: INTEGER, allowNull: false ,defaultValue:1},
    timestamp: { type: INTEGER(20), allowNull: true ,defaultValue:null},
  }, { timestamps: false })

  const SplitEstimateRecipes = sequelize.define('split_estimate_recipes', {
    id: { type: INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
    split_estimate_id: { type: BIGINT, allowNull: false },
    recipe_name: { type: TEXT, allowNull: true ,defaultValue:null},
    expected_cost: { type: FLOAT(12,2), allowNull: false ,defaultValue:0},
    total_cost: { type: FLOAT(12,2), allowNull: false ,defaultValue:0},
    split_percentage: { type: FLOAT(12,2), allowNull: false ,defaultValue:0},
    recipe_id: { type: INTEGER, allowNull: true ,defaultValue:null},
    product_id: { type: INTEGER, allowNull: true ,defaultValue:null},
    product_name: { type: TEXT, allowNull: true ,defaultValue:null},
    product_added: { type: TINYINT, allowNull: false, defaultValue: 0 },
    expected_install_labor_cost: { type: FLOAT(12,2), allowNull: true, defaultValue: 0 },
  }, { timestamps: false })

  const splitEstimateServices = sequelize.define('split_estimate_services', {
    id: { type: INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
    name: { type: TEXT, allowNull: true ,defaultValue:null},
    labour_cost_service_id:{ type: INTEGER, allowNull: false },
    quantity:{ type: FLOAT(14,2), allowNull: false },
    total:{ type: FLOAT(12,2), allowNull: false },
    unit_cost:{ type: FLOAT(14,2), allowNull: false },
    taken:{ type: INTEGER, allowNull: false,defaultValue:0 },
    job_cost:{ type: INTEGER, allowNull: false,defaultValue:0 },
    jobcost_quantity:{ type: FLOAT(14,2), allowNull: false,defaultValue:0.00 },
    jobcost_total_cost:{ type: FLOAT(14,2), allowNull: false,defaultValue:0.00 },
    split_estimate_id:{ type: BIGINT, allowNull: false },
    complete:{ type: INTEGER, allowNull: false, defaultValue: 0 },
    status:{ type: INTEGER, allowNull: false, defaultValue: 1 },
  }, { timestamps: false, tableName:'split_estimate_services' })

  const addSplitEstimate = async (data) => {
    try {
        const splitEstimate = await SplitEstimate.create(data);
        return splitEstimate.id;
    } catch (error) {
        console.log(error);
        return null;
    }
  }

  const updateSplitEstimate = async (data,id) => {
    try {
        const [affectedRows] = await SplitEstimate.update(data, { where: { id: id } });
        
        if (affectedRows === 0) {
            console.log("No rows updated");
            return null;
        }
        
        // Fetch updated row
        const updatedRow = await SplitEstimate.findByPk(id);
        return updatedRow?.get({ plain: true });
    } catch (error) {
        console.log(error);
        return null;
    }
  }

  const updateProductAddedStatus = async (data, selectedRecipeId) => {
    try {
      const [affectedRows] = await SplitEstimateRecipes.update(
        data,
        { where: { id: selectedRecipeId } }
      );
  
      if (affectedRows === 0) {
        console.log("No rows updated");
        return null;
      }
  
      // fetch updated row
      const updatedRow = await SplitEstimateRecipes.findByPk(selectedRecipeId);
      return updatedRow?.get({ plain: true });
  
    } catch (error) {
      console.error('Error updating product_added status:', error);
      throw error;
    }
  };

  const getAllSplitEstimates = async (search, page, rowsPerPage, sort, whereCondition) => {
    try {
        let query = 'SELECT * FROM split_estimates sp';
        let whereClause = '';
        let orderClause = '';
        let limitClause = '';
        
        // Build WHERE clause
        const conditions = [];
        
        // Add whereCondition if provided
        if (whereCondition) {
            conditions.push(whereCondition);
        }
        
        // Add search by name if provided
        if (search) {
            conditions.push(`sp.name LIKE '%${search}%'`);
        }
        
        if (conditions.length > 0) {
            whereClause = ' WHERE ' + conditions.join(' AND ');
        }
        
        // Build ORDER BY clause
        if (sort) {
            switch (sort) {
                case 'new':
                    orderClause = ' ORDER BY sp.id DESC';
                    break;
                case 'old':
                    orderClause = ' ORDER BY sp.id ASC';
                    break;
                case 'last_updated':
                    orderClause = ' ORDER BY sp.timestamp DESC';
                    break;
                default:
                    orderClause = ' ORDER BY sp.id DESC';
            }
        } else {
            orderClause = ' ORDER BY sp.id DESC';
        }
        
        // Build LIMIT clause
        if (rowsPerPage) {
            const offset = page ? (page - 1) * rowsPerPage : 0;
            limitClause = ` LIMIT ${rowsPerPage} OFFSET ${offset}`;
        }
        
        // Combine all parts
        const finalQuery = query + whereClause + orderClause + limitClause;
        
        const splitEstimates = await sequelize.query(finalQuery, {
            type: sequelize.QueryTypes.SELECT
        });
        
        // Get total count for pagination
        const countQuery = 'SELECT COUNT(*) as total FROM split_estimates sp' + whereClause;
        const totalResult = await sequelize.query(countQuery, {
            type: sequelize.QueryTypes.SELECT
        });
        
        return {
            data: splitEstimates,
            totalCount: totalResult[0].total
        };
    } catch (error) {
        console.log(error);
        return null;
    }
  }

  const addSplitEstimateAttachments = async (data) => {
    try {
        const splitEstimateAttachments = await SplitEstimateAttachments.create(data);
        return splitEstimateAttachments;
    } catch (error) {
        console.log(error);
        return null;
    }
  }

  const getSplitEstimateById = async (id) => {
    try {
        const splitEstimate = await SplitEstimate.findByPk(id);
        return splitEstimate;
    } catch (error) {
        console.log(error);
        return null;
  }
}

const getSplitEstimateAttachmentsBySplitEstimateId = async (splitEstimateId) => {
    try {
        const splitEstimateAttachments = await SplitEstimateAttachments.findAll({ where: { split_estimate_id: splitEstimateId } });
        return splitEstimateAttachments;
    } catch (error) {
        console.log(error);
        return null;
    }
}

/** Used by convertPdfToPngHelper for split_estimate document_source. Returns { name, file_type, extension } (name = url for PDF fetch). */
const getSplitEstimateAttachmentByIdForConversion = async (documentId) => {
    try {
        const att = await SplitEstimateAttachments.findByPk(documentId);
        if (!att) return null;
        const row = att.get ? att.get({ plain: true }) : att;
        const ft = (row.file_type || '').toLowerCase();
        const ext = ft.includes('pdf') ? 'pdf' : 'img';
        return { name: row.url || row.name, file_type: row.file_type, extension: ext };
    } catch (error) {
        console.error('Error getting split estimate attachment by ID for conversion:', error);
        return null;
    }
}

const deleteAllSplitEstimateAttachments = async (splitEstimateId) => {
    try {
        const splitEstimateAttachments = await SplitEstimateAttachments.destroy({ where: { split_estimate_id: splitEstimateId } });
        return splitEstimateAttachments;
    } catch (error) {
        console.log(error);
        return null;
    }
}

const addSplitEstimateRecipes = async (data) => {
    try {
        const splitEstimateRecipes = await SplitEstimateRecipes.create(data);
        return splitEstimateRecipes;
    } catch (error) {
        console.log(error);
        return null;
    }
}

const deleteSplitEstimateRecipes = async (splitEstimateId) => {
    try {
        const splitEstimateRecipes = await SplitEstimateRecipes.destroy({ where: { split_estimate_id: splitEstimateId } });
        return splitEstimateRecipes;
    } catch (error) {
        console.log(error);
        return null;
    }
}

const fetchAllSplitEstimateRecipes = async (splitEstimateId) => {
    try {
        const splitEstimateRecipes = await SplitEstimateRecipes.findAll({ where: { split_estimate_id: splitEstimateId } });
        return splitEstimateRecipes;
    } catch (error) {
        console.log(error);
        return null;
    }
}

const addSplitEstimateServices= async (data) => {
    try {
        const result = await splitEstimateServices.create(data);
        return result;
    } catch (error) {
        console.log(error);
        return null;
    }
}

const getSplitEstimateServicesBySplitEstimateId = async (splitEstimateId) => {
    try {
        const services = await splitEstimateServices.findAll({ where: { split_estimate_id: splitEstimateId } });
        return services;
    } catch (error) {
        console.log(error);
        return null;
    }
}

const deleteSplitEstimateServices = async (splitEstimateId) => {
    try {
        const services = await splitEstimateServices.destroy({ where: { split_estimate_id: splitEstimateId } });
        return services;
    } catch (error) {
        console.log(error);
        return null;
    }
}

const getSplitEstimateServicesforRequest = async (id) => {
    let res = await sequelize.query(
      `SELECT ses.*, lcs.name AS name 
       FROM split_estimate_services ses
       LEFT JOIN labour_cost_services lcs ON ses.labour_cost_service_id = lcs.id
       WHERE ses.split_estimate_id = :id`,
      {
        replacements: { id },
        type: sequelize.QueryTypes.SELECT,
      }
    );
  
    return res.length ? res : false;
  };

  module.exports = {
    addSplitEstimate,
    updateSplitEstimate,
    getAllSplitEstimates,
    addSplitEstimateAttachments,
    getSplitEstimateById,
    getSplitEstimateAttachmentsBySplitEstimateId,
    getSplitEstimateAttachmentByIdForConversion,
    deleteAllSplitEstimateAttachments,
    addSplitEstimateRecipes,
    deleteSplitEstimateRecipes,
    fetchAllSplitEstimateRecipes,
    updateProductAddedStatus,
    addSplitEstimateServices,
    getSplitEstimateServicesBySplitEstimateId,
    getSplitEstimateServicesforRequest,
    deleteSplitEstimateServices
  }
