package db

import "gorm.io/gorm"

type compatColumn struct {
	Name string
	DDL  string
}

type compatIndex struct {
	Name string
	DDL  string
}

// EnsureCompatibility patches schema drift left by the legacy Python/Alembic
// backend. It is intentionally narrow: only columns that current Go handlers
// read/write are added.
func EnsureCompatibility(conn *gorm.DB) error {
	if conn == nil {
		return nil
	}
	if err := ensureNotifications(conn); err != nil {
		return err
	}
	if err := ensureAnnouncements(conn); err != nil {
		return err
	}
	if err := ensureActivities(conn); err != nil {
		return err
	}
	if err := ensureFinanceIndexes(conn); err != nil {
		return err
	}
	if err := ensureOAuthConnections(conn); err != nil {
		return err
	}
	return nil
}

func ensureActivities(conn *gorm.DB) error {
	if err := conn.Exec(`
CREATE TABLE IF NOT EXISTS activities (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(128) NOT NULL,
  description TEXT NOT NULL,
	  subtitle VARCHAR(255) NULL,
	  content LONGTEXT NULL,
  image_url VARCHAR(500) NULL,
	  banner_url VARCHAR(500) NULL,
  target_url VARCHAR(500) NULL,
  label VARCHAR(32) NULL,
	  template_key VARCHAR(32) NOT NULL DEFAULT 'standard',
	  button_text VARCHAR(32) NOT NULL DEFAULT '立即参与',
	  participation_mode VARCHAR(32) NOT NULL DEFAULT 'direct',
	  promo_code_id BIGINT NULL,
	  audience VARCHAR(32) NOT NULL DEFAULT 'all',
	  rules LONGTEXT NULL,
	  per_user_limit INT NOT NULL DEFAULT 0,
	  total_limit INT NOT NULL DEFAULT 0,
  limited_time TINYINT(1) NOT NULL DEFAULT 1,
  starts_at DATETIME NULL,
  ends_at DATETIME NULL,
  is_published TINYINT(1) NOT NULL DEFAULT 1,
	  is_featured TINYINT(1) NOT NULL DEFAULT 0,
	  show_on_home TINYINT(1) NOT NULL DEFAULT 1,
  sort_order INT NOT NULL DEFAULT 0,
  created_by BIGINT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_activities_starts_at (starts_at),
  INDEX idx_activities_ends_at (ends_at),
  INDEX idx_activities_is_published (is_published),
  INDEX idx_activities_sort_order (sort_order)
)`).Error; err != nil {
		return err
	}
	if err := ensureColumns(conn, "activities", []compatColumn{
		{"subtitle", "ALTER TABLE activities ADD COLUMN subtitle VARCHAR(255) NULL"},
		{"content", "ALTER TABLE activities ADD COLUMN content LONGTEXT NULL"},
		{"image_url", "ALTER TABLE activities ADD COLUMN image_url VARCHAR(500) NULL"},
		{"banner_url", "ALTER TABLE activities ADD COLUMN banner_url VARCHAR(500) NULL"},
		{"target_url", "ALTER TABLE activities ADD COLUMN target_url VARCHAR(500) NULL"},
		{"label", "ALTER TABLE activities ADD COLUMN label VARCHAR(32) NULL"},
		{"template_key", "ALTER TABLE activities ADD COLUMN template_key VARCHAR(32) NOT NULL DEFAULT 'standard'"},
		{"button_text", "ALTER TABLE activities ADD COLUMN button_text VARCHAR(32) NOT NULL DEFAULT '立即参与'"},
		{"participation_mode", "ALTER TABLE activities ADD COLUMN participation_mode VARCHAR(32) NOT NULL DEFAULT 'direct'"},
		{"promo_code_id", "ALTER TABLE activities ADD COLUMN promo_code_id BIGINT NULL"},
		{"audience", "ALTER TABLE activities ADD COLUMN audience VARCHAR(32) NOT NULL DEFAULT 'all'"},
		{"rules", "ALTER TABLE activities ADD COLUMN rules LONGTEXT NULL"},
		{"per_user_limit", "ALTER TABLE activities ADD COLUMN per_user_limit INT NOT NULL DEFAULT 0"},
		{"total_limit", "ALTER TABLE activities ADD COLUMN total_limit INT NOT NULL DEFAULT 0"},
		{"limited_time", "ALTER TABLE activities ADD COLUMN limited_time TINYINT(1) NOT NULL DEFAULT 1"},
		{"starts_at", "ALTER TABLE activities ADD COLUMN starts_at DATETIME NULL"},
		{"ends_at", "ALTER TABLE activities ADD COLUMN ends_at DATETIME NULL"},
		{"is_published", "ALTER TABLE activities ADD COLUMN is_published TINYINT(1) NOT NULL DEFAULT 1"},
		{"is_featured", "ALTER TABLE activities ADD COLUMN is_featured TINYINT(1) NOT NULL DEFAULT 0"},
		{"show_on_home", "ALTER TABLE activities ADD COLUMN show_on_home TINYINT(1) NOT NULL DEFAULT 1"},
		{"sort_order", "ALTER TABLE activities ADD COLUMN sort_order INT NOT NULL DEFAULT 0"},
		{"created_by", "ALTER TABLE activities ADD COLUMN created_by BIGINT NULL"},
	}); err != nil {
		return err
	}
	if err := ensureIndexes(conn, "activities", []compatIndex{
		{"idx_activities_visible_order", "CREATE INDEX idx_activities_visible_order ON activities (is_published, sort_order, starts_at, ends_at, id)"},
	}); err != nil {
		return err
	}
	if err := conn.Exec(`
CREATE TABLE IF NOT EXISTS activity_products (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  activity_id BIGINT NOT NULL,
  product_id BIGINT NOT NULL,
  section_name VARCHAR(128) NOT NULL DEFAULT '精选产品',
  section_description VARCHAR(500) NULL,
  card_style VARCHAR(16) NOT NULL DEFAULT 'a',
  custom_label VARCHAR(32) NULL,
  product_description VARCHAR(500) NULL,
  billing_cycle VARCHAR(32) NULL,
  activity_price DECIMAL(12,2) NULL,
  discount_percent DECIMAL(6,2) NULL,
  stock_limit INT NOT NULL DEFAULT 0,
  per_user_limit INT NOT NULL DEFAULT 0,
  sort_order INT NOT NULL DEFAULT 0,
  UNIQUE KEY uq_activity_product (activity_id, product_id),
  INDEX idx_activity_products_activity (activity_id, sort_order, id),
  INDEX idx_activity_products_product (product_id)
)`).Error; err != nil {
		return err
	}
	return ensureIndexes(conn, "activity_products", []compatIndex{
		{"idx_activity_products_activity_product", "CREATE INDEX idx_activity_products_activity_product ON activity_products (activity_id, product_id)"},
	})
}

func ensureNotifications(conn *gorm.DB) error {
	if err := conn.Exec(`
CREATE TABLE IF NOT EXISTS notifications (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT NOT NULL,
  category VARCHAR(24) NOT NULL DEFAULT 'system',
  level VARCHAR(24) NOT NULL DEFAULT 'info',
  sender_id BIGINT NULL,
  sender_name VARCHAR(128) NULL,
  sender_type VARCHAR(32) NOT NULL DEFAULT 'system',
  title VARCHAR(128) NOT NULL,
  content TEXT NOT NULL,
  link VARCHAR(256) NULL,
  action_label VARCHAR(64) NULL,
  action_url VARCHAR(256) NULL,
  is_read TINYINT(1) NOT NULL DEFAULT 0,
  read_at DATETIME NULL,
  handled_at DATETIME NULL,
  archived_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_notifications_user_id (user_id),
  INDEX idx_notifications_category (category),
  INDEX idx_notifications_is_read (is_read),
  INDEX idx_notifications_user_read (user_id, is_read)
)`).Error; err != nil {
		return err
	}
	if err := ensureColumns(conn, "notifications", []compatColumn{
		{"category", "ALTER TABLE notifications ADD COLUMN category VARCHAR(24) NOT NULL DEFAULT 'system'"},
		{"level", "ALTER TABLE notifications ADD COLUMN level VARCHAR(24) NOT NULL DEFAULT 'info'"},
		{"sender_id", "ALTER TABLE notifications ADD COLUMN sender_id BIGINT NULL"},
		{"sender_name", "ALTER TABLE notifications ADD COLUMN sender_name VARCHAR(128) NULL"},
		{"sender_type", "ALTER TABLE notifications ADD COLUMN sender_type VARCHAR(32) NOT NULL DEFAULT 'system'"},
		{"link", "ALTER TABLE notifications ADD COLUMN link VARCHAR(256) NULL"},
		{"action_label", "ALTER TABLE notifications ADD COLUMN action_label VARCHAR(64) NULL"},
		{"action_url", "ALTER TABLE notifications ADD COLUMN action_url VARCHAR(256) NULL"},
		{"handled_at", "ALTER TABLE notifications ADD COLUMN handled_at DATETIME NULL"},
		{"archived_at", "ALTER TABLE notifications ADD COLUMN archived_at DATETIME NULL"},
	}); err != nil {
		return err
	}
	return ensureIndexes(conn, "notifications", []compatIndex{
		{"idx_notifications_user_read_archived_category", "CREATE INDEX idx_notifications_user_read_archived_category ON notifications (user_id, is_read, archived_at, category)"},
	})
}

func ensureAnnouncements(conn *gorm.DB) error {
	if err := conn.Exec(`
CREATE TABLE IF NOT EXISTS announcements (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(128) NOT NULL,
  content TEXT NOT NULL,
  category VARCHAR(24) NOT NULL DEFAULT 'announcement',
  level VARCHAR(24) NOT NULL DEFAULT 'info',
  link VARCHAR(256) NULL,
  action_label VARCHAR(64) NULL,
  action_url VARCHAR(256) NULL,
  target_type VARCHAR(16) NOT NULL DEFAULT 'all',
  target_user_ids JSON NULL,
  is_published TINYINT(1) NOT NULL DEFAULT 1,
  is_pinned TINYINT(1) NOT NULL DEFAULT 0,
  published_at DATETIME NULL,
  expires_at DATETIME NULL,
  created_by BIGINT NULL,
  created_by_id BIGINT NULL,
  target_audience VARCHAR(32) NOT NULL DEFAULT 'all',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_announcements_is_published (is_published),
  INDEX idx_announcements_is_pinned (is_pinned)
)`).Error; err != nil {
		return err
	}
	if err := ensureColumns(conn, "announcements", []compatColumn{
		{"category", "ALTER TABLE announcements ADD COLUMN category VARCHAR(24) NOT NULL DEFAULT 'announcement'"},
		{"level", "ALTER TABLE announcements ADD COLUMN level VARCHAR(24) NOT NULL DEFAULT 'info'"},
		{"link", "ALTER TABLE announcements ADD COLUMN link VARCHAR(256) NULL"},
		{"action_label", "ALTER TABLE announcements ADD COLUMN action_label VARCHAR(64) NULL"},
		{"action_url", "ALTER TABLE announcements ADD COLUMN action_url VARCHAR(256) NULL"},
		{"target_type", "ALTER TABLE announcements ADD COLUMN target_type VARCHAR(16) NOT NULL DEFAULT 'all'"},
		{"target_user_ids", "ALTER TABLE announcements ADD COLUMN target_user_ids JSON NULL"},
		{"is_published", "ALTER TABLE announcements ADD COLUMN is_published TINYINT(1) NOT NULL DEFAULT 1"},
		{"is_pinned", "ALTER TABLE announcements ADD COLUMN is_pinned TINYINT(1) NOT NULL DEFAULT 0"},
		{"published_at", "ALTER TABLE announcements ADD COLUMN published_at DATETIME NULL"},
		{"expires_at", "ALTER TABLE announcements ADD COLUMN expires_at DATETIME NULL"},
		{"created_by", "ALTER TABLE announcements ADD COLUMN created_by BIGINT NULL"},
		{"created_by_id", "ALTER TABLE announcements ADD COLUMN created_by_id BIGINT NULL"},
		{"target_audience", "ALTER TABLE announcements ADD COLUMN target_audience VARCHAR(32) NOT NULL DEFAULT 'all'"},
	}); err != nil {
		return err
	}
	if err := ensureIndexes(conn, "announcements", []compatIndex{
		{"idx_announcements_visible_order", "CREATE INDEX idx_announcements_visible_order ON announcements (is_published, published_at, expires_at, is_pinned, id)"},
	}); err != nil {
		return err
	}
	if err := conn.Exec(`
CREATE TABLE IF NOT EXISTS announcement_reads (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  announcement_id BIGINT NOT NULL,
  user_id BIGINT NOT NULL,
  read_at DATETIME NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_announcement_user (announcement_id, user_id),
  INDEX idx_announcement_reads_announcement_id (announcement_id),
  INDEX idx_announcement_reads_user_id (user_id)
)`).Error; err != nil {
		return err
	}
	return ensureIndexes(conn, "announcement_reads", []compatIndex{
		{"idx_announcement_reads_user_announcement", "CREATE INDEX idx_announcement_reads_user_announcement ON announcement_reads (user_id, announcement_id)"},
	})
}

func ensureFinanceIndexes(conn *gorm.DB) error {
	if err := ensureIndexes(conn, "recharge_orders", []compatIndex{
		{"idx_recharge_orders_status_id", "CREATE INDEX idx_recharge_orders_status_id ON recharge_orders (status, id)"},
		{"idx_recharge_orders_channel_id", "CREATE INDEX idx_recharge_orders_channel_id ON recharge_orders (channel, id)"},
		{"idx_recharge_orders_status_channel_id", "CREATE INDEX idx_recharge_orders_status_channel_id ON recharge_orders (status, channel, id)"},
		{"idx_recharge_orders_updated_status", "CREATE INDEX idx_recharge_orders_updated_status ON recharge_orders (updated_at, status)"},
	}); err != nil {
		return err
	}
	return ensureIndexes(conn, "balance_logs", []compatIndex{
		{"idx_balance_logs_user_id_id", "CREATE INDEX idx_balance_logs_user_id_id ON balance_logs (user_id, id)"},
		{"idx_balance_logs_change_type_id", "CREATE INDEX idx_balance_logs_change_type_id ON balance_logs (change_type, id)"},
	})
}

func ensureColumns(conn *gorm.DB, table string, columns []compatColumn) error {
	for _, column := range columns {
		ok, err := hasColumn(conn, table, column.Name)
		if err != nil {
			return err
		}
		if !ok {
			if err := conn.Exec(column.DDL).Error; err != nil {
				return err
			}
		}
	}
	return nil
}

func hasColumn(conn *gorm.DB, table string, column string) (bool, error) {
	var count int64
	err := conn.Raw(`
SELECT COUNT(*)
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ? AND COLUMN_NAME = ?
`, table, column).Scan(&count).Error
	return count > 0, err
}

func ensureIndexes(conn *gorm.DB, table string, indexes []compatIndex) error {
	for _, index := range indexes {
		ok, err := hasIndex(conn, table, index.Name)
		if err != nil {
			return err
		}
		if !ok {
			if err := conn.Exec(index.DDL).Error; err != nil {
				return err
			}
		}
	}
	return nil
}

func hasIndex(conn *gorm.DB, table string, index string) (bool, error) {
	var count int64
	err := conn.Raw(`
SELECT COUNT(*)
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ? AND INDEX_NAME = ?
`, table, index).Scan(&count).Error
	return count > 0, err
}

func ensureOAuthConnections(conn *gorm.DB) error {
	return conn.Exec(`
CREATE TABLE IF NOT EXISTS user_oauth_connections (
    id           BIGINT       PRIMARY KEY AUTO_INCREMENT,
    user_id      BIGINT       NOT NULL,
    plugin_id    VARCHAR(64)  NOT NULL,
    provider_uid VARCHAR(256) NOT NULL,
    access_token TEXT         NULL,
    metadata     JSON         NULL,
    created_at   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_uoc_plugin_provider (plugin_id, provider_uid),
    KEY idx_uoc_user_id (user_id)
)`).Error
}
