<?php

namespace AcademicCrew\Core;

/**
 * Runs on plugin activation: creates DB tables, sets default options, flushes rewrites.
 */
class Activator {

    public static function activate(): void {
        self::create_tables();
        self::set_default_options();
        self::create_archive_pages();
        flush_rewrite_rules();
    }

    private static function create_archive_pages(): void {
        \AcademicCrew\PageGenerator\MembersArchivePageGenerator::generate_if_needed();
        \AcademicCrew\PageGenerator\CoursesArchivePageGenerator::generate_if_needed();
        \AcademicCrew\PageGenerator\CollaborationsArchivePageGenerator::generate_if_needed();
    }

    public static function upgrade(): void {
        self::create_tables();
        self::create_archive_pages();
        // Add keywords column if upgrading from a version that lacks it.
        global $wpdb;
        $table = esc_sql( $wpdb->prefix . 'academiccrew_publications' );
        if ( ! $wpdb->get_var( "SHOW COLUMNS FROM {$table} LIKE 'keywords'" ) ) { // phpcs:ignore
            $wpdb->query( "ALTER TABLE {$table} ADD COLUMN keywords LONGTEXT DEFAULT NULL AFTER raw_data" ); // phpcs:ignore
        }
        $pub_table = esc_sql( $wpdb->prefix . 'academiccrew_publications' );
        if ( ! $wpdb->get_var( "SHOW COLUMNS FROM {$pub_table} LIKE 'merged_ids_json'" ) ) { // phpcs:ignore
            $wpdb->query( "ALTER TABLE {$pub_table} ADD COLUMN merged_ids_json LONGTEXT DEFAULT NULL AFTER keywords" ); // phpcs:ignore
        }
        $log_table = esc_sql( $wpdb->prefix . 'academiccrew_sync_log' );
        if ( ! $wpdb->get_var( "SHOW COLUMNS FROM {$log_table} LIKE 'records_merged'" ) ) { // phpcs:ignore
            $wpdb->query( "ALTER TABLE {$log_table} ADD COLUMN records_merged INT NOT NULL DEFAULT 0 AFTER records_skipped" ); // phpcs:ignore
        }
        $excl_table = esc_sql( $wpdb->prefix . 'academiccrew_excluded_publications' );
        if ( ! $wpdb->get_var( "SHOW TABLES LIKE '{$excl_table}'" ) ) { // phpcs:ignore
            self::create_tables();
        }
        // 1.10: where each publication's citation count came from (Scholar wins).
        if ( ! $wpdb->get_var( "SHOW COLUMNS FROM {$pub_table} LIKE 'citations_source'" ) ) { // phpcs:ignore
            $wpdb->query( "ALTER TABLE {$pub_table} ADD COLUMN citations_source VARCHAR(20) DEFAULT NULL AFTER citations" ); // phpcs:ignore
        }
        // 1.9: citations come only from Scholar — drop the old per-publication sums.
        foreach ( get_posts( [ 'post_type' => 'academiccrew_member', 'post_status' => 'any', 'posts_per_page' => -1, 'fields' => 'ids' ] ) as $mid ) {
            \AcademicCrew\Members\MemberStats::refresh( (int) $mid );
        }
        update_option( 'academiccrew_db_version', '1.10' );
        flush_rewrite_rules( false );
    }

    public static function create_tables(): void {
        global $wpdb;

        require_once ABSPATH . 'wp-admin/includes/upgrade.php';

        $charset_collate = $wpdb->get_charset_collate();
        $p               = $wpdb->prefix;

        // Publications — one row per unique publication (deduped by DOI / source ID).
        $publications = "CREATE TABLE {$p}academiccrew_publications (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  doi           VARCHAR(255) DEFAULT NULL,
  orcid_work_id VARCHAR(50) DEFAULT NULL,
  scholar_id    VARCHAR(100) DEFAULT NULL,
  iris_id       VARCHAR(100) DEFAULT NULL,
  title         TEXT NOT NULL,
  abstract      LONGTEXT,
  year          SMALLINT DEFAULT NULL,
  venue         VARCHAR(500) DEFAULT NULL,
  pub_type      VARCHAR(100) DEFAULT NULL,
  authors_json  LONGTEXT,
  pdf_url       VARCHAR(500) DEFAULT NULL,
  url           VARCHAR(500) DEFAULT NULL,
  citations      INT UNSIGNED DEFAULT NULL,
  citations_source VARCHAR(20) DEFAULT NULL,
  source_api     VARCHAR(50) DEFAULT NULL,
  raw_data       LONGTEXT,
  keywords       LONGTEXT DEFAULT NULL,
  merged_ids_json LONGTEXT DEFAULT NULL,
  is_featured    TINYINT(1) NOT NULL DEFAULT 0,
  featured_order SMALLINT UNSIGNED NOT NULL DEFAULT 999,
  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 uq_doi (doi),
  KEY idx_year (year),
  KEY idx_pub_type (pub_type),
  KEY idx_orcid_work (orcid_work_id),
  KEY idx_scholar (scholar_id),
  KEY idx_iris (iris_id),
  KEY idx_featured (is_featured, featured_order)
) ENGINE=InnoDB $charset_collate;";

        dbDelta( $publications );

        // Junction table — links members to publications.
        $member_publications = "CREATE TABLE {$p}academiccrew_member_publications (
  member_id        BIGINT UNSIGNED NOT NULL,
  publication_id   BIGINT UNSIGNED NOT NULL,
  is_corresponding TINYINT(1) NOT NULL DEFAULT 0,
  PRIMARY KEY  (member_id, publication_id),
  KEY idx_member (member_id),
  KEY idx_pub (publication_id)
) ENGINE=InnoDB $charset_collate;";

        dbDelta( $member_publications );

        // Sync log — one row per sync run per source per member.
        $sync_log = "CREATE TABLE {$p}academiccrew_sync_log (
  id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  source          VARCHAR(50) DEFAULT NULL,
  member_id       BIGINT UNSIGNED DEFAULT NULL,
  status          VARCHAR(10) NOT NULL,
  records_new     INT NOT NULL DEFAULT 0,
  records_updated INT NOT NULL DEFAULT 0,
  records_skipped INT NOT NULL DEFAULT 0,
  records_merged  INT NOT NULL DEFAULT 0,
  error_message   TEXT,
  started_at      DATETIME DEFAULT NULL,
  finished_at     DATETIME DEFAULT NULL,
  PRIMARY KEY  (id),
  KEY idx_member (member_id),
  KEY idx_source (source),
  KEY idx_status (status)
) ENGINE=InnoDB $charset_collate;";

        dbDelta( $sync_log );

        // Skip log — one row per skipped publication per sync run.
        $skip_log = "CREATE TABLE {$p}academiccrew_skip_log (
  id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  sync_log_id  BIGINT UNSIGNED DEFAULT NULL,
  member_id    BIGINT UNSIGNED DEFAULT NULL,
  pub_id       BIGINT UNSIGNED DEFAULT NULL,
  source       VARCHAR(50) DEFAULT NULL,
  match_type   VARCHAR(20) DEFAULT NULL,
  match_value  VARCHAR(500) DEFAULT NULL,
  pub_title    VARCHAR(500) DEFAULT NULL,
  pub_year     SMALLINT DEFAULT NULL,
  created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY  (id),
  KEY idx_sync (sync_log_id),
  KEY idx_member (member_id)
) ENGINE=InnoDB $charset_collate;";

        dbDelta( $skip_log );

        // Excluded publications — source IDs of pubs the admin chose never to re-add from sync.
        $excluded_pubs = "CREATE TABLE {$p}academiccrew_excluded_publications (
  id             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  doi            VARCHAR(255) DEFAULT NULL,
  orcid_work_id  VARCHAR(50)  DEFAULT NULL,
  scholar_id     VARCHAR(100) DEFAULT NULL,
  iris_id        VARCHAR(100) DEFAULT NULL,
  title          TEXT DEFAULT NULL,
  year           SMALLINT DEFAULT NULL,
  excluded_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  excluded_by    BIGINT UNSIGNED DEFAULT NULL,
  PRIMARY KEY  (id),
  KEY idx_doi     (doi),
  KEY idx_scholar (scholar_id),
  KEY idx_iris    (iris_id),
  KEY idx_orcid   (orcid_work_id)
) ENGINE=InnoDB $charset_collate;";

        dbDelta( $excluded_pubs );

        // Publication tags — one row per tag with colour.
        $pub_tags = "CREATE TABLE {$p}academiccrew_pub_tags (
  id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name       VARCHAR(100) NOT NULL,
  color      VARCHAR(7) NOT NULL DEFAULT '#2563eb',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY  (id),
  UNIQUE KEY uq_name (name)
) ENGINE=InnoDB $charset_collate;";

        dbDelta( $pub_tags );

        // Junction — links tags to publications.
        $pub_tag_relations = "CREATE TABLE {$p}academiccrew_pub_tag_relations (
  publication_id BIGINT UNSIGNED NOT NULL,
  tag_id         BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY  (publication_id, tag_id),
  KEY idx_tag (tag_id)
) ENGINE=InnoDB $charset_collate;";

        dbDelta( $pub_tag_relations );

        update_option( 'academiccrew_db_version', '1.10' );
    }

    private static function set_default_options(): void {
        add_option( 'academiccrew_version', ACADEMICCREW_VERSION );
        // Academic period cadence/windows and everything else default in code
        // (see PeriodCadence, AcademicYearCalculator) until the admin saves
        // Settings for the first time.
        add_option( 'academiccrew_settings', [] );
    }
}