// Package repository provides typed access to the SQLite tables.
// Every function takes the shared *sql.DB so the repository is stateless and
// safe to call from the bot goroutine and the web handlers at the same time.
package repository

import (
	"database/sql"
	"errors"
	"fmt"
	"strings"

	"github.com/z/tg-apicreator-bot/internal/database"
)

// Common errors let handlers branch without string matching.
var (
	ErrNotFound = errors.New("repository: record not found")
)

// Repository bundles every store together. Construct one at startup and pass
// it around; the underlying *sql.DB does the connection pooling.
type Repository struct {
	db *sql.DB
}

func New(db *sql.DB) *Repository { return &Repository{db: db} }

// ---------------- users ----------------

// UpsertUser inserts a new user or refreshes their profile / last_seen.
func (r *Repository) UpsertUser(u database.User) (database.User, error) {
	q := `INSERT INTO users (tg_id, username, first_name, last_name, language, is_admin, last_seen)
	      VALUES (?, ?, ?, ?, ?, ?, datetime('now'))
	      ON CONFLICT(tg_id) DO UPDATE SET
	        username = excluded.username,
	        first_name = excluded.first_name,
	        last_name = excluded.last_name,
	        last_seen = datetime('now')
	      RETURNING id, tg_id, username, first_name, last_name, language, is_banned, is_admin, created_count, created_at, last_seen`
	row := r.db.QueryRow(q, u.TgID, nullString(u.Username), nullString(u.FirstName),
		nullString(u.LastName), defaultStr(u.Language, "fa"), u.IsAdmin)
	return scanUser(row)
}

func (r *Repository) GetUserByTgID(tgID int64) (database.User, error) {
	row := r.db.QueryRow(`SELECT id, tg_id, username, first_name, last_name, language,
		is_banned, is_admin, created_count, created_at, last_seen FROM users WHERE tg_id = ?`, tgID)
	return scanUser(row)
}

func (r *Repository) SetLanguage(id int64, lang string) error {
	_, err := r.db.Exec(`UPDATE users SET language = ? WHERE id = ?`, lang, id)
	return err
}

func (r *Repository) SetBanned(tgID int64, banned bool) error {
	v := 0
	if banned {
		v = 1
	}
	res, err := r.db.Exec(`UPDATE users SET is_banned = ? WHERE tg_id = ?`, v, tgID)
	if err != nil {
		return err
	}
	return assertAffected(res)
}

func (r *Repository) IncrementCreatedCount(id int64) error {
	_, err := r.db.Exec(`UPDATE users SET created_count = created_count + 1 WHERE id = ?`, id)
	return err
}

func (r *Repository) ListUsers(limit, offset int) ([]database.User, int, error) {
	var total int
	if err := r.db.QueryRow(`SELECT COUNT(*) FROM users`).Scan(&total); err != nil {
		return nil, 0, err
	}
	rows, err := r.db.Query(`SELECT id, tg_id, username, first_name, last_name, language,
		is_banned, is_admin, created_count, created_at, last_seen
		FROM users ORDER BY id DESC LIMIT ? OFFSET ?`, limit, offset)
	if err != nil {
		return nil, 0, err
	}
	defer rows.Close()
	var out []database.User
	for rows.Next() {
		u, err := scanUserRows(rows)
		if err != nil {
			return nil, 0, err
		}
		out = append(out, u)
	}
	return out, total, rows.Err()
}

// ---------------- api keys ----------------

func (r *Repository) CreateAPIKey(k database.APIKey) (int64, error) {
	res, err := r.db.Exec(`INSERT INTO api_keys (user_id, tg_id, phone, app_title, app_shortname,
		app_platform, api_id, api_hash, status) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,
		k.UserID, k.TgID, k.Phone, k.AppTitle, k.AppShortname, k.AppPlatform, k.APIID, k.APIHash, "active")
	if err != nil {
		return 0, err
	}
	return res.LastInsertId()
}

func (r *Repository) ListAPIKeysByUser(userID int64) ([]database.APIKey, error) {
	rows, err := r.db.Query(`SELECT id, user_id, tg_id, phone, app_title, app_shortname, app_platform,
		api_id, api_hash, status, created_at FROM api_keys WHERE user_id = ? ORDER BY id DESC`, userID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	return scanAPIKeys(rows)
}

func (r *Repository) ListAPIKeys(limit, offset int) ([]database.APIKey, int, error) {
	var total int
	if err := r.db.QueryRow(`SELECT COUNT(*) FROM api_keys`).Scan(&total); err != nil {
		return nil, 0, err
	}
	rows, err := r.db.Query(`SELECT id, user_id, tg_id, phone, app_title, app_shortname, app_platform,
		api_id, api_hash, status, created_at FROM api_keys ORDER BY id DESC LIMIT ? OFFSET ?`, limit, offset)
	if err != nil {
		return nil, 0, err
	}
	defer rows.Close()
	ks, err := scanAPIKeys(rows)
	return ks, total, err
}

func (r *Repository) RevokeAPIKey(id int64) error {
	res, err := r.db.Exec(`UPDATE api_keys SET status = 'revoked' WHERE id = ?`, id)
	if err != nil {
		return err
	}
	return assertAffected(res)
}

func (r *Repository) DeleteAPIKey(id int64) error {
	res, err := r.db.Exec(`DELETE FROM api_keys WHERE id = ?`, id)
	if err != nil {
		return err
	}
	return assertAffected(res)
}

// ---------------- proxies ----------------

func (r *Repository) CreateProxy(p database.Proxy) (int64, error) {
	res, err := r.db.Exec(`INSERT INTO proxies (kind, host, port, username, password, enabled)
		VALUES (?, ?, ?, ?, ?, ?)`, p.Kind, p.Host, p.Port, nullString(p.Username), nullString(p.Password), boolToInt(p.Enabled))
	if err != nil {
		return 0, err
	}
	return res.LastInsertId()
}

func (r *Repository) ListProxies() ([]database.Proxy, error) {
	rows, err := r.db.Query(`SELECT id, kind, host, port, username, password, enabled, last_used,
		fail_count, created_at FROM proxies ORDER BY id DESC`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []database.Proxy
	for rows.Next() {
		var p database.Proxy
		var en int
		var uname, passw sql.NullString
		if err := rows.Scan(&p.ID, &p.Kind, &p.Host, &p.Port, &uname, &passw, &en,
			&p.LastUsed, &p.FailCount, &p.CreatedAt); err != nil {
			return nil, err
		}
		p.Username = uname.String
		p.Password = passw.String
		p.Enabled = en == 1
		out = append(out, p)
	}
	return out, rows.Err()
}

func (r *Repository) ListEnabledProxies() ([]database.Proxy, error) {
	all, err := r.ListProxies()
	if err != nil {
		return nil, err
	}
	var out []database.Proxy
	for _, p := range all {
		if p.Enabled {
			out = append(out, p)
		}
	}
	return out, nil
}

func (r *Repository) ToggleProxy(id int64, enabled bool) error {
	v := boolToInt(enabled)
	res, err := r.db.Exec(`UPDATE proxies SET enabled = ? WHERE id = ?`, v, id)
	if err != nil {
		return err
	}
	return assertAffected(res)
}

func (r *Repository) DeleteProxy(id int64) error {
	res, err := r.db.Exec(`DELETE FROM proxies WHERE id = ?`, id)
	if err != nil {
		return err
	}
	return assertAffected(res)
}

func (r *Repository) MarkProxyUsed(id int64, failed bool) error {
	if failed {
		_, err := r.db.Exec(`UPDATE proxies SET fail_count = fail_count + 1, last_used = datetime('now') WHERE id = ?`, id)
		return err
	}
	_, err := r.db.Exec(`UPDATE proxies SET fail_count = 0, last_used = datetime('now') WHERE id = ?`, id)
	return err
}

// ---------------- daily usage / rate limit ----------------

// IncrementDaily atomically bumps the per-user, per-day counter and returns
// the new value. Day is "YYYY-MM-DD" in UTC.
func (r *Repository) IncrementDaily(userID int64, day string) (int, error) {
	var count int
	err := r.db.QueryRow(`INSERT INTO daily_usage (user_id, day, count) VALUES (?, ?, 1)
		ON CONFLICT(user_id, day) DO UPDATE SET count = count + 1
		RETURNING count`, userID, day).Scan(&count)
	return count, err
}

func (r *Repository) DailyCount(userID int64, day string) (int, error) {
	var count int
	err := r.db.QueryRow(`SELECT count FROM daily_usage WHERE user_id = ? AND day = ?`, userID, day).Scan(&count)
	if err == sql.ErrNoRows {
		return 0, nil
	}
	return count, err
}

// ---------------- settings ----------------

func (r *Repository) GetSetting(key string) (string, error) {
	var v string
	err := r.db.QueryRow(`SELECT value FROM settings WHERE key = ?`, key).Scan(&v)
	if err == sql.ErrNoRows {
		return "", nil
	}
	return v, err
}

func (r *Repository) SetSetting(key, value string) error {
	_, err := r.db.Exec(`INSERT INTO settings (key, value) VALUES (?, ?)
		ON CONFLICT(key) DO UPDATE SET value = excluded.value`, key, value)
	return err
}

// ---------------- logs ----------------

func (r *Repository) AddLog(level, source string, userID sql.NullInt64, message string) error {
	_, err := r.db.Exec(`INSERT INTO logs (level, source, user_id, message) VALUES (?, ?, ?, ?)`,
		level, source, userID, message)
	return err
}

func (r *Repository) ListLogs(limit, offset int, level string) ([]database.LogEntry, int, error) {
	var total int
	if level == "" {
		if err := r.db.QueryRow(`SELECT COUNT(*) FROM logs`).Scan(&total); err != nil {
			return nil, 0, err
		}
	} else {
		if err := r.db.QueryRow(`SELECT COUNT(*) FROM logs WHERE level = ?`, level).Scan(&total); err != nil {
			return nil, 0, err
		}
	}

	var rows *sql.Rows
	var err error
	if level == "" {
		rows, err = r.db.Query(`SELECT id, level, source, user_id, message, created_at
			FROM logs ORDER BY id DESC LIMIT ? OFFSET ?`, limit, offset)
	} else {
		rows, err = r.db.Query(`SELECT id, level, source, user_id, message, created_at
			FROM logs ORDER BY id DESC LIMIT ? OFFSET ?`, limit, offset)
	}
	if err != nil {
		return nil, 0, err
	}
	defer rows.Close()
	var out []database.LogEntry
	for rows.Next() {
		var l database.LogEntry
		if err := rows.Scan(&l.ID, &l.Level, &l.Source, &l.UserID, &l.Message, &l.CreatedAt); err != nil {
			return nil, 0, err
		}
		out = append(out, l)
	}
	return out, total, rows.Err()
}

// ---------------- dashboard stats ----------------

type Stats struct {
	Users      int
	Keys       int
	KeysToday  int
	Proxies    int
	ActiveProx int
	Banned     int
}

func (r *Repository) Stats() (Stats, error) {
	var s Stats
	queries := []struct {
		sql string
		dst *int
	}{
		{`SELECT COUNT(*) FROM users`, &s.Users},
		{`SELECT COUNT(*) FROM api_keys`, &s.Keys},
		{`SELECT COUNT(*) FROM api_keys WHERE date(created_at) = date('now')`, &s.KeysToday},
		{`SELECT COUNT(*) FROM proxies`, &s.Proxies},
		{`SELECT COUNT(*) FROM proxies WHERE enabled = 1`, &s.ActiveProx},
		{`SELECT COUNT(*) FROM users WHERE is_banned = 1`, &s.Banned},
	}
	for _, q := range queries {
		if err := r.db.QueryRow(q.sql).Scan(q.dst); err != nil {
			return s, err
		}
	}
	return s, nil
}

// ---------------- helpers ----------------

func scanUser(row *sql.Row) (database.User, error) {
	var u database.User
	var uname, fname, lname sql.NullString
	err := row.Scan(&u.ID, &u.TgID, &uname, &fname, &lname, &u.Language, &u.IsBanned,
		&u.IsAdmin, &u.CreatedCount, &u.CreatedAt, &u.LastSeen)
	if err != nil {
		if err == sql.ErrNoRows {
			return u, ErrNotFound
		}
		return u, err
	}
	u.Username = uname.String
	u.FirstName = fname.String
	u.LastName = lname.String
	return u, nil
}

type rowScanner interface {
	Scan(dest ...any) error
}

func scanUserRows(row rowScanner) (database.User, error) {
	var u database.User
	var uname, fname, lname sql.NullString
	if err := row.Scan(&u.ID, &u.TgID, &uname, &fname, &lname, &u.Language, &u.IsBanned,
		&u.IsAdmin, &u.CreatedCount, &u.CreatedAt, &u.LastSeen); err != nil {
		return u, err
	}
	u.Username = uname.String
	u.FirstName = fname.String
	u.LastName = lname.String
	return u, nil
}

func scanAPIKeys(rows *sql.Rows) ([]database.APIKey, error) {
	var out []database.APIKey
	for rows.Next() {
		var k database.APIKey
		if err := rows.Scan(&k.ID, &k.UserID, &k.TgID, &k.Phone, &k.AppTitle, &k.AppShortname,
			&k.AppPlatform, &k.APIID, &k.APIHash, &k.Status, &k.CreatedAt); err != nil {
			return nil, err
		}
		out = append(out, k)
	}
	return out, rows.Err()
}

func nullString(s string) any {
	if s == "" {
		return nil
	}
	return s
}

func defaultStr(s, def string) string {
	if s == "" {
		return def
	}
	return s
}

func boolToInt(b bool) int {
	if b {
		return 1
	}
	return 0
}

func assertAffected(res sql.Result) error {
	n, err := res.RowsAffected()
	if err != nil {
		return err
	}
	if n == 0 {
		return ErrNotFound
	}
	return nil
}

// SanitizeLike escapes user input used in LIKE clauses.
func SanitizeLike(s string) string {
	r := strings.NewReplacer("%", "\\%", "_", "\\_", "\\", "\\\\")
	return r.Replace(s)
}

// QuoteList builds a (?, ?, ...) placeholder list of the given size.
func QuoteList(n int) string {
	if n <= 0 {
		return ""
	}
	return "(" + strings.Repeat("?,", n-1) + "?)"
}

// helper to format errors uniformly for logging
func wrap(who string, err error) error {
	if err == nil {
		return nil
	}
	return fmt.Errorf("%s: %w", who, err)
}

// quiet unused-export warning
var _ = wrap
