const { INTEGER, STRING, TEXT, TINYINT } = require("sequelize");
const sequelize = require("../config/dbconnection");

const RequestProposalAttachments = sequelize.define('request_proposal_attachments', {
  id: {
    type: INTEGER,
    allowNull: false,
    primaryKey: true,
    autoIncrement: true
  },
  name: {
    type: TEXT,
    allowNull: false
  },
  proposal_id: {
    type: INTEGER,
    allowNull: false
  },
  extension: {
    type: STRING(10),
    allowNull: false
  },
  file_size: {
    type: INTEGER,
    allowNull: false
  },
  file_type: {
    type: STRING(255),
    allowNull: false
  },
  added_by: {
    type: INTEGER,
    allowNull: false
  },
  timestamp: {
    type: INTEGER,
    allowNull: false
  },
  status: {
    type: TINYINT(2),
    allowNull: false,
    defaultValue: 1
  }
}, {
  tableName: 'request_proposal_attachments',
  timestamps: false
});

const DesignOrderAttachments = sequelize.define('design_order_attachments', {
  id: { type: INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
  name: { type: TEXT, allowNull: false },
  design_order_id: { type: INTEGER, allowNull: false },
  extension: { type: STRING(10), allowNull: false,defaultValue:"img" },
  file_size: { type: INTEGER, allowNull: false,defaultValue:2 },
  file_type: { type:STRING(200), allowNull: false,defaultValue:"img" },
  approved: { type: TINYINT,allowNull: false, defaultValue: 0 },
  approved_by: { type: INTEGER,allowNull:true, defaultValue: null },
  added_by: { type: INTEGER, defaultValue: null },
  reason: { type: TEXT, allowNull: true, defaultValue: null },
  timestamp: { type: INTEGER, allowNull: false },
  isChat: { type: TINYINT, defaultValue: 0 },
  status: { type: TINYINT, defaultValue: 1 },
  isDesign: { type: TINYINT, defaultValue: 1 },
  isNew: { type: INTEGER, defaultValue: 1 },
  is_reference: { type: TINYINT,allowNull: false, defaultValue: 0 },
}, { timestamps: false, tableName:'design_order_attachments' })

const RequestAttachments = sequelize.define('request_attachments', {
  id: { type: INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
  name: { type: TEXT, allowNull: false },
  request_id: { type: INTEGER, allowNull: false },
  extension: { type: STRING(10), allowNull: false },
  file_size: { type: INTEGER, allowNull: false },
  file_type: { type: STRING(255), allowNull: false },
  approved: { type: TINYINT(4), allowNull: false, defaultValue: 0 },
  approved_by: { type: INTEGER, allowNull: true },
  added_by: { type: INTEGER, allowNull: false },
  product_id: { type: INTEGER, allowNull: true },
  product_ids: { type: STRING(300), allowNull: true },
  location_ids: { type: STRING(300), allowNull: true },
  reason: { type: TEXT, allowNull: true },
  timestamp: { type: INTEGER, allowNull: false },
  status: { type: TINYINT(2), allowNull: false, defaultValue: 1 }
}, {
  tableName: 'request_attachments',
  timestamps: false
});

const ProjectFiles = sequelize.define('project_files', {
  ID: { type: INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
  userid: { type: INTEGER, allowNull: false },
  project_id: { type: INTEGER, allowNull: false },
  extension: { type: STRING(10), allowNull: false },
  file_size: { type: INTEGER, allowNull: false },
  file_type: { type: STRING(255), allowNull: false },
  file_url: { type: STRING(1000), allowNull: false },
  timestamp: { type: INTEGER, allowNull: false },
  upload_file_name: { type: STRING(255), allowNull: false }
}, {
  tableName: 'project_files',
  timestamps: false
});

const ProjectTaskFiles = sequelize.define('project_task_files', {
  ID: { type: INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
  userid: { type: INTEGER, allowNull: false },
  task_id: { type: INTEGER, allowNull: false },
  extension: { type: STRING(10), allowNull: false },
  file_size: { type: INTEGER, allowNull: false },
  file_type: { type: STRING(255), allowNull: false },
  file_url: { type: STRING(1000), allowNull: false },
  timestamp: { type: INTEGER, allowNull: false },
  upload_file_name: { type: STRING(255), allowNull: false }
}, {
  tableName: 'project_task_files',
  timestamps: false
});

const StaffTaskFiles = sequelize.define('staff_task_files', {
  ID: { type: INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
  userid: { type: INTEGER, allowNull: false },
  taskid: { type: INTEGER, allowNull: false },
  extension: { type: STRING(10), allowNull: false },
  file_size: { type: INTEGER, allowNull: false },
  file_type: { type: STRING(255), allowNull: false },
  file_url: { type: STRING(1000), allowNull: false },
  timestamp: { type: INTEGER, allowNull: false },
  upload_file_name: { type: STRING(255), allowNull: false }
}, {
  tableName: 'staff_task_files',
  timestamps: false
});

const RequestAttachmentImages = sequelize.define('request_attachment_images', {
  id: { type: INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
  document_id: { type: INTEGER, allowNull: false },
  document_source: { type: STRING(20), allowNull: false, defaultValue: 'request' },
  image_url: { type: TEXT, allowNull: false },
  page_number: { type: INTEGER, allowNull: false },
  file_size: { type: INTEGER, allowNull: false },
  timestamp: { type: INTEGER, allowNull: false },
  status: { type: TINYINT(2), allowNull: false, defaultValue: 1 }
}, {
  tableName: 'request_attachment_images',
  timestamps: false
});

const getAllRequestProposalAttachmentsModal = async () => {
  try {
    const [results] = await sequelize.query(`
      SELECT 
        rpa.id,
        rpa.name,
        rpa.proposal_id,
        rpa.extension,
        rpa.file_size,
        rpa.file_type,
        rpa.added_by,
        CONCAT(u.first_name, ' ', u.last_name) AS added_by_name,
        u.profile_pic,
        rpa.timestamp,
        rpa.status,
        rp.name AS proposal_name,
        rp.community AS project_id_community,       -- ✅ NEW: get community ID from proposal
        c.name AS project_name_community            -- ✅ NEW: get community name
      FROM request_proposal_attachments rpa
      LEFT JOIN users u ON rpa.added_by = u.id
      LEFT JOIN proposals rp ON rpa.proposal_id = rp.id
      LEFT JOIN community c ON rp.community = c.id  -- ✅ NEW: join with community table
      WHERE rpa.status != 0 AND rp.id IS NOT NULL
      ORDER BY rpa.id DESC
    `);
    return results;
  } catch (error) {
    console.error('Error fetching proposal attachments:', error);
    return [];
  }
};

const getAllDesignOrderAttachmentsModal = async () => {
  try {
    const [results] = await sequelize.query(`
      SELECT 
        doa.id,
        doa.name,
        doa.design_order_id,
        doa.extension,
        doa.file_size,
        doa.file_type,
        doa.approved,
        doa.approved_by,
        doa.added_by,
        CONCAT(u.first_name, ' ', u.last_name) AS added_by_name,
        u.profile_pic,
        doa.reason,
        doa.isChat,
        doa.timestamp,
        doa.status,
        doa.isDesign,
        d.name AS design_order_name,
        d.community AS project_id_community,                     -- ✅ NEW: community project ID
        c.name AS project_name_community,                        -- ✅ NEW: community project name
        d.client AS client_id,                                   -- ✅ NEW: client ID from design_orders
        p.name AS client_name,                                   -- ✅ NEW: client name from partners
        p.thumbnail AS client_thumbnail                          -- ✅ NEW: client thumbnail from partners
      FROM design_order_attachments doa
      LEFT JOIN users u ON doa.added_by = u.id
      LEFT JOIN design_orders d ON doa.design_order_id = d.id
      LEFT JOIN community c ON d.community = c.id                -- ✅ existing: join with community
      LEFT JOIN partners p ON d.client = p.id                    -- ✅ NEW: join with partners via client
      WHERE doa.status != 0 AND d.id IS NOT NULL
      ORDER BY doa.id DESC
    `);
    return results;
  } catch (error) {
    console.error('Error fetching design order attachments:', error);
    return [];
  }
};

const getAllRequestAttachmentsModal = async () => {
  try {
    const [results] = await sequelize.query(`
      SELECT
        ra.id,
        ra.name,
        ra.request_id,
        ra.extension,
        ra.file_size,
        ra.file_type,
        ra.approved,
        ra.approved_by,
        ra.added_by,
        ra.product_id,
        ra.product_ids,
        ra.location_ids,
        ra.reason,
        ra.timestamp,
        ra.status,
        CONCAT(u.first_name, ' ', u.last_name) AS added_by_name,
        u.profile_pic,
        r.name AS request_name,
        r.id AS request_id_main,
        r.status AS request_status,
        r.community AS project_id_community,
        c.name AS project_name_community,

        -- ✅ Updated: Single client ID and name from joined partner
        p.id AS client_id,
        p.name AS client_name,
        p.thumbnail AS client_thumbnail

      FROM request_attachments ra
      LEFT JOIN users u ON ra.added_by = u.id
      LEFT JOIN requests r ON ra.request_id = r.id
      LEFT JOIN community c ON r.community = c.id
      LEFT JOIN customer_orders co ON co.request_id = r.id
      LEFT JOIN partners p ON co.customer_id = p.id

      WHERE ra.status != 0 AND r.id IS NOT NULL
      ORDER BY ra.id DESC
    `);
    return results;
  } catch (error) {
    console.error('Error fetching request attachments:', error);
    return [];
  }
};

const getAllProjectFilesModal = async () => {
  try {
    const [results] = await sequelize.query(`
      -- ✅ Project Files
      SELECT 
        pf.ID AS id,
        pf.file_url AS name,
        pf.project_id AS project_id,
        pf.extension,
        pf.file_size,
        pf.file_type,
        pf.userid AS added_by,
        CONCAT(u.first_name, ' ', u.last_name) AS added_by_name,
        u.profile_pic,
        pf.timestamp,
        1 AS status,
        pr.name AS project_name,
        pr.community AS project_id_community,
        c.name AS project_name_community
      FROM project_files pf
      LEFT JOIN users u ON pf.userid = u.id
      LEFT JOIN projects pr ON pf.project_id = pr.id
      LEFT JOIN community c ON pr.community = c.id
      WHERE pr.id IS NOT NULL AND pr.community IS NOT NULL

      UNION ALL

      -- ✅ Request Attachments from related requests via projects.request_id
      SELECT 
        ra.id AS id,
        ra.name AS name,
        pr.id AS project_id,
        ra.extension AS extension,          -- ✅ dynamic
        ra.file_size AS file_size,
        ra.file_type AS file_type,          -- ✅ dynamic
        ra.added_by AS added_by,
        CONCAT(u.first_name, ' ', u.last_name) AS added_by_name,
        u.profile_pic,
        ra.timestamp AS timestamp,
        1 AS status,
        pr.name AS project_name,
        pr.community AS project_id_community,
        c.name AS project_name_community
      FROM request_attachments ra
      LEFT JOIN requests r ON ra.request_id = r.id
      LEFT JOIN projects pr ON pr.request_id = r.id
      LEFT JOIN users u ON ra.added_by = u.id
      LEFT JOIN community c ON pr.community = c.id
      WHERE ra.status = 1 AND r.id IS NOT NULL AND pr.id IS NOT NULL
      ORDER BY id DESC
    `);
    return results;
  } catch (error) {
    console.error('Error fetching project and request files:', error);
    return [];
  }
};

const getAllProjectTaskFilesModal = async () => {
  try {
    const [results] = await sequelize.query(`
      SELECT 
        ptf.ID AS id,
        ptf.file_url AS name,
        ptf.task_id,
        ptf.extension,
        ptf.file_size,
        ptf.file_type,
        ptf.userid AS added_by,
        CONCAT(u.first_name, ' ', u.last_name) AS added_by_name,
        u.profile_pic,
        ptf.timestamp,
        1 AS status,
        pt.name AS task_name,
        pt.project_id AS project_id_task,
        pr.name AS project_name_task,
        pr.community AS project_id_community,
        c.name AS project_name_community
      FROM project_task_files ptf
      LEFT JOIN users u ON ptf.userid = u.id
      LEFT JOIN project_tasks pt ON ptf.task_id = pt.id
      LEFT JOIN projects pr ON pt.project_id = pr.id
      LEFT JOIN community c ON pr.community = c.id
      WHERE pt.id IS NOT NULL 
        AND pr.id IS NOT NULL 
        AND pr.community IS NOT NULL
      ORDER BY ptf.ID DESC
    `);
    return results;
  } catch (error) {
    console.error('Error fetching project task files:', error);
    return [];
  }
};

const getAllStaffTaskFilesModal = async () => {
  try {
    const [results] = await sequelize.query(`
      SELECT 
        stf.ID AS id,
        stf.file_url AS name,
        stf.taskid,
        stf.extension,
        stf.file_size,
        stf.file_type,
        stf.userid AS added_by,
        CONCAT(u.first_name, ' ', u.last_name) AS added_by_name,
        u.profile_pic,
        stf.timestamp,
        1 AS status,
        st.name AS task_name
      FROM staff_task_files stf
      LEFT JOIN users u ON stf.userid = u.id
      LEFT JOIN staff_tasks st ON stf.taskid = st.id
      WHERE st.id IS NOT NULL
      ORDER BY stf.ID DESC
    `);
    return results;
  } catch (error) {
    console.error('Error fetching staff task files:', error);
    return [];
  }
};

const addRequestAttachmentImage = async (imageData) => {
  try {
    const data = { ...imageData };
    if (data.document_source == null) data.document_source = 'request';
    const result = await RequestAttachmentImages.create(data);
    return result;
  } catch (error) {
    console.error('Error adding request attachment image:', error);
    throw error;
  }
};

const getRequestAttachmentByIdForConversion = async (documentId) => {
  try {
    const doc = await RequestAttachments.findByPk(documentId);
    return doc;
  } catch (error) {
    console.error('Error getting request attachment by ID:', error);
    throw error;
  }
};

const getDesignOrderAttachmentByIdForConversion = async (documentId) => {
  try {
    const doc = await DesignOrderAttachments.findByPk(documentId);
    return doc;
  } catch (error) {
    console.error('Error getting design order attachment by ID:', error);
    throw error;
  }
};

const getProjectAttachmentByIdForConversion = async (documentId) => {
  try {
    const doc = await ProjectFiles.findByPk(documentId);
    return doc;
  } catch (error) {
    console.error('Error getting project attachment by ID:', error);
    throw error;
  }
};

const getEditedPdfAttachmentBySource = async (documentId, documentSource = 'request') => {
  try {
    if (documentSource === 'request') {
      return await RequestAttachments.findByPk(documentId);
    }
    if (documentSource === 'design_order') {
      return await DesignOrderAttachments.findByPk(documentId);
    }
    if (documentSource === 'estimate_project') {
      return await ProjectFiles.findByPk(documentId);
    }
    if (documentSource === 'split_estimate') {
      const rows = await sequelize.query(
        `SELECT id, url, file_type, status, timestamp
         FROM split_estimates_attachments
         WHERE id = :id
         LIMIT 1`,
        { replacements: { id: Number(documentId) }, type: sequelize.QueryTypes.SELECT }
      );
      return Array.isArray(rows) && rows.length ? rows[0] : null;
    }
    return null;
  } catch (error) {
    console.error('Error getting edited PDF attachment by source:', error);
    throw error;
  }
};

const updateEditedPdfAttachmentBySource = async ({
  documentId,
  documentSource = 'request',
  supabaseUrl,
  fileSize,
  existingFileSize,
  timestamp,
}) => {
  try {
    if (documentSource === 'request') {
      return await RequestAttachments.update(
        { name: supabaseUrl, file_size: fileSize || existingFileSize, timestamp },
        { where: { id: documentId } }
      );
    }
    if (documentSource === 'design_order') {
      return await DesignOrderAttachments.update(
        { name: supabaseUrl, file_size: fileSize || existingFileSize, timestamp },
        { where: { id: documentId } }
      );
    }
    if (documentSource === 'estimate_project') {
      return await ProjectFiles.update(
        { file_url: supabaseUrl, file_size: fileSize || existingFileSize, timestamp },
        { where: { ID: documentId } }
      );
    }
    if (documentSource === 'split_estimate') {
      return await sequelize.query(
        `UPDATE split_estimates_attachments
         SET url = :supabaseUrl, timestamp = :timestamp
         WHERE id = :documentId`,
        {
          replacements: { supabaseUrl, timestamp, documentId: Number(documentId) },
          type: sequelize.QueryTypes.UPDATE
        }
      );
    }
    return null;
  } catch (error) {
    console.error('Error updating edited PDF attachment by source:', error);
    throw error;
  }
};

const getRequestAttachmentImagesByDocumentId = async (documentId, documentSource = 'request') => {
  try {
    const images = await RequestAttachmentImages.findAll({
      where: { document_id: documentId, document_source: documentSource, status: 1 }
    });
    return images;
  } catch (error) {
    console.error('Error getting request attachment images by document ID:', error);
    throw error;
  }
};

const deleteRequestAttachmentImagesByDocumentId = async (documentId, documentSource = null) => {
  try {
    const where = { document_id: documentId };
    if (documentSource != null) where.document_source = documentSource;
    const result = await RequestAttachmentImages.destroy({ where });
    return result;
  } catch (error) {
    console.error('Error deleting request attachment images by document ID:', error);
    throw error;
  }
};

const updateRequestAttachmentImagesStatusByDocumentId = async (documentId, status, documentSource = 'request') => {
  try {
    const result = await RequestAttachmentImages.update(
      { status: status },
      { where: { document_id: documentId, document_source: documentSource } }
    );
    return result;
  } catch (error) {
    console.error('Error updating request attachment images status by document ID:', error);
    throw error;
  }
};

/**
 * Get request_attachments that are PDFs and have no rows in request_attachment_images (for backfill).
 * @param {number} limit - max records to return
 * @returns {Promise<Array>} rows from request_attachments
 */
const getRequestAttachmentsMissingImages = async (limit = 50) => {
  try {
    const safeLimit = Number(limit) > 0 ? Number(limit) : 50;
    const rows = await sequelize.query(
      `SELECT ra.id, ra.name, ra.extension, ra.file_type
       FROM request_attachments ra
       LEFT JOIN request_attachment_images ai ON ra.id = ai.document_id AND ai.status = 1 AND ai.document_source = 'request'
       WHERE ra.status != 0
         AND (ra.file_type = 'application/pdf' OR ra.file_type = 'pdf' OR LOWER(ra.extension) = 'pdf')
         AND ra.name IS NOT NULL AND TRIM(ra.name) != ''
       GROUP BY ra.id
       HAVING COUNT(ai.id) = 0
       ORDER BY ra.id ASC
       LIMIT :limit`,
      { replacements: { limit: safeLimit }, type: sequelize.QueryTypes.SELECT }
    );
    return Array.isArray(rows) ? rows : [];
  } catch (error) {
    console.error('Error fetching request attachments missing images:', error);
    return [];
  }
};

/**
 * Get design_order_attachments that are PDFs and have no rows in request_attachment_images (for backfill).
 * @param {number} limit - max records to return
 * @returns {Promise<Array>} rows from design_order_attachments
 */
const getDesignOrderAttachmentsMissingImages = async (limit = 50) => {
  try {
    const safeLimit = Number(limit) > 0 ? Number(limit) : 50;
    const rows = await sequelize.query(
      `SELECT doa.id, doa.name, doa.extension, doa.file_type
       FROM design_order_attachments doa
       LEFT JOIN request_attachment_images ai ON doa.id = ai.document_id AND ai.status = 1 AND ai.document_source = 'design_order'
       WHERE doa.status != 0
         AND (doa.file_type = 'application/pdf' OR doa.file_type = 'pdf' OR LOWER(doa.extension) = 'pdf')
         AND doa.name IS NOT NULL AND TRIM(doa.name) != ''
       GROUP BY doa.id
       HAVING COUNT(ai.id) = 0
       ORDER BY doa.id ASC
       LIMIT :limit`,
      { replacements: { limit: safeLimit }, type: sequelize.QueryTypes.SELECT }
    );
    return Array.isArray(rows) ? rows : [];
  } catch (error) {
    console.error('Error fetching design order attachments missing images:', error);
    return [];
  }
};

/**
 * Get split_estimates_attachments that are PDFs and have no rows in request_attachment_images (for backfill).
 * @param {number} limit - max records to return
 * @returns {Promise<Array>} rows from split_estimates_attachments
 */
const getSplitEstimateAttachmentsMissingImages = async (limit = 50) => {
  try {
    const safeLimit = Number(limit) > 0 ? Number(limit) : 50;
    const rows = await sequelize.query(
      `SELECT sea.id, sea.url AS name, sea.file_type
       FROM split_estimates_attachments sea
       LEFT JOIN request_attachment_images ai ON sea.id = ai.document_id AND ai.status = 1 AND ai.document_source = 'split_estimate'
       WHERE sea.status != 0
         AND (sea.file_type = 'application/pdf' OR sea.file_type = 'pdf' OR (sea.file_type IS NOT NULL AND LOWER(sea.file_type) LIKE '%pdf%'))
         AND sea.url IS NOT NULL AND TRIM(sea.url) != ''
       GROUP BY sea.id
       HAVING COUNT(ai.id) = 0
       ORDER BY sea.id ASC
       LIMIT :limit`,
      { replacements: { limit: safeLimit }, type: sequelize.QueryTypes.SELECT }
    );
    return Array.isArray(rows) ? rows : [];
  } catch (error) {
    console.error('Error fetching split estimate attachments missing images:', error);
    return [];
  }
};

/**
 * Get project_files that are PDFs and have no rows in request_attachment_images (for backfill of project attachments).
 * Uses document_source = 'estimate_project'.
 * @param {number} limit - max records to return
 * @returns {Promise<Array>} rows from project_files
 */
const getProjectAttachmentsMissingImages = async (limit = 50) => {
  try {
    const safeLimit = Number(limit) > 0 ? Number(limit) : 50;
    const rows = await sequelize.query(
      `SELECT pf.ID AS id, pf.file_url AS name, pf.extension, pf.file_type
       FROM project_files pf
       LEFT JOIN request_attachment_images ai 
         ON pf.ID = ai.document_id 
        AND ai.status = 1 
        AND ai.document_source = 'estimate_project'
       WHERE (
              pf.file_type = 'application/pdf' 
           OR pf.file_type = 'pdf'
           OR pf.file_type = 'other'
           OR LOWER(pf.extension) = 'pdf'
           OR LOWER(pf.upload_file_name) LIKE '%.pdf%'
           OR LOWER(pf.file_url) LIKE '%.pdf%'
       )
       GROUP BY pf.ID
       HAVING COUNT(ai.id) = 0
       ORDER BY pf.ID ASC
       LIMIT :limit`,
      { replacements: { limit: safeLimit }, type: sequelize.QueryTypes.SELECT }
    );
    return Array.isArray(rows) ? rows : [];
  } catch (error) {
    console.error('Error fetching project attachments missing images:', error);
    return [];
  }
};

module.exports = { RequestProposalAttachments, getAllRequestProposalAttachmentsModal, DesignOrderAttachments, getAllDesignOrderAttachmentsModal, RequestAttachments, getAllRequestAttachmentsModal, ProjectFiles, getAllProjectFilesModal, ProjectTaskFiles, getAllProjectTaskFilesModal, StaffTaskFiles, getAllStaffTaskFilesModal, RequestAttachmentImages, addRequestAttachmentImage, getRequestAttachmentByIdForConversion, getDesignOrderAttachmentByIdForConversion, getProjectAttachmentByIdForConversion, getEditedPdfAttachmentBySource, updateEditedPdfAttachmentBySource, getRequestAttachmentImagesByDocumentId, deleteRequestAttachmentImagesByDocumentId, updateRequestAttachmentImagesStatusByDocumentId, getRequestAttachmentsMissingImages, getDesignOrderAttachmentsMissingImages, getSplitEstimateAttachmentsMissingImages, getProjectAttachmentsMissingImages };
