from __future__ import annotations

import logging
from datetime import date
from typing import Any

logger = logging.getLogger(__name__)


class ErpTimesheetMixin:
    def list_timesheets(
        self, project_id: str | None = None, work_date: date | None = None
    ) -> list[dict[str, Any]]:
        """Lấy bảng chấm công nhân sự theo dự án và ngày làm việc."""
        sql = """
            SELECT t.*, emp.full_name as employee_name, emp.employee_code, p.project_name as project_name
            FROM erp_timesheets t
            JOIN employees emp ON t.employee_id = emp.id
            JOIN projects p ON t.project_id = p.id
            WHERE 1=1
        """
        params: list[Any] = []
        if project_id:
            sql += " AND t.project_id = %s"
            params.append(project_id)
        if work_date:
            sql += " AND t.work_date = %s"
            params.append(work_date)
        sql += " ORDER BY t.work_date DESC, emp.full_name ASC;"

        with self.get_connection() as conn, conn.cursor() as cur:
            cur.execute(sql, params)
            return cur.fetchall()

    def record_timesheet(self, payload: dict[str, Any]) -> dict[str, Any]:
        """Ghi nhận chấm công cho nhân sự theo ca."""
        sql = """
            INSERT INTO erp_timesheets (
                company_id, employee_id, project_id, work_date, shift_type,
                hours_worked, daily_wage_rate, labor_cost_allocated,
                attendance_status, location_verified, notes
            ) VALUES (
                %(company_id)s, %(employee_id)s, %(project_id)s, %(work_date)s, %(shift_type)s,
                %(hours_worked)s, %(daily_wage_rate)s, %(labor_cost_allocated)s,
                %(attendance_status)s, %(location_verified)s, %(notes)s
            )
            ON CONFLICT (employee_id, project_id, work_date, shift_type)
            DO UPDATE SET
                hours_worked = EXCLUDED.hours_worked,
                labor_cost_allocated = EXCLUDED.labor_cost_allocated,
                attendance_status = EXCLUDED.attendance_status,
                location_verified = EXCLUDED.location_verified,
                notes = EXCLUDED.notes
            RETURNING *;
        """
        with self.get_connection() as conn, conn.cursor() as cur:
            cur.execute(sql, payload)
            conn.commit()
            return cur.fetchone()

    def list_employees(self, department: str | None = None) -> list[dict[str, Any]]:
        """Lấy danh sách nhân viên công ty."""
        sql = """
            SELECT e.*, c.display_name as company_name
            FROM employees e
            JOIN companies c ON e.company_id = c.id
            WHERE e.employment_status = 'active'
        """
        params: list[Any] = []
        if department:
            sql += " AND e.department = %s"
            params.append(department)
        sql += " ORDER BY e.employee_code ASC;"

        with self.get_connection() as conn, conn.cursor() as cur:
            cur.execute(sql, params)
            return cur.fetchall()