const { Sequelize, DataTypes } = require('sequelize');
const sequelize = require("../config/dbconnection");

const CustomerOrder = sequelize.define('customer_orders', {
  id: { type: DataTypes.INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
  customer_id: { type: DataTypes.INTEGER, allowNull: true, defaultValue: null },
  customer_name: { type: DataTypes.STRING(255), allowNull: true, defaultValue: null },
  customer_phone: { type: DataTypes.STRING(20), allowNull: true, defaultValue: null },
  customer_address: { type: DataTypes.TEXT, allowNull: true, defaultValue: null },
  tax: {
    type: DataTypes.DECIMAL(12, 2),
    allowNull: false,
    get() {
      const value = this.getDataValue('tax');
      return value === null ? null : parseFloat(value);
    }
  },
  tax_amount: {
    type: DataTypes.FLOAT(12, 2),
    allowNull: false,
    defaultValue: 0.00
  },
  tax_category: {
    type: DataTypes.INTEGER,
    allowNull: true,
    defaultValue: null
  },
  sub_total: {
    type: DataTypes.DECIMAL(12, 2),
    allowNull: false,
    get() {
      const value = this.getDataValue('sub_total');
      return value === null ? null : parseFloat(value);
    }
  },
  addl_amount: {
    type: DataTypes.FLOAT(14, 2),
    allowNull: false,
    defaultValue: 0,
  },
  grandTotal: {
    type: DataTypes.FLOAT(14, 2),
    allowNull: false,
    defaultValue: 0,
  },
  total: {
    type: DataTypes.DECIMAL(14, 2),
    allowNull: true,
    defaultValue: null,
    get() {
      const value = this.getDataValue('total');
      return value === null ? null : parseFloat(value);
    }
  },
  budget: {
    type: DataTypes.DECIMAL(12, 2),
    allowNull: true,
    defaultValue: null,
    get() {
      const value = this.getDataValue('budget');
      return value === null ? null : parseFloat(value);
    }
  },
  request_id: { type: DataTypes.INTEGER, allowNull: true, defaultValue: null },
  extra_discount: {
    type: DataTypes.DECIMAL(9, 2),
    allowNull: false,
    get() {
      const value = this.getDataValue('extra_discount');
      return value === null ? null : parseFloat(value);
    }
  },
  timestamp: { type: DataTypes.INTEGER, allowNull: false },
  status: { type: DataTypes.TINYINT, allowNull: false, defaultValue: 1 }
}, {
  tableName: 'customer_orders',
  timestamps: false
});

const createCustomerOrder = async (data) => {
  let res = await CustomerOrder.create(data)
  return res.id;
}
const updateCustomerOrder = async (order_id, data) => {
  let result = await CustomerOrder.update(data, {
    where: {
      id: order_id
    }
  })

  console.log('updateCustomerOrder===result===', result)
  if (result[0] > 0) {
    return result.id;
  } else {
    return false;
  }
}

const parseDecimalFields = (order) => {
  order.total = order.total ? parseFloat(order.total) : null;
  order.invoice_total = order.invoice_total ? parseFloat(order.invoice_total) : null;
  order.budget = order.budget ? parseFloat(order.budget) : null;
  order.sub_total = order.sub_total ? parseFloat(order.sub_total) : null;
  order.tax = order.tax ? parseFloat(order.tax) : null;
  order.extra_discount = order.extra_discount ? parseFloat(order.extra_discount) : null;
  return order;
}

const getCustomerOrderRequestId = async (request_id) => {

  let query = `
      SELECT cust_ord.*,p.address as customerAddress,p.city as customer_city,p.zip_code as customer_zipcode, r.installation_acceptance, r.send_to_client as customer_send_to_client, p.contact_name AS customer_contact_name,p.phone as customer_phone,s.name as customer_state,r.name as requestName,r.is_split_order,r.for_parent_facility,r.isReview,r.status as requestStatus,r.id as requestId,p.email as clientEmail, r.participation_ends,r.project_category, r.quotation, r.quotation_status, r.is_request_estimate ,u.profile_pic AS userProfilePic, ct.name AS customer_billing_type, ct.billing_type AS customer_billing_type_value, p.fax_number as customer_fax_number,
      CASE WHEN ur.isClientUserRole = 1 THEN 1 ELSE 0 END as isAddedByClient,r.origin_facility_id,r.po_number,r.facility_type,r.origin_facility_request_id,r.no_charge_reason,r.estimate_id,r.syncWithQuickbooks
      FROM customer_orders cust_ord
      left join requests r on r.id=cust_ord.request_id
      left join partners p on p.id=cust_ord.customer_id
      left join customer_type ct on p.customerTypeId = ct.id
      left join users u1 on u1.id = r.added_by
      left join states s on s.id=p.state_id
      left join user_roles ur on ur.id = u1.user_type
       LEFT JOIN users u ON p.user_id = u.id
      WHERE cust_ord.request_id = :requestId`
  const result = await sequelize.query(query, {
    replacements: { requestId: request_id },
    type: sequelize.QueryTypes.SELECT,
  });

  if (result.length === 1) {
    // return result[0];
    return parseDecimalFields(result[0]);
  } else {
    return false;
  }
};

const deleteCustomerOrderById = async (orderId, transaction = null) => {
  try {
    const result = await CustomerOrder.destroy({
      where: { id: orderId },
      transaction
    });

    return result > 0; // true if deleted
  } catch (error) {
    console.error('Error deleting customer order:', error);
    return false;
  }
};

module.exports = {
  CustomerOrder,
  CustomerOrdersModel: CustomerOrder,
  createCustomerOrder,
  getCustomerOrderRequestId,
  updateCustomerOrder,
  deleteCustomerOrderById
}