<?php

namespace Sibs\Stargate\Models;

use Sibs\Stargate\Constants\Plugin;
use Sibs\Stargate\Loggers\SibsLogger;
use Sibs\Stargate\Traits\SuppressLogTrait;

if (!defined('ABSPATH')) {
    exit; // Prevent direct access
}

abstract class SibsModels
{
    use SuppressLogTrait;

    /**
     * Table Name.
     *
     * @var string
     */
    protected $table_name;

    /**
     * Primary Key.
     *
     * @var string
     */
    protected $primary_key;

    /**
     * Database.
     *
     * @var \wpdb
     */
    protected $db;

    /**
     * is SIBS Table.
     *
     * @var bool
     */
    protected $is_sibs_table;

    /**
     * Constructor.
     *
     * @param bool $is_sibs_table (optional, default: true)
     */
    public function __construct(
        string $table_name,
        string $primary_key = 'id',
        bool $is_sibs_table = true
    ) {
        global $wpdb;

        $this->db = $wpdb;
        $this->table_name = $table_name;
        $this->primary_key = $primary_key;
        $this->is_sibs_table = $is_sibs_table;
        $this->setSuppressLog(false);
    }

    /**
     * Get Prefix.
     */
    public function getPrefix(): string
    {
        return $this->db->prefix;
    }

    /**
     * Get Module Prefix.
     */
    public function getModulePrefix(): string
    {
        if ($this->is_sibs_table) {
            return $this->getPrefix().Plugin::PLUGIN_PREFIX.'_';
        }

        return $this->getPrefix();
    }

    /**
     * Get Table Name.
     *
     * @return bool
     */
    abstract public function getTableName(): string;

    /**
     * Get Config Name.
     *
     * @return bool
     */
    abstract public function getConfigName(): string;

    /**
     * Create Model.
     */
    abstract public function create(): bool;

    /**
     * Migrate Model.
     */
    abstract public function migrate(string $installedVersion): bool;

    /**
     * Check.
     */
    abstract public function check(bool $suppressLog = false): bool;

    /**
     * Table Exists.
     */
    public function table_exists(): bool
    {
        $tableName = $this->getTableName();
        $placeholder = $this->get_placeholder($tableName);
        $query = $this->db->prepare("SHOW TABLES LIKE {$placeholder}", esc_sql($tableName));
        $table_exists = $this->db->get_var($query);

        return !empty($table_exists);
    }

    /**
     * Is table created.
     */
    public function is_table_created(array $result, string $table): bool
    {
        return !empty($result) && isset($result["{$table}"]) && false !== strpos($result["{$table}"], 'Created');
    }

    /**
     * Insert a new record into the database.
     *
     * @param array $data  key-value pairs of column names and values
     * @param array $where key-value pairs for the WHERE clause
     *
     * @return false|int inserted row ID on success, false on failure
     */
    public function insert($data, $where = [])
    {
        $result = !empty($where)
            ? $this->db->insert($this->getTableName(), array_merge($data, $where))
            : $this->db->insert($this->getTableName(), $data);

        if (false === $result) {
            $this->logDbError('Insert failed', $data);
        }

        return $this->db->insert_id;
    }

    /**
     * Update a record in the database.
     *
     * @param array $data  key-value pairs of column names and values to update
     * @param array $where key-value pairs for the WHERE clause
     *
     * @return false|int number of rows affected, or false on failure
     */
    public function update($data, $where = [])
    {
        if (empty($where)) {
            $this->logDbError('Update failed: no WHERE clause provided', ['data' => $data]);

            return false; // Prevent mass updates
        }

        $result = $this->db->update($this->getTableName(), $data, $where);

        if (false === $result) {
            $this->logDbError('Update failed', ['data' => $data, 'where' => $where]);
        }

        return $result;
    }

    /**
     * Upsert a record in the database.
     *
     * @param array $data  key-value pairs of column names and values to upsert
     * @param array $where key-value pairs for the WHERE clause
     *
     * @return false|int number of rows affected, or false on failure
     */
    public function upsert($data, $where = [])
    {
        if (!empty($where)) {
            // Check if a record matching the WHERE conditions exists
            $where_clauses = [];
            $values = [];

            foreach ($where as $column => $value) {
                $placeholder = $this->get_placeholder($value);
                $where_clauses[] = "{$column} = {$placeholder}";
                $values[] = $value;
            }

            $query = "SELECT COUNT(*) FROM {$this->getTableName()} WHERE ".implode(' AND ', $where_clauses);
            $query = $this->db->prepare($query, $values);

            $exists = (bool) (((int) $this->db->get_var($query)) > 0);

            if ($exists) {
                // Update the record if it exists
                return $this->update($data, $where);
            }
        }

        // Insert a new record if it doesn't exist or $where is empty
        return $this->insert($data, $where);
    }

    /**
     * Retrieve a record by its primary key.
     *
     * @param mixed $id the primary key value
     *
     * @return null|object the retrieved row as an object, or null if not found
     */
    public function get($id)
    {
        $placeholder = $this->get_placeholder($id);

        $query = $this->db->prepare(
            "SELECT * FROM {$this->getTableName()} WHERE {$this->primary_key} = {$placeholder} LIMIT 1",
            $id
        );

        $result = $this->db->get_row($query, ARRAY_A);

        if (null === $result && !empty($this->db->last_error)) {
            $this->logDbError('Get failed', ['id' => $id]);
        }

        return $result;
    }

    /**
     * Retrieve a record by a value.
     *
     * @param string $field the field value
     * @param mixed  $value the value of the field value
     *
     * @return null|object the retrieved row as an object, or null if not found
     */
    public function get_by($field, $value)
    {
        $placeholder = $this->get_placeholder($value);

        $query = $this->db->prepare(
            "SELECT * FROM {$this->getTableName()} WHERE {$field} = {$placeholder} ORDER BY {$this->primary_key} DESC LIMIT 1",
            $value
        );

        $result = $this->db->get_row($query, ARRAY_A);

        if (null === $result && !empty($this->db->last_error)) {
            $this->logDbError('Get by failed', ['field' => $field, 'value' => $value]);
        }

        return $result;
    }

    /**
     * Retrieve multiple records with optional conditions.
     *
     * @param array  $conditions optional WHERE conditions as key-value pairs
     * @param string $order_by   optional column to order by
     * @param string $order      optional order direction (ASC/DESC)
     *
     * @return null|array array of results as objects, or null if no rows found
     */
    public function get_all($conditions = [], $order_by = '', $order = 'ASC')
    {
        $query = "SELECT * FROM {$this->getTableName()}";

        if (!empty($conditions)) {
            $where_clauses = [];
            $values = [];

            foreach ($conditions as $column => $condition) {
                $where_clauses[] = $this->build_where_clause($column, $condition, $values);
            }

            $query .= ' WHERE '.implode(' AND ', $where_clauses);
            $query = $this->db->prepare($query, $values);
        }

        if ($order_by) {
            $query .= " ORDER BY {$order_by} ".('DESC' === strtoupper($order) ? 'DESC' : 'ASC');
        }

        $results = $this->db->get_results($query, ARRAY_A);

        if (null === $results && !empty($this->db->last_error)) {
            $this->logDbError('Get all failed', ['conditions' => $conditions, 'order_by' => $order_by, 'order' => $order]);
        }

        return $results;
    }

    /**
     * Delete a record by its primary key or conditions.
     *
     * @param array $where key-value pairs for the WHERE clause
     *
     * @return false|int number of rows deleted, false on failure
     */
    public function delete($where)
    {
        if (empty($where)) {
            return false; // Prevent accidental deletion without conditions
        }

        $result = $this->db->delete($this->table_name, $where);

        if (false === $result) {
            $this->logDbError('Delete failed', ['where' => $where]);
        }

        return $result;
    }

    /**
     * Delete all records from the table with optional conditions.
     *
     * @param array $conditions optional WHERE conditions as key-value pairs
     *
     * @return false|int number of rows deleted, false on failure
     */
    public function delete_all(array $conditions = [])
    {
        if (!empty($conditions)) {
            // Handle key-value pair conditions
            $where_clauses = [];
            $values = [];

            foreach ($conditions as $column => $value) {
                $placeholder = $this->get_placeholder($value);
                if (!empty($value) && false !== strpos($value, '%')) {
                    // Handle LIKE condition
                    $where_clauses[] = "{$column} LIKE {$placeholder}";
                } else {
                    // Handle equality condition
                    $where_clauses[] = "{$column} = {$placeholder}";
                }
                $values[] = $value;
            }

            $where_sql = ' WHERE '.implode(' AND ', $where_clauses);
            $query = $this->db->prepare('DELETE FROM '.$this->getTableName().$where_sql, $values);
        } else {
            // Delete all records if no conditions are provided
            $query = 'DELETE FROM '.$this->getTableName();
        }

        $result = $this->db->query($query);

        if (false === $result) {
            $this->logDbError('Delete all failed', ['conditions' => $conditions]);
        }

        return $result;
    }

    /**
     * Get appropriate placeholder for a value.
     *
     * @param mixed $value
     */
    protected function get_placeholder($value): string
    {
        if (is_int($value)) {
            return '%d';
        }

        if (is_float($value)) {
            return '%f';
        }

        // Treat everything else as string
        return '%s';
    }

    /**
     * Get Column Type.
     */
    protected function get_column_type(string $table_name, string $column_name): ?string
    {
        $type = $this->db->get_var(
            $this->db->prepare(
                'SELECT COLUMN_TYPE 
                FROM INFORMATION_SCHEMA.COLUMNS 
                WHERE TABLE_SCHEMA = DATABASE() 
                AND TABLE_NAME = %s 
                AND COLUMN_NAME = %s',
                $table_name,
                $column_name
            )
        );

        return null !== $type ? strtoupper($type) : null;
    }

    /**
     * Verify if a table is properly created.
     *
     * @param array $expected_columns      [column_name => expected_type]
     * @param array $expected_indexes      [index_name]
     * @param array $expected_foreign_keys [constraint_name => ['column' => ..., 'referenced_table' => ..., 'referenced_column' => ...]]
     */
    protected function health_check(
        string $table_name,
        array $expected_columns = [],
        array $expected_indexes = [],
        array $expected_foreign_keys = []
    ): bool {
        // 1. Check table exists
        $exists = $this->db->get_var($this->db->prepare(
            'SHOW TABLES LIKE %s',
            $table_name
        ));
        if (!$exists) {
            $this->selfLog(self::$ERROR, "Health check failed: table {$table_name} does not exist.");

            return false;
        }

        // 2. Get columns
        $columns = $this->db->get_results($this->db->prepare(
            'SELECT COLUMN_NAME, COLUMN_TYPE 
            FROM INFORMATION_SCHEMA.COLUMNS 
            WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s',
            $table_name
        ), OBJECT_K);

        foreach ($expected_columns as $col => $expected_type) {
            if (!isset($columns[$col])) {
                $this->selfLog(self::$ERROR, "Health check failed: missing column '{$col}' in {$table_name}.");

                return false;
            }
            $actual_type = strtoupper($columns[$col]->COLUMN_TYPE);
            if (false === strpos($actual_type, strtoupper($expected_type))) {
                $this->selfLog(self::$WARNING, "Column '{$col}' in {$table_name} has type '{$actual_type}' but expected '{$expected_type}'.");
            }
        }

        // 3. Check indexes
        if ($expected_indexes) {
            // No placeholder needed, just escape table name safely
            $table_name_esc = esc_sql($table_name);
            $indexes = $this->db->get_col(
                "SHOW INDEX FROM {$table_name_esc}", // direct query
                2
            );
            foreach ($expected_indexes as $idx) {
                if (!in_array($idx, $indexes)) {
                    $this->selfLog(self::$WARNING, "Health check: missing index '{$idx}' in {$table_name}.");
                }
            }
        }

        // 4. Check foreign keys
        if ($expected_foreign_keys) {
            $fks = $this->db->get_results(
                $this->db->prepare('
                    SELECT CONSTRAINT_NAME, COLUMN_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
                    FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
                    WHERE TABLE_SCHEMA = DATABASE()
                    AND TABLE_NAME = %s
                    AND REFERENCED_TABLE_NAME IS NOT NULL
                ', $table_name),
                OBJECT_K
            );

            foreach ($expected_foreign_keys as $name => $expected) {
                $found = false;
                foreach ($fks as $fk) {
                    if (
                        $fk->COLUMN_NAME === $expected['column']
                        && $fk->REFERENCED_TABLE_NAME === $expected['referenced_table']
                        && $fk->REFERENCED_COLUMN_NAME === $expected['referenced_column']
                    ) {
                        $found = true;

                        break;
                    }
                }
                if (!$found) {
                    $this->selfLog(self::$WARNING, "Health check: missing FK '{$name}' ({$expected['column']} → {$expected['referenced_table']}.{$expected['referenced_column']}).");
                }
            }
        }

        $this->selfLog(self::$INFO, "Health check passed for {$table_name}.");

        return true;
    }

    /**
     * Log DB errors using SibsLogger if available, otherwise fallback to error_log.
     */
    protected function logDbError(string $action, array $context = []): void
    {
        if (!empty($this->db->last_error)) {
            $message = "{$action}: {$this->db->last_error}";
            $errorData = [
                'message' => $message,
                'last_query' => $this->db->last_query,
                'context' => $context,
            ];

            $this->selfLog(self::$ERROR, SibsLogger::prettify($errorData));
        }
    }

    /**
     * Build a WHERE clause for a given column and condition.
     *
     * @param string $column
     * @param mixed  $cond   Either a value (simple mode) or ['operator' => ..., 'value' => ...]
     * @param array  $values Reference to values to bind
     *
     * @return string The SQL snippet for the condition
     */
    private function build_where_clause($column, $cond, array &$values)
    {
        if (is_array($cond) && isset($cond['operator'])) {
            $operator = strtoupper(trim($cond['operator']));
            $value = $cond['value'] ?? null;

            if (in_array($operator, ['IS', 'IS NOT'], true) && null === $value) {
                return "{$column} {$operator} NULL";
            }
            if ('IN' === $operator && is_array($value)) {
                $placeholders = implode(', ', array_map(function ($val) {
                    return $this->get_placeholder($val);
                }, $value));
                $values = array_merge($values, $value);

                return "{$column} IN ({$placeholders})";
            }
            if ('LIKE' === $operator && is_string($value)) {
                $placeholder = $this->get_placeholder($value);
                $values[] = $value;

                return "{$column} LIKE {$placeholder}";
            }
            if (in_array($operator, ['=', '!=', '<', '<=', '>', '>='], true)) {
                $placeholder = $this->get_placeholder($value);
                $values[] = $value;

                return "{$column} {$operator} {$placeholder}";
            }
        }

        $placeholder = $this->get_placeholder($cond);
        $values[] = $cond;

        return "{$column} = {$placeholder}";
    }
}
