package database

import (
	"database/sql"
	"fmt"
	"time"

	_ "github.com/go-sql-driver/mysql"
)

// DB 封装 MySQL 数据库操作
type DB struct {
	db *sql.DB
}

// NewDB 创建数据库连接
func NewDB(dsn string, maxOpen, maxIdle int) (*DB, error) {
	db, err := sql.Open("mysql", dsn)
	if err != nil {
		return nil, fmt.Errorf("打开数据库失败: %w", err)
	}

	db.SetMaxOpenConns(maxOpen)
	db.SetMaxIdleConns(maxIdle)
	db.SetConnMaxLifetime(5 * time.Minute)

	if err := db.Ping(); err != nil {
		db.Close()
		return nil, fmt.Errorf("数据库连接失败: %w", err)
	}

	return &DB{db: db}, nil
}

// Close 关闭数据库连接
func (d *DB) Close() error {
	return d.db.Close()
}

// InitSchema 初始化数据库表结构
func (d *DB) InitSchema() error {
	schema := `
CREATE TABLE IF NOT EXISTS inspection_records (
    id              BIGINT AUTO_INCREMENT PRIMARY KEY,
    inspection_type VARCHAR(50) NOT NULL COMMENT '巡检类型: switch/cloud_desktop/ad_security/es_log',
    target          VARCHAR(255) NOT NULL COMMENT '巡检目标名称',
    status          VARCHAR(20) NOT NULL COMMENT '巡检状态: ok/warning/critical/error',
    metrics         TEXT COMMENT '巡检指标 JSON',
    details         TEXT COMMENT '详细信息',
    message         TEXT COMMENT '告警消息',
    inspected_at    DATETIME NOT NULL COMMENT '巡检时间',
    created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_type_time (inspection_type, inspected_at),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='巡检记录表';

CREATE TABLE IF NOT EXISTS alert_history (
    id              BIGINT AUTO_INCREMENT PRIMARY KEY,
    inspection_type VARCHAR(50) NOT NULL,
    target          VARCHAR(255) NOT NULL,
    level           VARCHAR(20) NOT NULL COMMENT '告警级别: warning/critical',
    message         TEXT NOT NULL COMMENT '告警消息',
    sent            BOOLEAN DEFAULT FALSE COMMENT '是否已推送',
    sent_at         DATETIME COMMENT '推送时间',
    created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_type_target (inspection_type, target, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='告警历史表';
`
	_, err := d.db.Exec(schema)
	if err != nil {
		return fmt.Errorf("初始化数据库表结构失败: %w", err)
	}
	return nil
}

// InspectionRecord 巡检记录
type InspectionRecord struct {
	ID              int64
	InspectionType  string
	Target          string
	Status          string
	Metrics         string
	Details         string
	Message         string
	InspectedAt     time.Time
}

// SaveRecord 保存巡检记录
func (d *DB) SaveRecord(rec *InspectionRecord) error {
	_, err := d.db.Exec(
		`INSERT INTO inspection_records (inspection_type, target, status, metrics, details, message, inspected_at)
		 VALUES (?, ?, ?, ?, ?, ?, ?)`,
		rec.InspectionType, rec.Target, rec.Status, rec.Metrics, rec.Details, rec.Message, rec.InspectedAt,
	)
	if err != nil {
		return fmt.Errorf("保存巡检记录失败: %w", err)
	}
	return nil
}

// SaveRecords 批量保存巡检记录
func (d *DB) SaveRecords(records []*InspectionRecord) error {
	if len(records) == 0 {
		return nil
	}

	tx, err := d.db.Begin()
	if err != nil {
		return fmt.Errorf("开启事务失败: %w", err)
	}
	defer tx.Rollback()

	stmt, err := tx.Prepare(
		`INSERT INTO inspection_records (inspection_type, target, status, metrics, details, message, inspected_at)
		 VALUES (?, ?, ?, ?, ?, ?, ?)`)
	if err != nil {
		return fmt.Errorf("预处理语句失败: %w", err)
	}
	defer stmt.Close()

	for _, rec := range records {
		_, err := stmt.Exec(rec.InspectionType, rec.Target, rec.Status, rec.Metrics, rec.Details, rec.Message, rec.InspectedAt)
		if err != nil {
			return fmt.Errorf("批量插入记录失败: %w", err)
		}
	}

	return tx.Commit()
}

// SaveAlert 保存告警记录
func (d *DB) SaveAlert(inspectionType, target, level, message string) error {
	_, err := d.db.Exec(
		`INSERT INTO alert_history (inspection_type, target, level, message) VALUES (?, ?, ?, ?)`,
		inspectionType, target, level, message,
	)
	if err != nil {
		return fmt.Errorf("保存告警记录失败: %w", err)
	}
	return nil
}

// MarkAlertSent 标记告警已推送
func (d *DB) MarkAlertSent(id int64) error {
	_, err := d.db.Exec(
		`UPDATE alert_history SET sent = TRUE, sent_at = NOW() WHERE id = ?`,
		id,
	)
	return err
}

// HasRecentAlert 检查在指定时间窗口内是否已有相同告警（去重）
func (d *DB) HasRecentAlert(inspectionType, target string, window time.Duration) (bool, error) {
	since := time.Now().Add(-window)
	var count int
	err := d.db.QueryRow(
		`SELECT COUNT(*) FROM alert_history 
		 WHERE inspection_type = ? AND target = ? AND created_at >= ?`,
		inspectionType, target, since,
	).Scan(&count)
	if err != nil {
		return false, err
	}
	return count > 0, nil
}

// QueryRecentRecords 查询最近的巡检记录
func (d *DB) QueryRecentRecords(inspectionType string, limit int) ([]*InspectionRecord, error) {
	rows, err := d.db.Query(
		`SELECT id, inspection_type, target, status, metrics, details, message, inspected_at
		 FROM inspection_records
		 WHERE inspection_type = ?
		 ORDER BY inspected_at DESC
		 LIMIT ?`,
		inspectionType, limit,
	)
	if err != nil {
		return nil, fmt.Errorf("查询巡检记录失败: %w", err)
	}
	defer rows.Close()

	var records []*InspectionRecord
	for rows.Next() {
		rec := &InspectionRecord{}
		var metrics, details, message sql.NullString
		if err := rows.Scan(&rec.ID, &rec.InspectionType, &rec.Target, &rec.Status,
			&metrics, &details, &message, &rec.InspectedAt); err != nil {
			return nil, err
		}
		rec.Metrics = metrics.String
		rec.Details = details.String
		rec.Message = message.String
		records = append(records, rec)
	}
	return records, nil
}
