<?php
/**
 * Database layer for personalliggare (employees and attendance events).
 *
 * @package Maca_Menu_List
 */

if (!defined('ABSPATH')) {
    exit;
}

// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.SchemaChange

/**
 * Employees table name.
 *
 * @return string
 */
function maca_menulist_db_employees_table() {
    global $wpdb;

    return $wpdb->prefix . 'maca_employees';
}

/**
 * Attendance events table name.
 *
 * @return string
 */
function maca_menulist_db_attendance_events_table() {
    global $wpdb;

    return $wpdb->prefix . 'maca_attendance_events';
}

/**
 * Create attendance tables.
 *
 * @return void
 */
function maca_menulist_db_create_attendance_tables() {
    global $wpdb;

    if (!isset($wpdb) || empty($wpdb)) {
        return;
    }

    $charset_collate = $wpdb->get_charset_collate();
    $employees_table = maca_menulist_db_employees_table();
    $events_table = maca_menulist_db_attendance_events_table();

    $sql_employees = "CREATE TABLE $employees_table (
        id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
        name varchar(255) NOT NULL,
        personal_number varchar(32) NOT NULL DEFAULT '',
        personal_number_type varchar(16) NOT NULL DEFAULT 'pnr',
        pin_hash varchar(255) NOT NULL DEFAULT '',
        is_active tinyint(1) NOT NULL DEFAULT 1,
        created_at datetime DEFAULT CURRENT_TIMESTAMP,
        updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY  (id),
        KEY is_active (is_active),
        KEY personal_number (personal_number)
    ) $charset_collate;";

    $sql_events = "CREATE TABLE $events_table (
        id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
        employee_id bigint(20) unsigned NOT NULL,
        event_type varchar(16) NOT NULL DEFAULT 'clock_in',
        occurred_at datetime NOT NULL,
        recorded_at datetime NOT NULL,
        source varchar(16) NOT NULL DEFAULT 'self',
        status varchar(16) NOT NULL DEFAULT 'active',
        voided_at datetime DEFAULT NULL,
        voided_by bigint(20) unsigned NOT NULL DEFAULT 0,
        created_by bigint(20) unsigned NOT NULL DEFAULT 0,
        PRIMARY KEY  (id),
        KEY employee_id (employee_id),
        KEY occurred_at (occurred_at),
        KEY status (status),
        KEY employee_occurred (employee_id, occurred_at)
    ) $charset_collate;";

    require_once ABSPATH . 'wp-admin/includes/upgrade.php';
    dbDelta($sql_employees);
    dbDelta($sql_events);

    if (function_exists('maca_menulist_db_create_attendance_log_table')) {
        maca_menulist_db_create_attendance_log_table();
    }
}

/**
 * Ensure attendance tables exist.
 *
 * @return void
 */
function maca_menulist_db_ensure_attendance_tables() {
    static $ensured = false;

    if ($ensured) {
        return;
    }

    global $wpdb;

    $employees_table = maca_menulist_db_employees_table();
    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
    $exists = $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $employees_table));

    if ($exists !== $employees_table) {
        maca_menulist_db_create_attendance_tables();
    } elseif (function_exists('maca_menulist_db_ensure_attendance_log_table')) {
        maca_menulist_db_ensure_attendance_log_table();
    }

    $ensured = true;
}

/**
 * Drop attendance tables.
 *
 * @return void
 */
function maca_menulist_db_drop_attendance_tables() {
    global $wpdb;

    foreach (array(
        maca_menulist_db_attendance_events_table(),
        maca_menulist_db_employees_table(),
    ) as $table) {
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange
        $wpdb->query("DROP TABLE IF EXISTS {$table}");
    }

    if (function_exists('maca_menulist_db_drop_attendance_log_table')) {
        maca_menulist_db_drop_attendance_log_table();
    }
}

/**
 * Get all employees.
 *
 * @param bool $active_only Only active employees.
 * @return array<int, object>
 */
function maca_menulist_db_get_employees($active_only = false) {
    global $wpdb;

    maca_menulist_db_ensure_attendance_tables();

    $table = maca_menulist_db_employees_table();
    $sql = "SELECT * FROM $table";

    if ($active_only) {
        $sql .= ' WHERE is_active = 1';
    }

    $sql .= ' ORDER BY name ASC';

    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
    $rows = $wpdb->get_results($sql);

    return is_array($rows) ? $rows : array();
}

/**
 * Get employee by ID.
 *
 * @param int $employee_id Employee ID.
 * @return object|null
 */
function maca_menulist_db_get_employee($employee_id) {
    global $wpdb;

    maca_menulist_db_ensure_attendance_tables();

    $employee_id = absint($employee_id);
    if ($employee_id <= 0) {
        return null;
    }

    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- Table name from $wpdb->prefix helper.
    $row = $wpdb->get_row(
        $wpdb->prepare(
            'SELECT * FROM ' . maca_menulist_db_employees_table() . ' WHERE id = %d',
            $employee_id
        )
    );

    return $row ? $row : null;
}

/**
 * Save employee row.
 *
 * @param array<string, mixed> $data Employee fields.
 * @return int Employee ID or 0 on failure.
 */
function maca_menulist_db_save_employee($data) {
    global $wpdb;

    maca_menulist_db_ensure_attendance_tables();

    $employee_id = isset($data['id']) ? absint($data['id']) : 0;
    $name = isset($data['name']) ? sanitize_text_field((string) $data['name']) : '';
    $personal_number = isset($data['personal_number']) ? sanitize_text_field((string) $data['personal_number']) : '';
    $personal_number_type = isset($data['personal_number_type']) ? sanitize_key((string) $data['personal_number_type']) : 'pnr';
    $is_active = !empty($data['is_active']);
    $pin_hash = isset($data['pin_hash']) ? (string) $data['pin_hash'] : '';

    if ($name === '') {
        return 0;
    }

    $allowed_types = array('pnr', 'coordination', 'foreign');
    if (!in_array($personal_number_type, $allowed_types, true)) {
        $personal_number_type = 'pnr';
    }

    $row = array(
        'name' => $name,
        'personal_number' => $personal_number,
        'personal_number_type' => $personal_number_type,
        'is_active' => $is_active ? 1 : 0,
    );

    $formats = array('%s', '%s', '%s', '%d');

    if ($pin_hash !== '') {
        $row['pin_hash'] = $pin_hash;
        $formats[] = '%s';
    }

    $table = maca_menulist_db_employees_table();

    if ($employee_id > 0) {
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $updated = $wpdb->update(
            $table,
            $row,
            array('id' => $employee_id),
            $formats,
            array('%d')
        );

        return $updated !== false ? $employee_id : 0;
    }

    if ($pin_hash === '') {
        return 0;
    }

    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
    $inserted = $wpdb->insert($table, $row, $formats);

    return $inserted ? (int) $wpdb->insert_id : 0;
}

/**
 * Insert an attendance event.
 *
 * @param array<string, mixed> $data Event fields.
 * @return int Event ID or 0.
 */
function maca_menulist_db_insert_attendance_event($data) {
    global $wpdb;

    maca_menulist_db_ensure_attendance_tables();

    $employee_id = isset($data['employee_id']) ? absint($data['employee_id']) : 0;
    $event_type = isset($data['event_type']) ? sanitize_key((string) $data['event_type']) : '';
    $occurred_at = isset($data['occurred_at']) ? sanitize_text_field((string) $data['occurred_at']) : '';
    $recorded_at = isset($data['recorded_at']) ? sanitize_text_field((string) $data['recorded_at']) : '';
    $source = isset($data['source']) ? sanitize_key((string) $data['source']) : 'self';
    $created_by = isset($data['created_by']) ? absint($data['created_by']) : 0;

    if ($employee_id <= 0 || !in_array($event_type, array('clock_in', 'clock_out'), true)) {
        return 0;
    }

    if ($occurred_at === '') {
        $occurred_at = function_exists('maca_menulist_wp_now_mysql') ? maca_menulist_wp_now_mysql() : current_time('mysql');
    }

    if ($recorded_at === '') {
        $recorded_at = $occurred_at;
    }

    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
    $inserted = $wpdb->insert(
        maca_menulist_db_attendance_events_table(),
        array(
            'employee_id' => $employee_id,
            'event_type' => $event_type,
            'occurred_at' => $occurred_at,
            'recorded_at' => $recorded_at,
            'source' => $source !== '' ? $source : 'self',
            'status' => 'active',
            'created_by' => $created_by,
        ),
        array('%d', '%s', '%s', '%s', '%s', '%s', '%d')
    );

    return $inserted ? (int) $wpdb->insert_id : 0;
}

/**
 * Latest active attendance event for an employee.
 *
 * @param int $employee_id Employee ID.
 * @return object|null
 */
function maca_menulist_db_get_latest_attendance_event($employee_id) {
    global $wpdb;

    maca_menulist_db_ensure_attendance_tables();

    $employee_id = absint($employee_id);
    if ($employee_id <= 0) {
        return null;
    }

    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- Table name from $wpdb->prefix helper.
    $row = $wpdb->get_row(
        $wpdb->prepare(
            'SELECT * FROM ' . maca_menulist_db_attendance_events_table()
            . " WHERE employee_id = %d AND status = 'active' ORDER BY occurred_at DESC, id DESC LIMIT 1",
            $employee_id
        )
    );

    return $row ? $row : null;
}

/**
 * Active attendance events for a calendar day (site timezone).
 *
 * @param string $date Date Y-m-d.
 * @return array<int, object>
 */
function maca_menulist_db_get_attendance_events_for_date($date) {
    global $wpdb;

    maca_menulist_db_ensure_attendance_tables();

    $date = sanitize_text_field($date);
    if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $date)) {
        $date = function_exists('maca_menulist_wp_today') ? maca_menulist_wp_today() : wp_date('Y-m-d');
    }

    $table = maca_menulist_db_attendance_events_table();
    $start = $date . ' 00:00:00';
    $end = $date . ' 23:59:59';

    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
    $rows = $wpdb->get_results(
        $wpdb->prepare(
            "SELECT * FROM $table WHERE status = 'active' AND occurred_at >= %s AND occurred_at <= %s ORDER BY occurred_at ASC, id ASC",
            $start,
            $end
        )
    );

    return is_array($rows) ? $rows : array();
}

/**
 * Active attendance events between two calendar dates (inclusive).
 *
 * @param string $date_from Start date Y-m-d.
 * @param string $date_to   End date Y-m-d.
 * @return array<int, object>
 */
function maca_menulist_db_get_attendance_events_between($date_from, $date_to) {
    global $wpdb;

    maca_menulist_db_ensure_attendance_tables();

    $date_from = sanitize_text_field($date_from);
    $date_to = sanitize_text_field($date_to);

    if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $date_from)) {
        $date_from = function_exists('maca_menulist_wp_today') ? maca_menulist_wp_today() : wp_date('Y-m-d');
    }

    if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $date_to)) {
        $date_to = $date_from;
    }

    if ($date_from > $date_to) {
        $swap = $date_from;
        $date_from = $date_to;
        $date_to = $swap;
    }

    $table = maca_menulist_db_attendance_events_table();
    $start = $date_from . ' 00:00:00';
    $end = $date_to . ' 23:59:59';

    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
    $rows = $wpdb->get_results(
        $wpdb->prepare(
            "SELECT * FROM $table WHERE status = 'active' AND occurred_at >= %s AND occurred_at <= %s ORDER BY occurred_at ASC, id ASC",
            $start,
            $end
        )
    );

    return is_array($rows) ? $rows : array();
}

/**
 * Active attendance events for one employee on a calendar day.
 *
 * @param int    $employee_id Employee ID.
 * @param string $date        Date Y-m-d.
 * @return array<int, object>
 */
function maca_menulist_db_get_attendance_events_for_employee_date($employee_id, $date) {
    $employee_id = absint($employee_id);
    $events = array();

    foreach (maca_menulist_db_get_attendance_events_for_date($date) as $event) {
        if ((int) $event->employee_id === $employee_id) {
            $events[] = $event;
        }
    }

    return $events;
}

/**
 * Get one attendance event by ID.
 *
 * @param int $event_id Event ID.
 * @return object|null
 */
function maca_menulist_db_get_attendance_event($event_id) {
    global $wpdb;

    maca_menulist_db_ensure_attendance_tables();

    $event_id = absint($event_id);
    if ($event_id <= 0) {
        return null;
    }

    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- Table name from $wpdb->prefix helper.
    $row = $wpdb->get_row(
        $wpdb->prepare(
            'SELECT * FROM ' . maca_menulist_db_attendance_events_table() . ' WHERE id = %d',
            $event_id
        )
    );

    return $row ? $row : null;
}

/**
 * Update occurred_at on an active event.
 *
 * @param int    $event_id    Event ID.
 * @param string $occurred_at MySQL datetime.
 * @return bool
 */
function maca_menulist_db_update_attendance_event_occurred_at($event_id, $occurred_at) {
    global $wpdb;

    maca_menulist_db_ensure_attendance_tables();

    $event_id = absint($event_id);
    $occurred_at = sanitize_text_field($occurred_at);

    if ($event_id <= 0 || $occurred_at === '') {
        return false;
    }

    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
    $updated = $wpdb->update(
        maca_menulist_db_attendance_events_table(),
        array('occurred_at' => $occurred_at),
        array(
            'id' => $event_id,
            'status' => 'active',
        ),
        array('%s'),
        array('%d', '%s')
    );

    return $updated !== false;
}

/**
 * Soft-delete an attendance event.
 *
 * @param int $event_id  Event ID.
 * @param int $voided_by User ID.
 * @return bool
 */
function maca_menulist_db_void_attendance_event($event_id, $voided_by = 0) {
    global $wpdb;

    maca_menulist_db_ensure_attendance_tables();

    $event_id = absint($event_id);
    if ($event_id <= 0) {
        return false;
    }

    $now = function_exists('maca_menulist_wp_now_mysql') ? maca_menulist_wp_now_mysql() : current_time('mysql');

    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
    $updated = $wpdb->update(
        maca_menulist_db_attendance_events_table(),
        array(
            'status' => 'voided',
            'voided_at' => $now,
            'voided_by' => absint($voided_by),
        ),
        array(
            'id' => $event_id,
            'status' => 'active',
        ),
        array('%s', '%s', '%d'),
        array('%d', '%s')
    );

    return $updated !== false;
}

// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.SchemaChange

