package db

import (
	"context"
	"database/sql"
	"errors"
	"time"
)

var _ = sql.Drivers // ensure database/sql import survives if Go vet removes unused references

// RatioSnapshotRow is the typed shape inserted by SnapshotWriter. Pointer
// fields are NULL-able. simulation_id is the Phase 8 escape hatch — Phase 1
// always passes nil.
type RatioSnapshotRow struct {
	TrackerID                string
	Timestamp                time.Time
	SimulationID             *int64
	RealUploadedBytes        *int64
	RealDownloadedBytes      *int64
	RealRatio                *float64
	DisplayedUploadedBytes   *int64
	DisplayedDownloadedBytes *int64
	DisplayedRatio           *float64
	BonusPoints              *int64
	UnsatCount               *int64
	UnsatLimit               *int64
	ClassOrRank              string
	RawJSON                  string
}

// SnapshotWriter dual-writes ratio snapshots to SQLite (transactional) and
// DuckDB (analytical) per PROJECT.md §5.4. The DuckDB handle may be nil
// (when the binary is built with -tags no_duckdb), in which case the
// analytical write is silently skipped.
type SnapshotWriter struct {
	sqlite *sql.DB
	duck   *sql.DB
}

// NewSnapshotWriter constructs a writer. sqliteDB must be non-nil; duckDB
// may be nil.
func NewSnapshotWriter(sqliteDB, duckDB *sql.DB) *SnapshotWriter {
	return &SnapshotWriter{sqlite: sqliteDB, duck: duckDB}
}

// WriteRatio inserts the row into both stores. A failure on the SQLite write
// returns an error; a failure on the DuckDB write is logged via the returned
// error wrapped with a "duckdb" prefix so callers can decide whether to
// treat it as fatal (in tests: yes; in the live scheduler: no, just record an
// event).
func (w *SnapshotWriter) WriteRatio(ctx context.Context, r RatioSnapshotRow) error {
	if w.sqlite == nil {
		return errors.New("snapshots: sqlite handle is nil")
	}
	_, err := w.sqlite.ExecContext(ctx, `
		INSERT INTO ratio_snapshots
		  (tracker_id, timestamp, simulation_id,
		   real_uploaded_bytes, real_downloaded_bytes, real_ratio,
		   displayed_uploaded_bytes, displayed_downloaded_bytes, displayed_ratio,
		   bonus_points, unsat_count, unsat_limit, class_or_rank, raw_json)
		VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
		r.TrackerID, r.Timestamp.Unix(), nullInt(r.SimulationID),
		nullInt(r.RealUploadedBytes), nullInt(r.RealDownloadedBytes), nullFloat(r.RealRatio),
		nullInt(r.DisplayedUploadedBytes), nullInt(r.DisplayedDownloadedBytes), nullFloat(r.DisplayedRatio),
		nullInt(r.BonusPoints), nullInt(r.UnsatCount), nullInt(r.UnsatLimit),
		nullString(r.ClassOrRank), nullString(r.RawJSON),
	)
	if err != nil {
		return err
	}

	if w.duck == nil {
		return nil
	}
	_, err = w.duck.ExecContext(ctx, `
		INSERT INTO ratio_snapshots
		  (tracker_id, timestamp, simulation_id,
		   real_uploaded_bytes, real_downloaded_bytes, real_ratio,
		   displayed_uploaded_bytes, displayed_downloaded_bytes, displayed_ratio,
		   bonus_points, unsat_count, unsat_limit, class_or_rank, raw_json)
		VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
		r.TrackerID, r.Timestamp.Unix(), nullInt(r.SimulationID),
		nullInt(r.RealUploadedBytes), nullInt(r.RealDownloadedBytes), nullFloat(r.RealRatio),
		nullInt(r.DisplayedUploadedBytes), nullInt(r.DisplayedDownloadedBytes), nullFloat(r.DisplayedRatio),
		nullInt(r.BonusPoints), nullInt(r.UnsatCount), nullInt(r.UnsatLimit),
		nullString(r.ClassOrRank), nullString(r.RawJSON),
	)
	if err != nil {
		return errors.New("duckdb: " + err.Error())
	}
	return nil
}

// UpsertTracker writes the operator's tracker config into the SQLite trackers
// table so foreign-key references from ratio_snapshots, torrent_trackers, and
// torrents are satisfiable. The YAML file remains the source of truth;
// SQLite is a join target.
func (w *SnapshotWriter) UpsertTracker(ctx context.Context, t TrackerRow) error {
	if w.sqlite == nil {
		return errors.New("snapshots: sqlite handle is nil")
	}
	now := time.Now().Unix()
	_, err := w.sqlite.ExecContext(ctx, `
		INSERT INTO trackers
		  (id, name, type, base_url, config_json, enabled,
		   scrape_interval_seconds, scrape_jitter_seconds, use_byparr,
		   created_at, updated_at)
		VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
		ON CONFLICT(id) DO UPDATE SET
		  name = excluded.name,
		  type = excluded.type,
		  base_url = excluded.base_url,
		  config_json = excluded.config_json,
		  enabled = excluded.enabled,
		  scrape_interval_seconds = excluded.scrape_interval_seconds,
		  scrape_jitter_seconds = excluded.scrape_jitter_seconds,
		  use_byparr = excluded.use_byparr,
		  updated_at = excluded.updated_at`,
		t.ID, t.Name, t.Type, t.BaseURL, t.ConfigJSON, boolInt(t.Enabled),
		t.ScrapeIntervalSeconds, t.ScrapeJitterSeconds, boolInt(t.UseByparr),
		now, now,
	)
	return err
}

// TorrentSnapshotRow is the typed shape inserted by WriteTorrentSnapshot.
type TorrentSnapshotRow struct {
	InfoHash         string
	ClientID         string
	Timestamp        time.Time
	SimulationID     *int64
	UploadedBytes    *int64
	DownloadedBytes  *int64
	State            string
	Ratio            *float64
	Seeders          *int64
	Leechers         *int64
	UploadSpeedBps   *int64
	DownloadSpeedBps *int64
}

// WriteTorrentSnapshot dual-writes one per-torrent snapshot row to SQLite
// and DuckDB. Mirrors WriteRatio in shape.
func (w *SnapshotWriter) WriteTorrentSnapshot(ctx context.Context, r TorrentSnapshotRow) error {
	if w.sqlite == nil {
		return errors.New("snapshots: sqlite handle is nil")
	}
	state := nullString(r.State)
	_, err := w.sqlite.ExecContext(ctx, `
		INSERT INTO torrent_snapshots
		  (info_hash, client_id, timestamp, simulation_id,
		   uploaded_bytes, downloaded_bytes, state, ratio,
		   seeders, leechers, upload_speed_bps, download_speed_bps)
		VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
		r.InfoHash, r.ClientID, r.Timestamp.Unix(), nullInt(r.SimulationID),
		nullInt(r.UploadedBytes), nullInt(r.DownloadedBytes), state, nullFloat(r.Ratio),
		nullInt(r.Seeders), nullInt(r.Leechers),
		nullInt(r.UploadSpeedBps), nullInt(r.DownloadSpeedBps),
	)
	if err != nil {
		return err
	}
	if w.duck == nil {
		return nil
	}
	_, err = w.duck.ExecContext(ctx, `
		INSERT INTO torrent_snapshots
		  (info_hash, client_id, timestamp, simulation_id,
		   uploaded_bytes, downloaded_bytes, state, ratio,
		   seeders, leechers, upload_speed_bps, download_speed_bps)
		VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
		r.InfoHash, r.ClientID, r.Timestamp.Unix(), nullInt(r.SimulationID),
		nullInt(r.UploadedBytes), nullInt(r.DownloadedBytes), state, nullFloat(r.Ratio),
		nullInt(r.Seeders), nullInt(r.Leechers),
		nullInt(r.UploadSpeedBps), nullInt(r.DownloadSpeedBps),
	)
	if err != nil {
		return errors.New("duckdb: " + err.Error())
	}
	return nil
}

// TorrentClientRow is the operator-facing torrent client config flattened
// into the schema shape.
type TorrentClientRow struct {
	ID                  string
	Name                string
	Type                string
	BaseURL             string
	ProxyViaQUI         bool
	ConfigJSON          string
	Enabled             bool
	PollIntervalSeconds int
}

// UpsertTorrentClient keeps the SQLite torrent_clients table in sync with
// `config/torrent-clients.yaml` so torrent_snapshots foreign keys resolve.
func (w *SnapshotWriter) UpsertTorrentClient(ctx context.Context, c TorrentClientRow) error {
	if w.sqlite == nil {
		return errors.New("snapshots: sqlite handle is nil")
	}
	now := time.Now().Unix()
	_, err := w.sqlite.ExecContext(ctx, `
		INSERT INTO torrent_clients
		  (id, name, type, base_url, proxy_via_qui, config_json,
		   enabled, poll_interval_seconds, created_at, updated_at)
		VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
		ON CONFLICT(id) DO UPDATE SET
		  name = excluded.name,
		  type = excluded.type,
		  base_url = excluded.base_url,
		  proxy_via_qui = excluded.proxy_via_qui,
		  config_json = excluded.config_json,
		  enabled = excluded.enabled,
		  poll_interval_seconds = excluded.poll_interval_seconds,
		  updated_at = excluded.updated_at`,
		c.ID, c.Name, c.Type, c.BaseURL, boolInt(c.ProxyViaQUI), c.ConfigJSON,
		boolInt(c.Enabled), c.PollIntervalSeconds, now, now,
	)
	return err
}

// UpsertTorrent records or refreshes one torrent row. Called by the snapshot
// collector when it sees a torrent for the first time or when last_seen_at
// should be bumped.
func (w *SnapshotWriter) UpsertTorrent(ctx context.Context, hash, name string, sizeBytes int64, category, tags string, sourceTrackerID string) error {
	if w.sqlite == nil {
		return errors.New("snapshots: sqlite handle is nil")
	}
	now := time.Now().Unix()
	var srcTracker any
	if sourceTrackerID != "" {
		srcTracker = sourceTrackerID
	}
	// On qBit /sync/maindata polls after the initial full update, each
	// torrent entry contains ONLY the fields that changed (a diff). Fields
	// not in the diff arrive as Go zero values (Name="", Size=0). Without
	// the COALESCE/NULLIF guards below, the diff would overwrite the
	// correctly-populated values from the first full poll with empty
	// strings and zero sizes on every subsequent poll cycle. Category and
	// tags can be legitimately cleared by the operator, so they pass
	// through; last_seen_at always updates.
	_, err := w.sqlite.ExecContext(ctx, `
		INSERT INTO torrents
		  (info_hash, name, size_bytes, category, tags,
		   first_seen_at, last_seen_at, source_tracker_id)
		VALUES (?, ?, ?, ?, ?, ?, ?, ?)
		ON CONFLICT(info_hash) DO UPDATE SET
		  name = CASE WHEN excluded.name = '' THEN torrents.name ELSE excluded.name END,
		  size_bytes = CASE WHEN excluded.size_bytes = 0 THEN torrents.size_bytes ELSE excluded.size_bytes END,
		  category = CASE WHEN excluded.category IS NULL THEN torrents.category ELSE excluded.category END,
		  tags = CASE WHEN excluded.tags IS NULL THEN torrents.tags ELSE excluded.tags END,
		  last_seen_at = excluded.last_seen_at`,
		hash, name, sizeBytes, nullString(category), nullString(tags), now, now, srcTracker,
	)
	return err
}

// AutomationToolRow flattens an automation tool config for the SQLite
// automation_tools table.
type AutomationToolRow struct {
	ID                  string
	Name                string
	Type                string
	BaseURL             string
	ConfigJSON          string
	WebhookToken        string
	Enabled             bool
	PollIntervalSeconds int
}

// UpsertAutomationTool keeps automation_tools in sync with
// automation-tools.yaml so foreign keys from filter_performance resolve.
func (w *SnapshotWriter) UpsertAutomationTool(ctx context.Context, t AutomationToolRow) error {
	if w.sqlite == nil {
		return errors.New("snapshots: sqlite handle is nil")
	}
	now := time.Now().Unix()
	_, err := w.sqlite.ExecContext(ctx, `
		INSERT INTO automation_tools
		  (id, name, type, base_url, config_json, webhook_token,
		   enabled, poll_interval_seconds, created_at, updated_at)
		VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
		ON CONFLICT(id) DO UPDATE SET
		  name = excluded.name,
		  type = excluded.type,
		  base_url = excluded.base_url,
		  config_json = excluded.config_json,
		  webhook_token = excluded.webhook_token,
		  enabled = excluded.enabled,
		  poll_interval_seconds = excluded.poll_interval_seconds,
		  updated_at = excluded.updated_at`,
		t.ID, t.Name, t.Type, t.BaseURL, t.ConfigJSON, nullString(t.WebhookToken),
		boolInt(t.Enabled), t.PollIntervalSeconds, now, now,
	)
	return err
}

// TrackerRow is the operator-facing tracker config flattened into the schema
// shape. The loader (internal/config) constructs these from trackers.yaml.
type TrackerRow struct {
	ID                    string
	Name                  string
	Type                  string
	BaseURL               string
	ConfigJSON            string
	Enabled               bool
	ScrapeIntervalSeconds int
	ScrapeJitterSeconds   int
	UseByparr             bool
}

func nullInt(p *int64) any {
	if p == nil {
		return nil
	}
	return *p
}
func nullFloat(p *float64) any {
	if p == nil {
		return nil
	}
	return *p
}
func nullString(s string) any {
	if s == "" {
		return nil
	}
	return s
}
func boolInt(b bool) int {
	if b {
		return 1
	}
	return 0
}