package main

import (
	"context"
	"fmt"
	"io"
	"math"
	"strconv"
	"strings"
	"time"
	"unicode"

	"github.com/jackc/pgx/v5"
	"github.com/xuri/excelize/v2"
)

// ============================================================================
// Data types
// ============================================================================

// AssetRow holds one row from the Asset ID Report XLSX.
// Column order (0-indexed, 19 columns):
//   0  S.No            1  Customer Name    2  Location         3  Manufacturer
//   4  Category        5  Sub-Category     6  Asset Name       7  Asset Model
//   8  Asset Criticality  9  Asset ID      10 Purchase Number  11 Purchase Date
//   12 Purchase Value  13 Service Provider 14 Status           15 Reason Inactive
//   16 Schedules Configured  17 Schedules Initiated  18 Schedule Assigned
type AssetRow struct {
	SeqNo               int
	CustomerName        string
	Location            string
	Manufacturer        string
	Category            string
	SubCategory         string
	AssetName           string
	AssetModel          string
	AssetCriticality    string
	AssetID             string // report_asset_id → becomes ic3_asset_master.asset_id
	PurchaseNumber      string
	PurchaseDate        *time.Time
	PurchaseValue       *float64
	ServiceProviderName string
	Status              int    // 1=Active, 0=Inactive
	ReasonForInactive   string
	ScheduleConfigured  int
	ScheduleInitiated   int
	ScheduleAssigned    string
}

// ImportResult summarises one bulk import run.
type ImportResult struct {
	Total    int    `json:"total"`
	Inserted int    `json:"inserted"`
	Updated  int    `json:"updated"`
	Skipped  int    `json:"skipped"`
	Duration string `json:"duration_ms"`
}

// AssetRecord is the JSON shape for list/get endpoints.
type AssetRecord struct {
	AssetID              string     `json:"asset_id"`
	AssetName            string     `json:"asset_name"`
	AssetType            string     `json:"asset_type"`
	AssetCategory        *string    `json:"asset_category"`
	AssetSubCategory     *string    `json:"asset_sub_category"`
	Criticality          string     `json:"criticality"`
	Traceid              *int       `json:"traceid"`
	CustomerName         *string    `json:"customer_name"`
	LocationName         *string    `json:"location_name"`
	BuildingName         *string    `json:"building_name"`
	SiteID               *string    `json:"site_id"`
	Department           *string    `json:"department"`
	Manufacturer         *string    `json:"manufacturer"`
	ModelNo              *string    `json:"model_no"`
	SerialNo             *string    `json:"serial_no"`
	CapacityRating       *string    `json:"capacity_rating"`
	AssetStatus          string     `json:"asset_status"`
	AssetLiveStatus      int        `json:"asset_live_status"`
	IotDeviceMapped      int        `json:"iot_device_mapped"`
	ScheduleConfigured   int        `json:"schedule_count_configured"`
	ScheduleInitiated    int        `json:"schedule_count_initiated"`
	Ticketcount          int        `json:"ticketcount"`
	PurchaseDate         *time.Time `json:"purchase_date"`
	PurchaseValue        *float64   `json:"purchase_value"`
	ContractEndDate      *time.Time `json:"contract_end_date"`
	Latitude             *float64   `json:"latitude"`
	Longitude            *float64   `json:"longitude"`
	InstallDate          *time.Time `json:"install_date"`
	LastSyncedAt         *time.Time `json:"last_synced_at"`
	CreatedAt            time.Time  `json:"created_at"`
}

// ============================================================================
// XLSX parser
// ============================================================================

// ParseAssetReportXLSX reads the Asset ID Report from an io.Reader (xlsx format).
func ParseAssetReportXLSX(r io.Reader) ([]AssetRow, error) {
	f, err := excelize.OpenReader(r)
	if err != nil {
		return nil, fmt.Errorf("open xlsx: %w", err)
	}
	defer f.Close()

	sheets := f.GetSheetList()
	if len(sheets) == 0 {
		return nil, fmt.Errorf("xlsx has no sheets")
	}

	rows, err := f.GetRows(sheets[0])
	if err != nil {
		return nil, fmt.Errorf("read rows: %w", err)
	}

	var out []AssetRow
	for i, cols := range rows {
		if i == 0 {
			continue // skip header
		}
		r := parseAssetRow(cols)
		if r.AssetID == "" {
			continue
		}
		out = append(out, r)
	}
	return out, nil
}

func parseAssetRow(cols []string) AssetRow {
	get := func(i int) string {
		if i >= len(cols) {
			return ""
		}
		v := strings.TrimSpace(cols[i])
		if v == "-" || strings.EqualFold(v, "na") || strings.EqualFold(v, "n/a") {
			return ""
		}
		return v
	}
	getInt := func(i int) int {
		n, _ := strconv.Atoi(get(i))
		return n
	}

	r := AssetRow{
		SeqNo:               getInt(0),
		CustomerName:        get(1),
		Location:            get(2),
		Manufacturer:        get(3),
		Category:            normaliseCategory(get(4)),
		SubCategory:         get(5),
		AssetName:           get(6),
		AssetModel:          get(7),
		AssetCriticality:    get(8),
		AssetID:             get(9),
		PurchaseNumber:      get(10),
		ServiceProviderName: get(13),
		ReasonForInactive:   get(15),
		ScheduleConfigured:  getInt(16),
		ScheduleInitiated:   getInt(17),
		ScheduleAssigned:    get(18),
	}

	if strings.EqualFold(get(14), "active") {
		r.Status = 1
	}

	if ds := get(11); ds != "" {
		if t := parseFlexDate(ds); t != nil {
			r.PurchaseDate = t
		}
	}

	if vs := get(12); vs != "" {
		clean := strings.Map(func(c rune) rune {
			if unicode.IsDigit(c) || c == '.' {
				return c
			}
			return -1
		}, vs)
		if v, err := strconv.ParseFloat(clean, 64); err == nil && v > 0 {
			r.PurchaseValue = &v
		}
	}

	return r
}

// normaliseCategory fixes dirty category values from the import.
func normaliseCategory(s string) string {
	switch strings.ToUpper(strings.TrimSpace(s)) {
	case "INSTRUMENTATION", "INSTRUMENATION", "INSTRUMENENTATION":
		return "Instrument"
	case "PRESSURE GAUGE":
		return "Instrument"
	case "SAMPLE CATEGORY", "":
		return "Uncategorised"
	default:
		return s
	}
}

var dateFormats = []string{
	"02-01-2006", "02/01/2006", "2006-01-02",
	"01/02/2006", "Jan 2, 2006", "2 Jan 2006",
}

func parseFlexDate(s string) *time.Time {
	for _, layout := range dateFormats {
		if t, err := time.Parse(layout, s); err == nil {
			return &t
		}
	}
	return nil
}

// ============================================================================
// DB — bulk import into ic3_asset_master (THE single asset table)
// ============================================================================

// BulkImportAssets upserts rows into ic3_asset_master.
// Conflict key: asset_id (= report Asset ID column, unique per asset).
// replace=true truncates the table before inserting.
func (db *DB) BulkImportAssets(ctx context.Context, rows []AssetRow, replace bool) (ImportResult, error) {
	start := time.Now()
	res := ImportResult{Total: len(rows)}

	tx, err := db.pool.Begin(ctx)
	if err != nil {
		return res, fmt.Errorf("begin tx: %w", err)
	}
	defer tx.Rollback(ctx)

	if replace {
		if _, err := tx.Exec(ctx, `TRUNCATE ic3_asset_master CASCADE`); err != nil {
			return res, fmt.Errorf("truncate: %w", err)
		}
	}

	const chunkSize = 500
	for start2 := 0; start2 < len(rows); start2 += chunkSize {
		end := start2 + chunkSize
		if end > len(rows) {
			end = len(rows)
		}
		chunk := rows[start2:end]

		batch := &pgx.Batch{}
		for _, r := range chunk {
			status := "Inactive"
			if r.Status == 1 {
				status = "Active"
			}
			batch.Queue(`
				INSERT INTO ic3_asset_master (
					asset_id, asset_name, asset_type, asset_category, asset_sub_category,
					criticality, manufacturer, model_no, serial_no,
					asset_status, asset_live_status,
					customer_name, location_name,
					schedule_count_configured, schedule_count_initiated, schedule_assigned,
					schedule_configured, schedule_initiate, ticketcount,
					purchase_number, purchase_date, purchase_value,
					manufacturer_id, reason_for_inactive,
					report_seq_no, data_source,
					created_at, updated_at
				) VALUES (
					$1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23,$24,$25,$26,NOW(),NOW()
				)
				ON CONFLICT (asset_id) DO UPDATE SET
					asset_name                = EXCLUDED.asset_name,
					asset_category            = EXCLUDED.asset_category,
					asset_sub_category        = EXCLUDED.asset_sub_category,
					criticality               = EXCLUDED.criticality,
					manufacturer              = EXCLUDED.manufacturer,
					model_no                  = EXCLUDED.model_no,
					serial_no                 = EXCLUDED.serial_no,
					asset_status              = EXCLUDED.asset_status,
					customer_name             = EXCLUDED.customer_name,
					location_name             = EXCLUDED.location_name,
					schedule_count_configured = EXCLUDED.schedule_count_configured,
					schedule_count_initiated  = EXCLUDED.schedule_count_initiated,
					schedule_assigned         = EXCLUDED.schedule_assigned,
					purchase_number           = EXCLUDED.purchase_number,
					purchase_date             = EXCLUDED.purchase_date,
					purchase_value            = EXCLUDED.purchase_value,
					reason_for_inactive       = EXCLUDED.reason_for_inactive,
					sync_version              = ic3_asset_master.sync_version + 1,
					last_synced_at            = NOW(),
					updated_at                = NOW()`,
				r.AssetID,
				nullStr(r.AssetName),
				nullStr(r.SubCategory),
				nullStr(r.Category),
				nullStr(r.SubCategory),
				nullStr(r.AssetCriticality),
				nullStr(r.Manufacturer),
				nullStr(r.AssetModel),
				nullStr(r.AssetID), // serial_no = asset_id initially
				status,
				1,
				nullStr(r.CustomerName),
				nullStr(r.Location),
				r.ScheduleConfigured,
				r.ScheduleInitiated,
				nullStr(r.ScheduleAssigned),
				boolToInt(r.ScheduleConfigured > 0),
				boolToInt(r.ScheduleInitiated > 0),
				0,
				nullStr(r.PurchaseNumber),
				r.PurchaseDate,
				r.PurchaseValue,
				nil,
				nullStr(r.ReasonForInactive),
				r.SeqNo,
				"IMPORT",
			)
		}

		br := tx.SendBatch(ctx, batch)
		for range chunk {
			tag, err := br.Exec()
			if err != nil {
				res.Skipped++
				continue
			}
			if tag.RowsAffected() == 1 {
				res.Inserted++
			} else {
				res.Updated++
			}
		}
		if err := br.Close(); err != nil {
			return res, fmt.Errorf("batch close: %w", err)
		}
	}

	tx.Exec(ctx, `
		INSERT INTO cmms_sync_log (sync_type, status, assets_synced, duration_ms)
		VALUES ('bulk_import','success',$1,$2)`,
		res.Inserted+res.Updated,
		int(time.Since(start).Milliseconds()),
	)

	if err := tx.Commit(ctx); err != nil {
		return res, fmt.Errorf("commit: %w", err)
	}
	res.Duration = strconv.FormatInt(time.Since(start).Milliseconds(), 10)
	return res, nil
}

// ============================================================================
// DB — queries on ic3_asset_master
// ============================================================================

func (db *DB) ListAssets(ctx context.Context, customer, category, status string, page, pageSize int) ([]AssetRecord, int, error) {
	if pageSize <= 0 || pageSize > 500 {
		pageSize = 100
	}
	if page < 1 {
		page = 1
	}
	offset := (page - 1) * pageSize

	where := []string{"1=1"}
	args := []any{}
	n := 1

	if customer != "" {
		where = append(where, fmt.Sprintf("customer_name ILIKE $%d", n))
		args = append(args, "%"+customer+"%")
		n++
	}
	if category != "" {
		where = append(where, fmt.Sprintf("(asset_category ILIKE $%d OR asset_sub_category ILIKE $%d)", n, n))
		args = append(args, "%"+category+"%")
		n++
	}
	switch status {
	case "active":
		where = append(where, "asset_status = 'Active'")
	case "inactive":
		where = append(where, "asset_status = 'Inactive'")
	}

	cond := strings.Join(where, " AND ")

	var total int
	db.pool.QueryRow(ctx, `SELECT COUNT(*) FROM ic3_asset_master WHERE `+cond, args...).Scan(&total)

	args = append(args, pageSize, offset)
	q := fmt.Sprintf(`
		SELECT asset_id, asset_name, asset_type, asset_category, asset_sub_category,
		       criticality, traceid, customer_name, location_name, building_name,
		       site_id, department, manufacturer, model_no, serial_no, capacity_rating,
		       asset_status, asset_live_status, iot_device_mapped,
		       schedule_count_configured, schedule_count_initiated, ticketcount,
		       purchase_date, purchase_value, contract_end_date,
		       latitude, longitude, install_date, last_synced_at, created_at
		FROM ic3_asset_master
		WHERE %s
		ORDER BY report_seq_no ASC NULLS LAST, asset_id ASC
		LIMIT $%d OFFSET $%d`, cond, n, n+1)

	rows, err := db.pool.Query(ctx, q, args...)
	if err != nil {
		return nil, 0, err
	}
	defer rows.Close()

	var out []AssetRecord
	for rows.Next() {
		var a AssetRecord
		if err := rows.Scan(
			&a.AssetID, &a.AssetName, &a.AssetType, &a.AssetCategory, &a.AssetSubCategory,
			&a.Criticality, &a.Traceid, &a.CustomerName, &a.LocationName, &a.BuildingName,
			&a.SiteID, &a.Department, &a.Manufacturer, &a.ModelNo, &a.SerialNo, &a.CapacityRating,
			&a.AssetStatus, &a.AssetLiveStatus, &a.IotDeviceMapped,
			&a.ScheduleConfigured, &a.ScheduleInitiated, &a.Ticketcount,
			&a.PurchaseDate, &a.PurchaseValue, &a.ContractEndDate,
			&a.Latitude, &a.Longitude, &a.InstallDate, &a.LastSyncedAt, &a.CreatedAt,
		); err != nil {
			continue
		}
		out = append(out, a)
	}
	return out, total, nil
}

func (db *DB) GetAsset(ctx context.Context, assetID string) (*AssetRecord, error) {
	var a AssetRecord
	err := db.pool.QueryRow(ctx, `
		SELECT asset_id, asset_name, asset_type, asset_category, asset_sub_category,
		       criticality, traceid, customer_name, location_name, building_name,
		       site_id, department, manufacturer, model_no, serial_no, capacity_rating,
		       asset_status, asset_live_status, iot_device_mapped,
		       schedule_count_configured, schedule_count_initiated, ticketcount,
		       purchase_date, purchase_value, contract_end_date,
		       latitude, longitude, install_date, last_synced_at, created_at
		FROM ic3_asset_master WHERE asset_id = $1`, assetID,
	).Scan(
		&a.AssetID, &a.AssetName, &a.AssetType, &a.AssetCategory, &a.AssetSubCategory,
		&a.Criticality, &a.Traceid, &a.CustomerName, &a.LocationName, &a.BuildingName,
		&a.SiteID, &a.Department, &a.Manufacturer, &a.ModelNo, &a.SerialNo, &a.CapacityRating,
		&a.AssetStatus, &a.AssetLiveStatus, &a.IotDeviceMapped,
		&a.ScheduleConfigured, &a.ScheduleInitiated, &a.Ticketcount,
		&a.PurchaseDate, &a.PurchaseValue, &a.ContractEndDate,
		&a.Latitude, &a.Longitude, &a.InstallDate, &a.LastSyncedAt, &a.CreatedAt,
	)
	if err != nil {
		return nil, err
	}
	return &a, nil
}

// AssetHealthSummary is returned by GET /api/assets/health.
type AssetHealthSummary struct {
	TotalAssets     int              `json:"total_assets"`
	ActiveAssets    int              `json:"active_assets"`
	InactiveAssets  int              `json:"inactive_assets"`
	NoIotLink       int              `json:"no_iot_link"`
	NoGPS           int              `json:"no_gps"`
	PMOverdue       int              `json:"pm_overdue"`
	PMDueSoon       int              `json:"pm_due_soon_7d"`
	ContractExpired int              `json:"contract_expired"`
	ByCategory      []map[string]any `json:"by_category"`
	ByCustomer      []map[string]any `json:"by_customer"`
	DataQuality     map[string]any   `json:"data_quality"`
	LastImport      *time.Time       `json:"last_import"`
}

func (db *DB) GetAssetHealth(ctx context.Context) (AssetHealthSummary, error) {
	var h AssetHealthSummary

	db.pool.QueryRow(ctx, `
		SELECT COUNT(*),
		       SUM(CASE WHEN asset_status='Active'   THEN 1 ELSE 0 END),
		       SUM(CASE WHEN asset_status='Inactive' THEN 1 ELSE 0 END)
		FROM ic3_asset_master`).Scan(&h.TotalAssets, &h.ActiveAssets, &h.InactiveAssets)

	db.pool.QueryRow(ctx, `SELECT COUNT(*) FROM ic3_asset_master WHERE asset_status='Active' AND iot_device_mapped=0`).Scan(&h.NoIotLink)
	db.pool.QueryRow(ctx, `SELECT COUNT(*) FROM ic3_asset_master WHERE asset_status='Active' AND latitude IS NULL`).Scan(&h.NoGPS)

	db.pool.QueryRow(ctx, `
		SELECT COUNT(*) FROM cmms_pm_schedule s
		JOIN ic3_asset_master m ON m.asset_id = s.asset_id
		WHERE m.asset_status='Active' AND s.is_overdue=TRUE`).Scan(&h.PMOverdue)

	db.pool.QueryRow(ctx, `
		SELECT COUNT(*) FROM cmms_pm_schedule s
		JOIN ic3_asset_master m ON m.asset_id = s.asset_id
		WHERE m.asset_status='Active' AND s.next_schedule_date BETWEEN NOW() AND NOW()+INTERVAL '7 days'`).Scan(&h.PMDueSoon)

	db.pool.QueryRow(ctx, `
		SELECT COUNT(*) FROM ic3_asset_master
		WHERE asset_status='Active' AND contract_end_date IS NOT NULL AND contract_end_date < NOW()`).Scan(&h.ContractExpired)

	catRows, _ := db.pool.Query(ctx, `
		SELECT COALESCE(asset_category,'Unknown'), COALESCE(asset_sub_category,''),
		       COUNT(*), SUM(CASE WHEN asset_status='Active' THEN 1 ELSE 0 END)
		FROM ic3_asset_master
		GROUP BY asset_category, asset_sub_category
		ORDER BY COUNT(*) DESC LIMIT 20`)
	if catRows != nil {
		for catRows.Next() {
			var cat, sub string
			var tot, active int
			catRows.Scan(&cat, &sub, &tot, &active)
			h.ByCategory = append(h.ByCategory, map[string]any{
				"category": cat, "sub_category": sub, "total": tot, "active": active,
			})
		}
		catRows.Close()
	}

	custRows, _ := db.pool.Query(ctx, `
		SELECT COALESCE(customer_name,'Unknown'), COUNT(*),
		       SUM(CASE WHEN asset_status='Active' THEN 1 ELSE 0 END)
		FROM ic3_asset_master
		GROUP BY customer_name ORDER BY COUNT(*) DESC LIMIT 20`)
	if custRows != nil {
		for custRows.Next() {
			var cust string
			var tot, active int
			custRows.Scan(&cust, &tot, &active)
			h.ByCustomer = append(h.ByCustomer, map[string]any{
				"customer": cust, "total": tot, "active": active,
			})
		}
		custRows.Close()
	}

	var avgQ, p1, p2 float64
	db.pool.QueryRow(ctx, `
		SELECT COALESCE(AVG(data_completeness_pct),0),
		       COALESCE(SUM(gap_no_iot_link+gap_no_install_date+gap_no_gps+gap_no_contract),0),
		       COALESCE(SUM(gap_no_purchase_date+gap_no_purchase_value+gap_no_lifespan+gap_no_depreciation+gap_no_vendor),0)
		FROM v_cmms_data_quality`).Scan(&avgQ, &p1, &p2)
	h.DataQuality = map[string]any{
		"avg_completeness_pct": math.Round(avgQ*10) / 10,
		"total_p1_gaps":        int(p1),
		"total_p2_gaps":        int(p2),
	}

	var lastImport time.Time
	if err := db.pool.QueryRow(ctx, `
		SELECT synced_at FROM cmms_sync_log WHERE sync_type='bulk_import' AND status='success'
		ORDER BY synced_at DESC LIMIT 1`).Scan(&lastImport); err == nil {
		h.LastImport = &lastImport
	}

	return h, nil
}

func (db *DB) GetPMOverdue(ctx context.Context) ([]map[string]any, error) {
	rows, err := db.pool.Query(ctx, `
		SELECT asset_id, traceid, customer_name, location_name, building_name,
		       criticality, in_charge_person, maintenance_name, next_schedule_date,
		       schedule_reference_id, overdue_days, pm_urgency
		FROM v_cmms_pm_overdue LIMIT 500`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var out []map[string]any
	for rows.Next() {
		var (
			aid, cust, loc, bldg, crit, inCharge, pmName, ref, urgency *string
			traceid, overdueDays                                         *int
			nextDate                                                      *time.Time
		)
		if err := rows.Scan(&aid, &traceid, &cust, &loc, &bldg, &crit, &inCharge,
			&pmName, &nextDate, &ref, &overdueDays, &urgency); err != nil {
			continue
		}
		out = append(out, map[string]any{
			"asset_id": aid, "traceid": traceid, "customer_name": cust,
			"location_name": loc, "building_name": bldg, "criticality": crit,
			"in_charge_person": inCharge, "maintenance_name": pmName,
			"next_schedule_date": nextDate, "schedule_reference": ref,
			"overdue_days": overdueDays, "pm_urgency": urgency,
		})
	}
	if out == nil {
		out = []map[string]any{}
	}
	return out, nil
}

// ============================================================================
// DB — Create / Update / Delete on ic3_asset_master
// ============================================================================

// AssetUpsertInput is the shape accepted by Create and Update.
type AssetUpsertInput struct {
	AssetID          string   `json:"asset_id"`
	AssetName        string   `json:"asset_name"`
	AssetType        string   `json:"asset_type"`
	AssetCategory    string   `json:"asset_category"`
	AssetSubCategory string   `json:"asset_sub_category"`
	Criticality      string   `json:"criticality"`
	CustomerName     string   `json:"customer_name"`
	LocationName     string   `json:"location_name"`
	BuildingName     string   `json:"building_name"`
	SiteID           string   `json:"site_id"`
	Department       string   `json:"department"`
	InChargePerson   string   `json:"in_charge_person"`
	Manufacturer     string   `json:"manufacturer"`
	ModelNo          string   `json:"model_no"`
	SerialNo         string   `json:"serial_no"`
	CapacityRating   string   `json:"capacity_rating"`
	AssetStatus      string   `json:"asset_status"`
	Latitude         *float64 `json:"latitude"`
	Longitude        *float64 `json:"longitude"`
	InstallDate      string   `json:"install_date"`
	PurchaseValue    *float64 `json:"purchase_value"`
	ContractEndDate  string   `json:"contract_end_date"`
	DesignLifeYears  *int     `json:"design_life_years"`
}

func (db *DB) CreateAsset(ctx context.Context, in AssetUpsertInput) (*AssetRecord, error) {
	if in.AssetID == "" || in.AssetName == "" {
		return nil, fmt.Errorf("asset_id and asset_name are required")
	}
	if in.AssetStatus == "" {
		in.AssetStatus = "Active"
	}
	if in.Criticality == "" {
		in.Criticality = "Non-Critical"
	}
	_, err := db.pool.Exec(ctx, `
		INSERT INTO ic3_asset_master (
			asset_id, asset_name, asset_type, asset_category, asset_sub_category,
			criticality, customer_name, location_name, building_name, site_id,
			department, in_charge_person, manufacturer, model_no, serial_no,
			capacity_rating, asset_status, latitude, longitude,
			install_date, purchase_value, contract_end_date, design_life_years,
			data_source, created_at, updated_at
		) VALUES (
			$1,$2,$3,$4,$5,$6,$7,$8,$9,$10,
			$11,$12,$13,$14,$15,$16,$17,$18,$19,
			$20,$21,$22,$23,'MANUAL',NOW(),NOW()
		)`,
		in.AssetID, in.AssetName, nullStr(in.AssetType), nullStr(in.AssetCategory), nullStr(in.AssetSubCategory),
		in.Criticality, nullStr(in.CustomerName), nullStr(in.LocationName), nullStr(in.BuildingName), nullStr(in.SiteID),
		nullStr(in.Department), nullStr(in.InChargePerson), nullStr(in.Manufacturer), nullStr(in.ModelNo), nullStr(in.SerialNo),
		nullStr(in.CapacityRating), in.AssetStatus, in.Latitude, in.Longitude,
		nullStr(in.InstallDate), in.PurchaseValue, nullStr(in.ContractEndDate), in.DesignLifeYears,
	)
	if err != nil {
		return nil, err
	}
	return db.GetAsset(ctx, in.AssetID)
}

func (db *DB) UpdateAsset(ctx context.Context, assetID string, in AssetUpsertInput) (*AssetRecord, error) {
	if in.AssetName == "" {
		return nil, fmt.Errorf("asset_name is required")
	}
	if in.AssetStatus == "" {
		in.AssetStatus = "Active"
	}
	_, err := db.pool.Exec(ctx, `
		UPDATE ic3_asset_master SET
			asset_name        = $2,
			asset_type        = $3,
			asset_category    = $4,
			asset_sub_category= $5,
			criticality       = $6,
			customer_name     = $7,
			location_name     = $8,
			building_name     = $9,
			site_id           = $10,
			department        = $11,
			in_charge_person  = $12,
			manufacturer      = $13,
			model_no          = $14,
			serial_no         = $15,
			capacity_rating   = $16,
			asset_status      = $17,
			latitude          = $18,
			longitude         = $19,
			install_date      = $20,
			purchase_value    = $21,
			contract_end_date = $22,
			design_life_years = $23,
			updated_at        = NOW()
		WHERE asset_id = $1`,
		assetID, in.AssetName, nullStr(in.AssetType), nullStr(in.AssetCategory), nullStr(in.AssetSubCategory),
		in.Criticality, nullStr(in.CustomerName), nullStr(in.LocationName), nullStr(in.BuildingName), nullStr(in.SiteID),
		nullStr(in.Department), nullStr(in.InChargePerson), nullStr(in.Manufacturer), nullStr(in.ModelNo), nullStr(in.SerialNo),
		nullStr(in.CapacityRating), in.AssetStatus, in.Latitude, in.Longitude,
		nullStr(in.InstallDate), in.PurchaseValue, nullStr(in.ContractEndDate), in.DesignLifeYears,
	)
	if err != nil {
		return nil, err
	}
	return db.GetAsset(ctx, assetID)
}

func (db *DB) DeleteAsset(ctx context.Context, assetID string) error {
	tag, err := db.pool.Exec(ctx, `DELETE FROM ic3_asset_master WHERE asset_id = $1`, assetID)
	if err != nil {
		return err
	}
	if tag.RowsAffected() == 0 {
		return fmt.Errorf("not found")
	}
	return nil
}

func (db *DB) GetSyncLog(ctx context.Context, limit int) ([]map[string]any, error) {
	if limit <= 0 || limit > 200 {
		limit = 50
	}
	rows, err := db.pool.Query(ctx, `
		SELECT id, cmms_id, sync_type, status, assets_synced,
		       response_code, error_msg, duration_ms, synced_at
		FROM cmms_sync_log ORDER BY synced_at DESC LIMIT $1`, limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var out []map[string]any
	for rows.Next() {
		var id, assets int64
		var cmmsID *int64
		var syncType, st string
		var rc, dur *int
		var errMsg *string
		var syncedAt time.Time
		rows.Scan(&id, &cmmsID, &syncType, &st, &assets, &rc, &errMsg, &dur, &syncedAt)
		out = append(out, map[string]any{
			"id": id, "cmms_id": cmmsID, "sync_type": syncType,
			"status": st, "assets_synced": assets,
			"response_code": rc, "error_msg": errMsg,
			"duration_ms": dur, "synced_at": syncedAt,
		})
	}
	if out == nil {
		out = []map[string]any{}
	}
	return out, nil
}

// ── helpers ──────────────────────────────────────────────────────────────────

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

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