package services

import (
	"database/sql"
	"encoding/json"
	"log"
	"stocksearch/models"
	"time"
)

// --- Rules ---

func dbInsertRule(rule models.AutoTradeRule) {
	if db == nil {
		return
	}
	_, err := db.Exec(`INSERT INTO autotrade_rules
		(id, name, enabled, min_rise_score, min_cntr_str, require_bullish,
		 order_amount, max_positions, stop_loss1_pct, stop_loss1_count,
		 stop_loss_pct, take_profit_pct, max_hold_minutes, exit_before_close, created_at)
		VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15)`,
		rule.ID, rule.Name, rule.Enabled,
		rule.MinRiseScore, rule.MinCntrStr, rule.RequireBullish,
		rule.OrderAmount, rule.MaxPositions,
		rule.StopLoss1Pct, rule.StopLoss1Count, rule.StopLossPct,
		rule.TakeProfitPct, rule.MaxHoldMinutes, rule.ExitBeforeClose, rule.CreatedAt)
	if err != nil {
		log.Printf("DB 규칙 삽입 실패: %v", err)
	}
}

func dbUpdateRule(rule models.AutoTradeRule) {
	if db == nil {
		return
	}
	_, err := db.Exec(`UPDATE autotrade_rules SET
		name=$2, enabled=$3, min_rise_score=$4, min_cntr_str=$5, require_bullish=$6,
		order_amount=$7, max_positions=$8, stop_loss1_pct=$9, stop_loss1_count=$10,
		stop_loss_pct=$11, take_profit_pct=$12, max_hold_minutes=$13, exit_before_close=$14
		WHERE id=$1`,
		rule.ID, rule.Name, rule.Enabled,
		rule.MinRiseScore, rule.MinCntrStr, rule.RequireBullish,
		rule.OrderAmount, rule.MaxPositions,
		rule.StopLoss1Pct, rule.StopLoss1Count, rule.StopLossPct,
		rule.TakeProfitPct, rule.MaxHoldMinutes, rule.ExitBeforeClose)
	if err != nil {
		log.Printf("DB 규칙 수정 실패: %v", err)
	}
}

func dbDeleteRule(id string) {
	if db == nil {
		return
	}
	if _, err := db.Exec(`DELETE FROM autotrade_rules WHERE id=$1`, id); err != nil {
		log.Printf("DB 규칙 삭제 실패: %v", err)
	}
}

func dbLoadRules() []models.AutoTradeRule {
	if db == nil {
		return nil
	}
	rows, err := db.Query(`SELECT id, name, enabled, min_rise_score, min_cntr_str, require_bullish,
		order_amount, max_positions, stop_loss1_pct, stop_loss1_count,
		stop_loss_pct, take_profit_pct, max_hold_minutes, exit_before_close, created_at
		FROM autotrade_rules ORDER BY created_at`)
	if err != nil {
		log.Printf("DB 규칙 조회 실패: %v", err)
		return nil
	}
	defer rows.Close()

	var rules []models.AutoTradeRule
	for rows.Next() {
		var r models.AutoTradeRule
		if err := rows.Scan(&r.ID, &r.Name, &r.Enabled,
			&r.MinRiseScore, &r.MinCntrStr, &r.RequireBullish,
			&r.OrderAmount, &r.MaxPositions,
			&r.StopLoss1Pct, &r.StopLoss1Count, &r.StopLossPct,
			&r.TakeProfitPct, &r.MaxHoldMinutes, &r.ExitBeforeClose, &r.CreatedAt); err != nil {
			log.Printf("DB 규칙 스캔 실패: %v", err)
			continue
		}
		rules = append(rules, r)
	}
	return rules
}

// --- Positions ---

func dbInsertPosition(pos *models.AutoTradePosition) {
	if db == nil {
		return
	}
	err := db.QueryRow(`INSERT INTO autotrade_positions
		(code, name, buy_price, qty, order_no, entry_time, rule_id,
		 stop_loss1, stop_loss1_touches, stop_loss, take_profit, status)
		VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12) RETURNING id`,
		pos.Code, pos.Name, pos.BuyPrice, pos.Qty, pos.OrderNo, pos.EntryTime, pos.RuleID,
		pos.StopLoss1, pos.StopLoss1Touches, pos.StopLoss, pos.TakeProfit, pos.Status).Scan(&pos.DBID)
	if err != nil {
		log.Printf("DB 포지션 삽입 실패: %v", err)
	}
}

func dbUpdatePosition(pos *models.AutoTradePosition) {
	if db == nil {
		return
	}
	var exitTime *time.Time
	if !pos.ExitTime.IsZero() {
		exitTime = &pos.ExitTime
	}
	_, err := db.Exec(`UPDATE autotrade_positions SET
		buy_price=$2, qty=$3, order_no=$4, stop_loss1=$5, stop_loss1_touches=$6,
		stop_loss=$7, take_profit=$8, status=$9, exit_time=$10, exit_price=$11, exit_reason=$12
		WHERE id=$1`,
		pos.DBID, pos.BuyPrice, pos.Qty, pos.OrderNo,
		pos.StopLoss1, pos.StopLoss1Touches, pos.StopLoss, pos.TakeProfit,
		pos.Status, exitTime, pos.ExitPrice, pos.ExitReason)
	if err != nil {
		log.Printf("DB 포지션 수정 실패: %v", err)
	}
}

func dbLoadActivePositions() map[string]*models.AutoTradePosition {
	if db == nil {
		return nil
	}
	rows, err := db.Query(`SELECT id, code, name, buy_price, qty, order_no, entry_time, rule_id,
		stop_loss1, stop_loss1_touches, stop_loss, take_profit, status
		FROM autotrade_positions WHERE status IN ('pending', 'open')`)
	if err != nil {
		log.Printf("DB 활성 포지션 조회 실패: %v", err)
		return nil
	}
	defer rows.Close()

	positions := make(map[string]*models.AutoTradePosition)
	for rows.Next() {
		var p models.AutoTradePosition
		if err := rows.Scan(&p.DBID, &p.Code, &p.Name, &p.BuyPrice, &p.Qty, &p.OrderNo,
			&p.EntryTime, &p.RuleID, &p.StopLoss1, &p.StopLoss1Touches,
			&p.StopLoss, &p.TakeProfit, &p.Status); err != nil {
			log.Printf("DB 포지션 스캔 실패: %v", err)
			continue
		}
		positions[p.Code] = &p
	}
	return positions
}

func dbLoadClosedPositions(limit int) []*models.AutoTradePosition {
	if db == nil {
		return nil
	}
	rows, err := db.Query(`SELECT id, code, name, buy_price, qty, order_no, entry_time, rule_id,
		stop_loss1, stop_loss1_touches, stop_loss, take_profit, status,
		exit_time, exit_price, exit_reason
		FROM autotrade_positions WHERE status='closed'
		ORDER BY exit_time DESC LIMIT $1`, limit)
	if err != nil {
		log.Printf("DB 종료 포지션 조회 실패: %v", err)
		return nil
	}
	defer rows.Close()

	var positions []*models.AutoTradePosition
	for rows.Next() {
		var p models.AutoTradePosition
		var exitTime sql.NullTime
		if err := rows.Scan(&p.DBID, &p.Code, &p.Name, &p.BuyPrice, &p.Qty, &p.OrderNo,
			&p.EntryTime, &p.RuleID, &p.StopLoss1, &p.StopLoss1Touches,
			&p.StopLoss, &p.TakeProfit, &p.Status,
			&exitTime, &p.ExitPrice, &p.ExitReason); err != nil {
			log.Printf("DB 종료 포지션 스캔 실패: %v", err)
			continue
		}
		if exitTime.Valid {
			p.ExitTime = exitTime.Time
		}
		positions = append(positions, &p)
	}
	return positions
}

func dbGetTodayStats() (tradeCount int, totalPL int64) {
	if db == nil {
		return 0, 0
	}
	today := time.Now().Truncate(24 * time.Hour)
	row := db.QueryRow(`SELECT COALESCE(COUNT(*), 0), COALESCE(SUM((exit_price - buy_price) * qty), 0)
		FROM autotrade_positions WHERE status='closed' AND exit_time >= $1`, today)
	if err := row.Scan(&tradeCount, &totalPL); err != nil {
		log.Printf("DB 통계 조회 실패: %v", err)
	}
	return
}

// --- Logs ---

func dbInsertLog(l models.AutoTradeLog) {
	if db == nil {
		return
	}
	_, err := db.Exec(`INSERT INTO autotrade_logs (at, level, message, code) VALUES ($1,$2,$3,$4)`,
		l.At, l.Level, l.Message, l.Code)
	if err != nil {
		log.Printf("DB 로그 삽입 실패: %v", err)
	}
}

func dbLoadRecentLogs(limit int) []models.AutoTradeLog {
	if db == nil {
		return nil
	}
	rows, err := db.Query(`SELECT at, level, message, code FROM autotrade_logs
		ORDER BY at DESC LIMIT $1`, limit)
	if err != nil {
		log.Printf("DB 로그 조회 실패: %v", err)
		return nil
	}
	defer rows.Close()

	var logs []models.AutoTradeLog
	for rows.Next() {
		var l models.AutoTradeLog
		if err := rows.Scan(&l.At, &l.Level, &l.Message, &l.Code); err != nil {
			log.Printf("DB 로그 스캔 실패: %v", err)
			continue
		}
		logs = append(logs, l)
	}
	return logs
}

// --- WatchSource ---

func dbUpsertWatchSource(ws models.AutoTradeWatchSource) {
	if db == nil {
		return
	}
	themesJSON, _ := json.Marshal(ws.SelectedThemes)
	_, err := db.Exec(`INSERT INTO autotrade_watch_source (id, use_scanner, selected_themes)
		VALUES (1, $1, $2)
		ON CONFLICT (id) DO UPDATE SET use_scanner=$1, selected_themes=$2`,
		ws.UseScanner, themesJSON)
	if err != nil {
		log.Printf("DB 감시소스 저장 실패: %v", err)
	}
}

func dbLoadWatchSource() *models.AutoTradeWatchSource {
	if db == nil {
		return nil
	}
	var ws models.AutoTradeWatchSource
	var themesJSON []byte
	err := db.QueryRow(`SELECT use_scanner, selected_themes FROM autotrade_watch_source WHERE id=1`).
		Scan(&ws.UseScanner, &themesJSON)
	if err != nil {
		if err == sql.ErrNoRows {
			return nil
		}
		log.Printf("DB 감시소스 조회 실패: %v", err)
		return nil
	}
	if err := json.Unmarshal(themesJSON, &ws.SelectedThemes); err != nil {
		ws.SelectedThemes = []models.ThemeRef{}
	}
	return &ws
}