<?php
/**
 * Database table creation, seeding, and CRUD operations.
 */

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

class Noon_Audit_Database {

    const DB_VERSION = '1.3'; // Bump this when adding new migrations.

    /**
     * Run on plugin activation — create tables, seed, and run migrations.
     */
    public static function activate() {
        self::create_tables();
        self::maybe_seed();
        self::maybe_upgrade();
    }

    /**
     * Run on admin_init to catch upgrades without deactivation.
     */
    public static function maybe_upgrade() {
        $installed_version = get_option( 'noon_audit_db_version', '1.0' );

        if ( version_compare( $installed_version, '1.1', '<' ) ) {
            self::upgrade_to_1_1();
        }
        if ( version_compare( $installed_version, '1.2', '<' ) ) {
            self::upgrade_to_1_2();
        }
        if ( version_compare( $installed_version, '1.3', '<' ) ) {
            self::upgrade_to_1_3();
        }

        update_option( 'noon_audit_db_version', self::DB_VERSION );
    }

    /**
     * v1.1 — Add GDPR consent columns to leads table.
     */
    private static function upgrade_to_1_1() {
        global $wpdb;
        $table = $wpdb->prefix . 'noon_audit_leads';

        // Check if columns already exist before adding.
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $columns = $wpdb->get_col( "DESCRIBE {$table}", 0 );

        if ( ! in_array( 'consented_at', $columns, true ) ) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.SchemaChange
            $wpdb->query( "ALTER TABLE {$table} ADD COLUMN consented_at DATETIME DEFAULT NULL AFTER crm_id" );
        }
        if ( ! in_array( 'marketing_optin', $columns, true ) ) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.SchemaChange
            $wpdb->query( "ALTER TABLE {$table} ADD COLUMN marketing_optin TINYINT(1) NOT NULL DEFAULT 0 AFTER consented_at" );
        }
    }

    /**
     * v1.2 — Add is_seed flag to audits table + backfill existing seeds.
     */
    private static function upgrade_to_1_2() {
        global $wpdb;
        $table = $wpdb->prefix . 'noon_audits';

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $columns = $wpdb->get_col( "DESCRIBE {$table}", 0 );
        if ( ! in_array( 'is_seed', $columns, true ) ) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.SchemaChange
            $wpdb->query( "ALTER TABLE {$table} ADD COLUMN is_seed TINYINT(1) NOT NULL DEFAULT 0 AFTER status" );
        }

        // Backfill: mark existing audits whose slugs match the seed file.
        $seed_file = NOON_AUDIT_PLUGIN_DIR . 'templates/seed-audits.json';
        if ( file_exists( $seed_file ) ) {
            $seed_data = json_decode( file_get_contents( $seed_file ), true );
            if ( is_array( $seed_data ) ) {
                foreach ( $seed_data as $audit ) {
                    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
                    $wpdb->update( $table, [ 'is_seed' => 1 ], [ 'slug' => $audit['slug'] ] );
                }
            }
        }
    }

    /**
     * v1.3 — Add crm_error tracking for failed API pushes.
     */
    private static function upgrade_to_1_3() {
        global $wpdb;
        $table = $wpdb->prefix . 'noon_audit_leads';

        // Check if columns already exist before adding.
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $columns = $wpdb->get_col( "DESCRIBE {$table}", 0 );

        if ( ! in_array( 'crm_error', $columns, true ) ) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.SchemaChange
            $wpdb->query( "ALTER TABLE {$table} ADD COLUMN crm_error TEXT NOT NULL DEFAULT '' AFTER crm_id" );
        }
    }

    /**
     * Run on plugin deactivation (no-op for now — don't drop tables).
     */
    public static function deactivate() {
        // Intentionally empty — we preserve data on deactivation.
    }

    // ─── Table Creation ──────────────────────────────────────────────────────

    private static function create_tables() {
        global $wpdb;
        $charset = $wpdb->get_charset_collate();

        $audits_table = $wpdb->prefix . 'noon_audits';
        $leads_table  = $wpdb->prefix . 'noon_audit_leads';

        $sql = "
            CREATE TABLE {$audits_table} (
                id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
                title VARCHAR(255) NOT NULL,
                slug VARCHAR(100) NOT NULL,
                hook_text TEXT NOT NULL DEFAULT '',
                categories LONGTEXT NOT NULL,
                questions LONGTEXT NOT NULL,
                band_headlines LONGTEXT NOT NULL,
                settings LONGTEXT NOT NULL DEFAULT '{}',
                status VARCHAR(20) NOT NULL DEFAULT 'published',
                is_seed TINYINT(1) NOT NULL DEFAULT 0,
                created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                PRIMARY KEY (id),
                UNIQUE KEY slug (slug)
            ) {$charset};

            CREATE TABLE {$leads_table} (
                id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
                audit_id BIGINT(20) UNSIGNED NOT NULL,
                email VARCHAR(255) NOT NULL,
                name VARCHAR(255) NOT NULL DEFAULT '',
                company VARCHAR(255) NOT NULL DEFAULT '',
                score INT NOT NULL DEFAULT 0,
                risk_band VARCHAR(20) NOT NULL DEFAULT '',
                category_scores LONGTEXT NOT NULL DEFAULT '[]',
                answers LONGTEXT NOT NULL DEFAULT '[]',
                crm_synced TINYINT(1) NOT NULL DEFAULT 0,
                crm_id VARCHAR(255) NOT NULL DEFAULT '',
                crm_error TEXT NOT NULL DEFAULT '',
                consented_at DATETIME DEFAULT NULL,
                marketing_optin TINYINT(1) NOT NULL DEFAULT 0,
                created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY (id),
                KEY audit_id (audit_id),
                KEY email (email)
            ) {$charset};
        ";

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

    // ─── Seeding ─────────────────────────────────────────────────────────────

    private static function maybe_seed() {
        global $wpdb;
        $table = $wpdb->prefix . 'noon_audits';

        // Only seed if table is empty.
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $count = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$table}" );
        if ( $count > 0 ) {
            return;
        }

        $seed_file = NOON_AUDIT_PLUGIN_DIR . 'templates/seed-audits.json';
        if ( ! file_exists( $seed_file ) ) {
            return;
        }

        $seed_data = json_decode( file_get_contents( $seed_file ), true );
        if ( ! is_array( $seed_data ) ) {
            return;
        }

        foreach ( $seed_data as $audit ) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
            $wpdb->insert( $table, [
                'title'          => $audit['title'],
                'slug'           => $audit['slug'],
                'hook_text'      => $audit['hook_text'],
                'categories'     => wp_json_encode( $audit['categories'] ),
                'questions'      => wp_json_encode( $audit['questions'] ),
                'band_headlines' => wp_json_encode( $audit['band_headlines'] ),
                'settings'       => wp_json_encode( $audit['settings'] ?? new \stdClass() ),
                'status'         => 'published',
                'is_seed'        => 1,
            ] );
        }
    }

    // ─── Audit CRUD ──────────────────────────────────────────────────────────

    public static function get_audit_by_slug( $slug ) {
        global $wpdb;
        $table = $wpdb->prefix . 'noon_audits';
        // phpcs:ignore
        return $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$table} WHERE slug = %s AND status = 'published' LIMIT 1", $slug ) );
    }

    public static function get_audit_by_id( $id ) {
        global $wpdb;
        $table = $wpdb->prefix . 'noon_audits';
        // phpcs:ignore
        return $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$table} WHERE id = %d LIMIT 1", $id ) );
    }

    public static function get_default_audit() {
        global $wpdb;
        $table = $wpdb->prefix . 'noon_audits';
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        return $wpdb->get_row( "SELECT * FROM {$table} WHERE status = 'published' ORDER BY id ASC LIMIT 1" );
    }

    public static function get_all_audits() {
        global $wpdb;
        $table = $wpdb->prefix . 'noon_audits';
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        return $wpdb->get_results( "SELECT * FROM {$table} ORDER BY id ASC" );
    }

    public static function insert_audit( $data ) {
        global $wpdb;
        $table = $wpdb->prefix . 'noon_audits';

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
        $result = $wpdb->insert( $table, [
            'title'          => sanitize_text_field( $data['title'] ),
            'slug'           => sanitize_title( $data['slug'] ),
            'hook_text'      => sanitize_textarea_field( $data['hook_text'] ?? '' ),
            'categories'     => wp_json_encode( $data['categories'] ?? [] ),
            'questions'      => wp_json_encode( $data['questions'] ?? [] ),
            'band_headlines' => wp_json_encode( $data['band_headlines'] ?? self::default_band_headlines() ),
            'settings'       => wp_json_encode( $data['settings'] ?? new \stdClass() ),
            'status'         => sanitize_text_field( $data['status'] ?? 'published' ),
        ] );

        return $result !== false ? $wpdb->insert_id : false;
    }

    public static function update_audit( $id, $data ) {
        global $wpdb;
        $table = $wpdb->prefix . 'noon_audits';

        $fields = [];
        if ( isset( $data['title'] ) )          $fields['title']          = sanitize_text_field( $data['title'] );
        if ( isset( $data['slug'] ) )           $fields['slug']           = sanitize_title( $data['slug'] );
        if ( isset( $data['hook_text'] ) )      $fields['hook_text']      = sanitize_textarea_field( $data['hook_text'] );
        if ( isset( $data['categories'] ) )     $fields['categories']     = wp_json_encode( $data['categories'] );
        if ( isset( $data['questions'] ) )      $fields['questions']      = wp_json_encode( $data['questions'] );
        if ( isset( $data['band_headlines'] ) )  $fields['band_headlines'] = wp_json_encode( $data['band_headlines'] );
        if ( isset( $data['settings'] ) )       $fields['settings']       = wp_json_encode( $data['settings'] );
        if ( isset( $data['status'] ) )         $fields['status']         = sanitize_text_field( $data['status'] );

        if ( empty( $fields ) ) {
            return false;
        }

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        return $wpdb->update( $table, $fields, [ 'id' => (int) $id ] ) !== false;
    }

    public static function delete_audit( $id ) {
        global $wpdb;
        $table = $wpdb->prefix . 'noon_audits';
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        return $wpdb->delete( $table, [ 'id' => (int) $id ] ) !== false;
    }

    public static function default_band_headlines() {
        return [
            'critical' => [ 'title' => 'Critical Risk', 'text' => 'Significant issues detected — immediate action needed.' ],
            'high'     => [ 'title' => 'High Risk', 'text' => 'Major gaps identified — action recommended.' ],
            'moderate' => [ 'title' => 'Moderate', 'text' => 'Solid foundation with clear gaps to close.' ],
            'low'      => [ 'title' => 'Low Risk', 'text' => 'Well-managed — you\'re ahead of most.' ],
        ];
    }

    // ─── Lead CRUD ───────────────────────────────────────────────────────────

    public static function insert_lead( $data ) {
        global $wpdb;
        $table = $wpdb->prefix . 'noon_audit_leads';

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
        $result = $wpdb->insert( $table, [
            'audit_id'        => (int) $data['audit_id'],
            'email'           => sanitize_email( $data['email'] ),
            'name'            => sanitize_text_field( $data['name'] ?? '' ),
            'company'         => sanitize_text_field( $data['company'] ?? '' ),
            'score'           => (int) ( $data['score'] ?? 0 ),
            'risk_band'       => sanitize_text_field( $data['risk_band'] ?? '' ),
            'category_scores' => wp_json_encode( $data['category_scores'] ?? [] ),
            'answers'         => wp_json_encode( $data['answers'] ?? [] ),
            'consented_at'    => ! empty( $data['consented_at'] )
                ? gmdate( 'Y-m-d H:i:s', strtotime( $data['consented_at'] ) )
                : current_time( 'mysql', true ),
            'marketing_optin' => ! empty( $data['marketing_optin'] ) ? 1 : 0,
        ] );

        if ( $result === false ) {
            return false;
        }

        return $wpdb->insert_id;
    }

    public static function get_leads( $args = [] ) {
        global $wpdb;
        $table = $wpdb->prefix . 'noon_audit_leads';

        $per_page = (int) ( $args['per_page'] ?? 20 );
        $page     = (int) ( $args['page'] ?? 1 );
        $offset   = ( $page - 1 ) * $per_page;
        $audit_id = $args['audit_id'] ?? null;

        $where = '';
        if ( $audit_id ) {
            $where = $wpdb->prepare( 'WHERE audit_id = %d', $audit_id );
        }

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $total = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$table} {$where}" );
        // phpcs:ignore
        $items = $wpdb->get_results( "SELECT * FROM {$table} {$where} ORDER BY created_at DESC LIMIT {$per_page} OFFSET {$offset}" );

        return [
            'items'    => $items,
            'total'    => $total,
            'page'     => $page,
            'per_page' => $per_page,
            'pages'    => ceil( $total / $per_page ),
        ];
    }

    /**
     * Run on plugin uninstall (hooked via Freemius).
     */
    public static function uninstall() {
        global $wpdb;

        // 1. Drop Custom Tables
        $noon_audit_tables = [
            $wpdb->prefix . 'noon_audits',
            $wpdb->prefix . 'noon_audit_leads',
        ];

        foreach ( $noon_audit_tables as $noon_audit_table ) {
            // Directly dropping tables is required for clinical cleanup as there is no core API for this.
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery
            $wpdb->query( "DROP TABLE IF EXISTS " . esc_sql( $noon_audit_table ) );
        }

        // 2. Delete Options
        $noon_audit_options = [
            'noon_audit_settings',
            'noon_audit_db_version',
        ];

        foreach ( $noon_audit_options as $noon_audit_option ) {
            delete_option( $noon_audit_option );
        }

        // 3. Delete User Metadata (Guide Dismissal)
        // We use a direct query to efficiently remove this meta key for all users at once during uninstallation.
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $wpdb->query( $wpdb->prepare( "DELETE FROM {$wpdb->usermeta} WHERE meta_key = %s", 'noon_audit_dismiss_guide' ) );
    }
}
