import { Injectable } from '@nestjs/common';
import { InjectDataSource } from '@nestjs/typeorm';
import { DataSource } from 'typeorm';
import { AddPackageCategoriesDto, EditPackageCategoriesDto } from './dtos/add-package-categories.dto';

@Injectable()
export class PackageCategoriesService {
  constructor(
    private readonly connection: DataSource,
    @InjectDataSource('AdminConnection')
    private readonly dynamicConnection: DataSource,
  ) { }
  async create(addPackageCategoriesDto: AddPackageCategoriesDto) {

    try {
     addPackageCategoriesDto.created_source = addPackageCategoriesDto.created_source || 'hms';
      const { name, hospital_id,created_by,created_source } = addPackageCategoriesDto;
      if (!name) {
        return {
          status: 'failed',
          message: 'Name is required',
        };
      }

      const [isPackages] = await this.connection.query(
        `SELECT * FROM package_categories WHERE name = ? AND status='1'`,
        [name],
      );
      if (isPackages) {
        return {
          status: 'failed',
          message:
            'Package category name already exists please try another name',
        };
      }

      const hos_packages = await this.connection.query(
        `INSERT INTO package_categories (name,created_by,created_source,created_at,status) VALUES (?,?,?,now(),'1')`,
        [name,created_by,created_source],
      );


      if (hos_packages.insertId) {
         const [hos_staff] = await this.connection.query(`
        SELECT * FROM staff WHERE id = ?`, [Number(addPackageCategoriesDto.created_by)])
      const [admin_staff] = await this.dynamicConnection.query(`
          SELECT * FROM staff WHERE email=?`,[hos_staff.email])
        await this.dynamicConnection.query(
          `INSERT INTO package_categories (name,hos_package_category_id,hospital_id,created_by,created_source,created_at,status) VALUES (?,?,?,?,?,now(),'1')`,
          [name, hos_packages.insertId, hospital_id,admin_staff.id,created_source],
        );
        const results = await this.connection.query(
          `SELECT * FROM package_categories WHERE id = ? AND status='1'`,
          [hos_packages.insertId],
        );

        return {
          data: results[0],
          status: 'success',
          message: 'Package category added successfully',
        };
      }
      return {
        status: 'failed',
        message: 'Failed to add package category',
      };
    } catch (error) {
      return {
        status: 'failed',
        message: 'Failed to add package category',
      };
    }
  }

  async update(id: number, editPackageCategoriesDto: EditPackageCategoriesDto) {
    try {
      const { name, hospital_id } =
        editPackageCategoriesDto;
      if (!name) {
        return {
          status: 'failed',
          message: 'Name is required',
        };
      }
      const [isPackages] = await this.connection.query(
        `SELECT * FROM package_categories WHERE id = ? AND status='1'`,
        [id],
      );
      if (!isPackages) {
        return {
          status: 'failed',
          message: 'Package category not found',
        };
      }
      const [isNameExist] = await this.connection.query(
        `SELECT * FROM package_categories WHERE id != ? AND name = ? AND status='1'`,
        [id, name],
      );
      if (isNameExist) {
        return {
          status: 'failed',
          message: 'Package category name already exists',
        };
      }

      const isUpdated = await this.connection.query(
        `UPDATE package_categories SET name = ?  WHERE id = ?`,
        [name, id],
      );

      if (isUpdated.affectedRows > 0) {
        const [hos_packages] = await this.dynamicConnection.query(`
          SELECT id FROM package_categories WHERE hos_package_category_id	= ? AND hospital_id = ?`, [Number(id), hospital_id])
        await this.dynamicConnection.query(
          `UPDATE package_categories SET name = ?  WHERE id = ?`,
          [name, hos_packages.id],
        );
        const [results] = await this.connection.query(
          `SELECT * FROM package_categories WHERE id = ? AND status='1'`,
          [id],
        );
        return {
          data: results,
          status: 'success',
          message: 'Package category updated successfully',
        };
      }
      return {
        status: 'failed',
        message: 'Failed to update package category',
      };
    } catch (error) {
      return {
        status: 'failed',
        message: 'Failed to update package category',
      };
    }
  }

  async findAll(search?: string, page: number = 1, limit: number = 10) {
    try {
      const safeLimit = Math.max(1, Number(limit) || 10);
      const safePage = Math.max(1, Number(page) || 1);
      const offset = (safePage - 1) * safeLimit;

      let where = '';
      const params: any[] = [];
      if (search && search.trim()) {
        where = 'AND name LIKE ?';
        params.push(`%${search.trim()}%`);
      }

      const countRes: any[] = await this.connection.query(
        `SELECT COUNT(*) as total FROM package_categories WHERE status='1' ${where}`,
        params,
      );
      const total = Number(countRes[0]?.total || 0);

      const data = await this.connection.query(
        `SELECT id, name, created_at
         FROM package_categories
         WHERE status='1'
         ${where}
         ORDER BY id DESC
         LIMIT ? OFFSET ?`,
        [...params, safeLimit, offset],
      );

      return {
        response: {
          data,
          total
        },
        status: 'success',
        message: 'Package category fetched successfully',
      };
    } catch (err) {
      return {
        status: 'failed',
        message: 'Failed to fetch package categories',
      };
    }
  }

  async findById(id: number) {
    try {
      const [result] = await this.connection.query(
        `SELECT * FROM package_categories WHERE id = ? AND status='1'`,
        [id],
      );

      if (!result) {
        return { status: 'failed', message: 'Package category not found' };
      }

      return {
        data: result,
        status: 'success',
        message: 'Package category fetched successfully',
      };
    } catch (error) {
      return { status: 'failed', message: 'Failed to fetch package category' };
    }
  }

  async delete(id: number, hospital_id: number) {
    try {
      if (!id) {
        return { status: 'failed', message: 'Invalid id' };
      }

      const res: any = await this.connection.query(
        `UPDATE package_categories SET status='0' WHERE id = ? AND status='1'`,
        [id],
      );

      if (res && (res.affectedRows === 1 || res.affectedRows > 0)) {
        const [hos_packages] = await this.dynamicConnection.query(`
          SELECT id FROM package_categories WHERE hos_package_category_id	= ? AND hospital_id = ?`, [Number(id), hospital_id])
        const res: any = await this.dynamicConnection.query(
          `UPDATE package_categories SET status='0' WHERE id = ? AND status='1'`,
          [hos_packages.id],
        );
        return {
          status: 'success',
          message: 'Package category deleted successfully',
        };
      }

      return {
        status: 'failed',
        message: `data not found of ${id} package category`,
      };
    } catch (err) {
      return { status: 'failed', message: 'Failed to delete package category' };
    }
  }
}