<?php

namespace HelpGent\Database\Migrations\V224;

defined( 'ABSPATH' ) || exit;

use HelpGent\Database\Migrations\CreateDB;
use HelpGent\Database\Migrations\Query;

class ConstraintsMigration {
    public static function migrate_constraints(): bool {
        helpgent_run_with_no_echo(
            function () {
                $db = new CreateDB();

                $db->set_db_prefix();

                self::migrate_nullable_columns( $db->db_prefix );
                self::migrate_constraint_columns( $db->db_prefix );

                $db->add_constraints();
            },
            [],
            null,
            true
        );

        return false;
    }

    public static function migrate_nullable_columns( string $db_prefix ) {
        global $wpdb;

        $tables = [
            'responses'              => [
                'created_by' => 'BIGINT UNSIGNED NULL',
            ],
            'contact_info'           => [
                'answer_id' => 'BIGINT UNSIGNED NULL',
                'form_id'   => 'BIGINT UNSIGNED NULL',
            ],
            'open_ended_text_inners' => [
                'answer_id' => 'BIGINT UNSIGNED NULL',
            ],
        ];

        foreach ( $tables as $table => $columns ) {
            $table_name = "{$db_prefix}{$table}";

            if ( ! Query::table_exists( $table, $db_prefix ) ) {
                continue;
            }

            foreach ( $columns as $column => $definition ) {
                $wpdb->query( $wpdb->prepare( "ALTER TABLE %s MODIFY COLUMN %s %s", $table_name, $column, $definition ) );
            }
        }
    }

    public static function migrate_constraint_columns( string $db_prefix ) {
        global $wpdb;

        self::migrate_response_constraint_columns( $db_prefix );
        self::migrate_contact_info_constraint_columns( $db_prefix );
        self::migrate_open_ended_text_inners_constraint_columns( $db_prefix );
        self::migrate_response_attachment_constraint_columns( $db_prefix );
    }

    public static function migrate_response_constraint_columns( string $db_prefix ): void {
        global $wpdb;

        if ( Query::table_exists( "users", $db_prefix ) ) {
            $wpdb->query( $wpdb->prepare( "UPDATE %s SET created_by = NULL WHERE created_by NOT IN ( SELECT id FROM %s )", "{$db_prefix}responses", "{$db_prefix}users" ) );
            return;
        }

        $wpdb->query( $wpdb->prepare( "UPDATE %s SET created_by = NULL WHERE created_by = %d", "{$db_prefix}responses", 0 ) );
    }

    public static function migrate_contact_info_constraint_columns( string $db_prefix ): void {
        global $wpdb;

        $wpdb->query( $wpdb->prepare( "UPDATE %s SET answer_id = NULL WHERE answer_id NOT IN ( SELECT id FROM %s )", "{$db_prefix}contact_info", "{$db_prefix}element_answers" ) );
        $wpdb->query( $wpdb->prepare( "UPDATE %s SET form_id = NULL WHERE form_id NOT IN ( SELECT id FROM %s )", "{$db_prefix}contact_info", "{$db_prefix}forms" ) );
    }

    public static function migrate_open_ended_text_inners_constraint_columns( string $db_prefix ): void {
        global $wpdb;

        $wpdb->query( $wpdb->prepare( "UPDATE %s SET answer_id = NULL WHERE answer_id NOT IN ( SELECT id FROM %s )", "{$db_prefix}open_ended_text_inners", "{$db_prefix}element_answers" ) );
    }

    public static function migrate_response_attachment_constraint_columns( string $db_prefix ): void {
        global $wpdb;

        $wpdb->query( $wpdb->prepare( "DELETE FROM %s WHERE attachment_id NOT IN ( SELECT id FROM %s )", "{$db_prefix}response_attachment", "{$db_prefix}attachments" ) );
    }
}