<?php

/**
 * Migration 0037 — staff / team model (Phase 1)
 *
 * Introduces a first-class team-member concept so an appointment can be
 * assigned to a PERSON, not just a service. Additive and backward-compatible:
 * every new column/table is optional. A shop with zero staff rows behaves
 * exactly as before (service-level capacity + shop hours); the staff layer
 * only engages once staff exist.
 *
 *   - instinctor_staff          : the team members (name, title, photo,
 *                                 commission %, bookable flag, optional login).
 *   - instinctor_staff_hours    : per-staff weekly working hours (one row per
 *                                 block; multiple rows/day = splits; no row for
 *                                 a day = off that day).
 *   - instinctor_staff_timeoff  : date-range blocks (vacation, sick, holds).
 *   - instinctor_service_staff  : which staff perform which service, with an
 *                                 optional per-pair price/duration override.
 *                                 A service with no rows here = any bookable
 *                                 staff can perform it.
 *   - instinctor_bookings       : + staff_id (nullable; NULL = any/unassigned)
 *                                 + commission_amount (snapshot at completion).
 *
 * Idempotent: CREATE TABLE IF NOT EXISTS + INFORMATION_SCHEMA-guarded ALTERs,
 * same pattern as 0031 / 0032 / 0036.
 */

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

return [
    'description' => 'Create staff/team tables + add staff_id & commission_amount to bookings',

    'up' => [
        "CREATE TABLE IF NOT EXISTS {prefix}instinctor_staff (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            name varchar(191) NOT NULL,
            title varchar(191) NOT NULL DEFAULT '',
            bio text NULL DEFAULT NULL,
            photo_url varchar(500) NOT NULL DEFAULT '',
            calendar_color varchar(9) NOT NULL DEFAULT '',
            commission_rate decimal(5,2) NOT NULL DEFAULT 0.00,
            bookable tinyint(1) NOT NULL DEFAULT 1,
            user_id bigint(20) unsigned NULL DEFAULT NULL,
            sort_order int NOT NULL DEFAULT 0,
            status varchar(20) NOT NULL DEFAULT 'active',
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY status (status),
            KEY bookable (bookable),
            KEY user_id (user_id)
        ) {charset}",

        "CREATE TABLE IF NOT EXISTS {prefix}instinctor_staff_hours (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            staff_id bigint(20) unsigned NOT NULL,
            day_of_week tinyint(1) NOT NULL,
            start_time time NOT NULL,
            end_time time NOT NULL,
            PRIMARY KEY (id),
            KEY staff_day (staff_id, day_of_week)
        ) {charset}",

        "CREATE TABLE IF NOT EXISTS {prefix}instinctor_staff_timeoff (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            staff_id bigint(20) unsigned NOT NULL,
            start_datetime datetime NOT NULL,
            end_datetime datetime NOT NULL,
            reason varchar(191) NOT NULL DEFAULT '',
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY staff_range (staff_id, start_datetime, end_datetime)
        ) {charset}",

        "CREATE TABLE IF NOT EXISTS {prefix}instinctor_service_staff (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            service_id bigint(20) unsigned NOT NULL,
            staff_id bigint(20) unsigned NOT NULL,
            price_override decimal(10,2) NULL DEFAULT NULL,
            duration_override int NULL DEFAULT NULL,
            PRIMARY KEY (id),
            UNIQUE KEY service_staff (service_id, staff_id),
            KEY staff_id (staff_id)
        ) {charset}",
    ],

    'php' => function () {
        $t = \Instinctor\Core\DB::prefix() . 'instinctor_bookings';

        $add = [
            'staff_id'          => "ADD COLUMN staff_id bigint(20) unsigned NULL DEFAULT NULL AFTER product_id",
            'commission_amount' => "ADD COLUMN commission_amount decimal(10,2) NULL DEFAULT NULL AFTER staff_id",
        ];
        foreach ($add as $col => $ddl) {
            $exists = \Instinctor\Core\DB::get_var(\Instinctor\Core\DB::prepare(
                "SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
                 WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s AND COLUMN_NAME = %s",
                $t, $col
            ));
            if (!$exists) {
                \Instinctor\Core\DB::query("ALTER TABLE {$t} {$ddl}");
                if (\Instinctor\Core\DB::last_error() !== '') {
                    throw new \RuntimeException(esc_html("bookings.{$col} add failed: " . \Instinctor\Core\DB::last_error()));
                }
            }
        }

        // Composite index for staff availability lookups (staff's bookings on a day).
        $idx = \Instinctor\Core\DB::get_var(\Instinctor\Core\DB::prepare(
            "SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
             WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s AND INDEX_NAME = 'staff_time'",
            $t
        ));
        if (!$idx) {
            \Instinctor\Core\DB::query("ALTER TABLE {$t} ADD KEY staff_time (staff_id, start_time)");
        }
    },

    'down' => [
        "DROP TABLE IF EXISTS {prefix}instinctor_service_staff",
        "DROP TABLE IF EXISTS {prefix}instinctor_staff_timeoff",
        "DROP TABLE IF EXISTS {prefix}instinctor_staff_hours",
        "DROP TABLE IF EXISTS {prefix}instinctor_staff",
        "ALTER TABLE {prefix}instinctor_bookings DROP COLUMN IF EXISTS commission_amount",
        "ALTER TABLE {prefix}instinctor_bookings DROP COLUMN IF EXISTS staff_id",
    ],
];
