<?php
/**
 * Custom table definitions created through dbDelta().
 *
 * Datetimes are stored in the site's local timezone (wp_timezone()).
 *
 * @package Cleannetic
 */

namespace Cleannetic\Database;

if ( ! defined( 'ABSPATH' ) ) {
    exit;
}

class Schema {

    public static function bookings_table(): string {
        global $wpdb;
        return $wpdb->prefix . 'cleannetic_bookings';
    }

    public static function customers_table(): string {
        global $wpdb;
        return $wpdb->prefix . 'cleannetic_customers';
    }

    public static function payments_table(): string {
        global $wpdb;
        return $wpdb->prefix . 'cleannetic_payments';
    }

    public static function services_table(): string {
        global $wpdb;
        return $wpdb->prefix . 'cleannetic_services';
    }

    public static function categories_table(): string {
        global $wpdb;
        return $wpdb->prefix . 'cleannetic_categories';
    }

    /**
     * @return string[]
     */
    public static function all_tables(): array {
        return [
            self::bookings_table(),
            self::customers_table(),
            self::payments_table(),
            self::services_table(),
            self::categories_table(),
        ];
    }

    public static function tables_exist(): bool {
        global $wpdb;

        foreach ( [ self::services_table(), self::categories_table() ] as $table ) {
            $found = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery
            if ( $found !== $table ) {
                return false;
            }
        }

        return true;
    }

    public static function create_tables(): void {
        global $wpdb;

        require_once ABSPATH . 'wp-admin/includes/upgrade.php';

        $charset_collate = $wpdb->get_charset_collate();
        $bookings        = self::bookings_table();
        $customers       = self::customers_table();
        $payments        = self::payments_table();
        $services        = self::services_table();
        $categories      = self::categories_table();

        // dbDelta() requires two spaces before PRIMARY KEY and one field per line.
        $sql = "CREATE TABLE {$bookings} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            booking_ref varchar(32) NOT NULL,
            customer_id bigint(20) unsigned NOT NULL DEFAULT 0,
            service_id bigint(20) unsigned NOT NULL DEFAULT 0,
            staff_id bigint(20) unsigned DEFAULT NULL,
            start_datetime datetime NOT NULL,
            end_datetime datetime NOT NULL,
            duration_minutes int(11) unsigned NOT NULL DEFAULT 0,
            status varchar(20) NOT NULL DEFAULT 'pending',
            pricing_breakdown longtext NULL,
            total_amount decimal(12,2) NOT NULL DEFAULT 0.00,
            deposit_paid decimal(12,2) NOT NULL DEFAULT 0.00,
            payment_status varchar(20) NOT NULL DEFAULT 'unpaid',
            payment_method varchar(50) NOT NULL DEFAULT 'cash',
            is_recurring tinyint(1) NOT NULL DEFAULT 0,
            recurrence_cycle varchar(20) DEFAULT NULL,
            address_data text NOT NULL,
            notes text NULL,
            admin_notes text NULL,
            source varchar(20) NOT NULL DEFAULT 'frontend',
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY  (id),
            UNIQUE KEY booking_ref (booking_ref),
            KEY customer_id (customer_id),
            KEY service_id (service_id),
            KEY staff_id (staff_id),
            KEY start_datetime (start_datetime),
            KEY status (status)
        ) {$charset_collate};

        CREATE TABLE {$customers} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            wp_user_id bigint(20) unsigned DEFAULT NULL,
            first_name varchar(100) NOT NULL DEFAULT '',
            last_name varchar(100) NOT NULL DEFAULT '',
            email varchar(191) NOT NULL DEFAULT '',
            phone varchar(50) NOT NULL DEFAULT '',
            address text NULL,
            property_notes text NULL,
            gate_code varchar(100) DEFAULT NULL,
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY  (id),
            KEY email (email),
            KEY phone (phone),
            KEY wp_user_id (wp_user_id)
        ) {$charset_collate};

        CREATE TABLE {$payments} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            booking_id bigint(20) unsigned NOT NULL,
            transaction_id varchar(191) NOT NULL DEFAULT '',
            gateway varchar(50) NOT NULL DEFAULT 'cash',
            amount decimal(12,2) NOT NULL DEFAULT 0.00,
            currency varchar(10) NOT NULL DEFAULT 'USD',
            payment_type varchar(20) NOT NULL DEFAULT 'full',
            status varchar(20) NOT NULL DEFAULT 'completed',
            notes text NULL,
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY  (id),
            KEY booking_id (booking_id)
        ) {$charset_collate};

        CREATE TABLE {$categories} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            name varchar(191) NOT NULL DEFAULT '',
            slug varchar(191) NOT NULL DEFAULT '',
            description text NULL,
            icon varchar(50) NOT NULL DEFAULT 'sparkles',
            color varchar(20) NOT NULL DEFAULT '#0D9488',
            sort_order int(11) 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 slug (slug),
            KEY status (status),
            KEY sort_order (sort_order)
        ) {$charset_collate};

        CREATE TABLE {$services} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            category_id bigint(20) unsigned NOT NULL DEFAULT 0,
            title varchar(191) NOT NULL DEFAULT '',
            description text NULL,
            icon varchar(50) NOT NULL DEFAULT 'sparkles',
            badge varchar(100) NOT NULL DEFAULT '',
            mode varchar(20) NOT NULL DEFAULT 'fixed',
            pricing_model varchar(20) NOT NULL DEFAULT 'unit',
            base_price decimal(12,2) NOT NULL DEFAULT 0.00,
            base_duration int(11) unsigned NOT NULL DEFAULT 60,
            enable_beds tinyint(1) NOT NULL DEFAULT 0,
            bed_price decimal(12,2) NOT NULL DEFAULT 0.00,
            enable_baths tinyint(1) NOT NULL DEFAULT 0,
            bath_price decimal(12,2) NOT NULL DEFAULT 0.00,
            enable_sqft tinyint(1) NOT NULL DEFAULT 0,
            sqft_price decimal(12,4) NOT NULL DEFAULT 0.0000,
            enable_windows tinyint(1) NOT NULL DEFAULT 0,
            window_price decimal(12,2) NOT NULL DEFAULT 0.00,
            enable_hours tinyint(1) NOT NULL DEFAULT 0,
            hourly_rate decimal(12,2) NOT NULL DEFAULT 0.00,
            addons longtext NULL,
            status varchar(20) NOT NULL DEFAULT 'active',
            sort_order int(11) NOT NULL DEFAULT 0,
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY  (id),
            KEY category_id (category_id),
            KEY status (status),
            KEY sort_order (sort_order)
        ) {$charset_collate};";

        dbDelta( $sql );
    }

    public static function drop_tables(): void {
        global $wpdb;

        foreach ( self::all_tables() as $table_name ) {
            $wpdb->query( $wpdb->prepare( 'DROP TABLE IF EXISTS %i', $table_name ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- Dropping the plugin's own custom tables has no WordPress API equivalent.
        }
    }
}