package storage

import (
	"database/sql"
	"sort"
	"time"
)

type DeviceConfigStateRecord struct {
	DeviceID          string
	TemplateName      string
	ProfileName       string
	OverlaysJSON      string
	ConfigID          string
	ConfigVersion     string
	LastAppliedTaskID string
	UpdatedAt         string
}

type DeviceConfigStateRepo struct {
	db *sql.DB
}

func NewDeviceConfigStateRepo(db *sql.DB) *DeviceConfigStateRepo {
	return &DeviceConfigStateRepo{db: db}
}

func (r *DeviceConfigStateRepo) Upsert(state DeviceConfigStateRecord) error {
	if r == nil || r.db == nil {
		return nil
	}
	now := time.Now().Format(time.RFC3339)
	_, err := r.db.Exec(`
INSERT INTO device_config_state(device_id, template_name, profile_name, overlays_json, config_id, config_version, last_applied_task_id, updated_at)
VALUES(?, ?, ?, ?, ?, ?, ?, ?)
ON CONFLICT(device_id) DO UPDATE SET
  template_name=excluded.template_name,
  profile_name=excluded.profile_name,
  overlays_json=excluded.overlays_json,
  config_id=excluded.config_id,
  config_version=excluded.config_version,
  last_applied_task_id=excluded.last_applied_task_id,
  updated_at=excluded.updated_at
`, state.DeviceID, state.TemplateName, state.ProfileName, state.OverlaysJSON, state.ConfigID, state.ConfigVersion, state.LastAppliedTaskID, now)
	return err
}

func (r *DeviceConfigStateRepo) UpsertState(deviceID string, templateName string, profileName string, overlaysJSON string, configID string, configVersion string, lastAppliedTaskID string) error {
	return r.Upsert(DeviceConfigStateRecord{
		DeviceID:          deviceID,
		TemplateName:      templateName,
		ProfileName:       profileName,
		OverlaysJSON:      overlaysJSON,
		ConfigID:          configID,
		ConfigVersion:     configVersion,
		LastAppliedTaskID: lastAppliedTaskID,
	})
}

func (r *DeviceConfigStateRepo) Get(deviceID string) (*DeviceConfigStateRecord, error) {
	if r == nil || r.db == nil {
		return nil, nil
	}
	var item DeviceConfigStateRecord
	err := r.db.QueryRow(`
SELECT device_id, template_name, profile_name, overlays_json, config_id, config_version, last_applied_task_id, updated_at
FROM device_config_state
WHERE device_id = ?
`, deviceID).Scan(&item.DeviceID, &item.TemplateName, &item.ProfileName, &item.OverlaysJSON, &item.ConfigID, &item.ConfigVersion, &item.LastAppliedTaskID, &item.UpdatedAt)
	if err == sql.ErrNoRows {
		return nil, nil
	}
	if err != nil {
		return nil, err
	}
	return &item, nil
}

func (r *DeviceConfigStateRepo) ListByProfileName(profileName string) ([]DeviceConfigStateRecord, error) {
	if r == nil || r.db == nil {
		return nil, nil
	}
	rows, err := r.db.Query(`
SELECT device_id, template_name, profile_name, overlays_json, config_id, config_version, last_applied_task_id, updated_at
FROM device_config_state
WHERE profile_name = ?
ORDER BY device_id ASC
`, profileName)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []DeviceConfigStateRecord
	for rows.Next() {
		var item DeviceConfigStateRecord
		if err := rows.Scan(&item.DeviceID, &item.TemplateName, &item.ProfileName, &item.OverlaysJSON, &item.ConfigID, &item.ConfigVersion, &item.LastAppliedTaskID, &item.UpdatedAt); err != nil {
			return nil, err
		}
		items = append(items, item)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	sort.Slice(items, func(i, j int) bool { return items[i].DeviceID < items[j].DeviceID })
	return items, nil
}
