import { SF_STALE_STATE, SFError } from "../errors.js";
import {
	_getAdapter,
	asStringOrNull,
	normalizeScheduleScope,
	scheduleEntryFromRow,
	transaction,
} from "./sf-db-core.js";

export function recordRepoProfile(profile) {
	const currentDb = _getAdapter();
	if (!currentDb) throw new SFError(SF_STALE_STATE, "sf-db: No database open");
	transaction(() => {
		currentDb
			.prepare(`INSERT OR REPLACE INTO repo_profiles (
           profile_id, project_hash, project_root, head, branch, remote_hash,
           dirty, profile_json, created_at
         ) VALUES (
           :profile_id, :project_hash, :project_root, :head, :branch, :remote_hash,
           :dirty, :profile_json, :created_at
         )`)
			.run({
				":profile_id": profile.profileId,
				":project_hash": profile.projectHash,
				":project_root": profile.projectRoot,
				":head": profile.git.head,
				":branch": profile.git.branch,
				":remote_hash": profile.git.remoteHash,
				":dirty": profile.git.dirty ? 1 : 0,
				":profile_json": JSON.stringify(profile),
				":created_at": profile.createdAt,
			});
		const stmt = currentDb.prepare(`INSERT INTO repo_file_observations (
         path, latest_profile_id, git_status, ownership, language, size_bytes,
         content_hash, summary, first_seen_at, last_seen_at, adopted_at,
         adoption_unit_id
       ) VALUES (
         :path, :latest_profile_id, :git_status, :ownership, :language, :size_bytes,
         :content_hash, :summary, :first_seen_at, :last_seen_at, :adopted_at,
         :adoption_unit_id
       )
       ON CONFLICT(path) DO UPDATE SET
         latest_profile_id = excluded.latest_profile_id,
         git_status = excluded.git_status,
         ownership = CASE
           WHEN repo_file_observations.ownership = 'sf_generated'
             THEN repo_file_observations.ownership
           WHEN repo_file_observations.ownership = 'candidate_harness'
             THEN repo_file_observations.ownership
           ELSE excluded.ownership
         END,
         language = excluded.language,
         size_bytes = excluded.size_bytes,
         content_hash = excluded.content_hash,
         summary = excluded.summary,
         first_seen_at = repo_file_observations.first_seen_at,
         last_seen_at = excluded.last_seen_at,
         adopted_at = COALESCE(repo_file_observations.adopted_at, excluded.adopted_at),
         adoption_unit_id = COALESCE(repo_file_observations.adoption_unit_id, excluded.adoption_unit_id)`);
		for (const file of profile.git.changedFiles) {
			stmt.run({
				":path": file.path,
				":latest_profile_id": profile.profileId,
				":git_status": file.gitStatus,
				":ownership": file.ownership,
				":language": file.language,
				":size_bytes": file.sizeBytes,
				":content_hash": file.contentHash,
				":summary": file.summary,
				":first_seen_at": file.firstSeenAt,
				":last_seen_at": file.lastSeenAt,
				":adopted_at": file.adoptedAt,
				":adoption_unit_id": file.adoptionUnitId,
			});
		}
	});
}

export function getLatestRepoProfile() {
	const currentDb = _getAdapter();
	if (!currentDb) throw new SFError(SF_STALE_STATE, "sf-db: No database open");
	const row = currentDb
		.prepare(`SELECT profile_id, project_hash, project_root, head, branch, remote_hash,
              dirty, profile_json, created_at
       FROM repo_profiles
       ORDER BY created_at DESC, profile_id DESC
       LIMIT 1`)
		.get();
	if (!row) return null;
	return {
		profileId: row["profile_id"],
		projectHash: row["project_hash"],
		projectRoot: row["project_root"],
		head: asStringOrNull(row["head"]),
		branch: asStringOrNull(row["branch"]),
		remoteHash: asStringOrNull(row["remote_hash"]),
		dirty: row["dirty"] === 1,
		profileJson: row["profile_json"] ?? "{}",
		createdAt: row["created_at"],
	};
}

export function getRepoFileObservations() {
	const currentDb = _getAdapter();
	if (!currentDb) throw new SFError(SF_STALE_STATE, "sf-db: No database open");
	return currentDb
		.prepare(`SELECT path, latest_profile_id, git_status, ownership, language,
              size_bytes, content_hash, summary, first_seen_at, last_seen_at,
              adopted_at, adoption_unit_id
       FROM repo_file_observations
       ORDER BY path ASC`)
		.all()
		.map((row) => ({
			path: row["path"],
			latestProfileId: row["latest_profile_id"],
			gitStatus: row["git_status"],
			ownership: row["ownership"],
			language: asStringOrNull(row["language"]),
			sizeBytes: row["size_bytes"] ?? 0,
			contentHash: asStringOrNull(row["content_hash"]),
			summary: asStringOrNull(row["summary"]),
			firstSeenAt: row["first_seen_at"],
			lastSeenAt: row["last_seen_at"],
			adoptedAt: asStringOrNull(row["adopted_at"]),
			adoptionUnitId: asStringOrNull(row["adoption_unit_id"]),
		}));
}

export function insertScheduleEntry(scope, entry, importedFrom = null) {
	const currentDb = _getAdapter();
	if (!currentDb) return;
	const normalizedScope = normalizeScheduleScope(scope);
	const schemaVersion = entry.schemaVersion ?? 1;
	const full = { schemaVersion, ...entry };
	currentDb
		.prepare(
			`INSERT INTO schedule_entries (
				scope, id, schema_version, kind, status, due_at, created_at,
				snoozed_at, payload_json, created_by, autonomous_dispatch, full_json,
				imported_from
			) VALUES (
				:scope, :id, :schema_version, :kind, :status, :due_at, :created_at,
				:snoozed_at, :payload_json, :created_by, :autonomous_dispatch, :full_json,
				:imported_from
			)`,
		)
		.run({
			":scope": normalizedScope,
			":id": entry.id,
			":schema_version": schemaVersion,
			":kind": entry.kind ?? "reminder",
			":status": entry.status ?? "pending",
			":due_at": entry.due_at ?? "",
			":created_at": entry.created_at ?? "",
			":snoozed_at": entry.snoozed_at ?? null,
			":payload_json": JSON.stringify(entry.payload ?? {}),
			":created_by": entry.created_by ?? "user",
			":autonomous_dispatch": entry.autonomous_dispatch ? 1 : 0,
			":full_json": JSON.stringify(full),
			":imported_from": importedFrom,
		});
}

export function getScheduleEntries(scope) {
	const currentDb = _getAdapter();
	if (!currentDb) return [];
	const normalizedScope = normalizeScheduleScope(scope);
	try {
		const rows = currentDb
			.prepare(
				`SELECT s.*
				   FROM schedule_entries s
				   JOIN (
				     SELECT id, MAX(seq) AS max_seq
				       FROM schedule_entries
				      WHERE scope = :scope
				      GROUP BY id
				   ) latest ON latest.id = s.id AND latest.max_seq = s.seq
				  WHERE s.scope = :scope
				  ORDER BY s.due_at ASC, s.created_at ASC, s.seq ASC`,
			)
			.all({ ":scope": normalizedScope });
		return rows.map(scheduleEntryFromRow).filter(Boolean);
	} catch {
		return [];
	}
}

export function countScheduleEntries(scope) {
	const currentDb = _getAdapter();
	if (!currentDb) return 0;
	const normalizedScope = normalizeScheduleScope(scope);
	try {
		const row = currentDb
			.prepare(
				"SELECT COUNT(*) AS cnt FROM schedule_entries WHERE scope = :scope",
			)
			.get({ ":scope": normalizedScope });
		return row?.cnt ?? 0;
	} catch {
		return 0;
	}
}

export function getRuntimeCounter(key) {
	const currentDb = _getAdapter();
	if (!currentDb) return 0;
	const row = currentDb
		.prepare("SELECT value FROM runtime_counters WHERE key = ?")
		.get(key);
	return typeof row?.value === "number" ? row.value : 0;
}

export function setRuntimeCounter(key, value) {
	const currentDb = _getAdapter();
	if (!currentDb) throw new SFError(SF_STALE_STATE, "sf-db: No database open");
	currentDb
		.prepare(
			`INSERT INTO runtime_counters (key, value, updated_at)
       VALUES (:key, :value, :updated_at)
       ON CONFLICT(key) DO UPDATE SET value = excluded.value, updated_at = excluded.updated_at`,
		)
		.run({
			":key": key,
			":value": value,
			":updated_at": new Date().toISOString(),
		});
}

export function incrementRuntimeCounter(key) {
	const currentDb = _getAdapter();
	if (!currentDb) throw new SFError(SF_STALE_STATE, "sf-db: No database open");
	currentDb
		.prepare(
			`INSERT INTO runtime_counters (key, value, updated_at)
       VALUES (:key, 1, :updated_at)
       ON CONFLICT(key) DO UPDATE SET value = value + 1, updated_at = excluded.updated_at`,
		)
		.run({ ":key": key, ":updated_at": new Date().toISOString() });
	const row = currentDb
		.prepare("SELECT value FROM runtime_counters WHERE key = ?")
		.get(key);
	return typeof row?.value === "number" ? row.value : 1;
}

/**
 * Increment a runtime counter by an arbitrary positive amount.
 *
 * Purpose: track cumulative runtime metrics such as prompt-section character
 * volume without requiring every caller to read-modify-write manually.
 *
 * Consumer: memory prompt telemetry for surfacing memory injection cost.
 */
export function incrementRuntimeCounterBy(key, amount) {
	const currentDb = _getAdapter();
	if (!currentDb) throw new SFError(SF_STALE_STATE, "sf-db: No database open");
	const delta = Number.isFinite(amount) && amount > 0 ? amount : 0;
	if (delta === 0) return getRuntimeCounter(key);
	currentDb
		.prepare(
			`INSERT INTO runtime_counters (key, value, updated_at)
       VALUES (:key, :value, :updated_at)
       ON CONFLICT(key) DO UPDATE SET value = value + excluded.value, updated_at = excluded.updated_at`,
		)
		.run({
			":key": key,
			":value": delta,
			":updated_at": new Date().toISOString(),
		});
	return getRuntimeCounter(key);
}

export function getProjectStartedAt(db) {
	const row = db
		.prepare(
			"SELECT value FROM project_metrics_meta WHERE key = 'projectStartedAt'",
		)
		.get();
	if (!row) return null;
	const ts = Number(row["value"]);
	return Number.isFinite(ts) ? ts : null;
}

export function setProjectStartedAt(db, ts) {
	db.prepare(
		`INSERT INTO project_metrics_meta (key, value) VALUES ('projectStartedAt', :value)
		ON CONFLICT(key) DO UPDATE SET value = excluded.value`,
	).run({ ":value": String(ts) });
}

export function openIntentChapter({
	id,
	unitType,
	unitId,
	milestoneId,
	sliceId,
	taskId,
	intent,
	metadata,
}) {
	const currentDb = _getAdapter();
	if (!currentDb) throw new SFError(SF_STALE_STATE, "sf-db: No database open");
	const now = new Date().toISOString();
	currentDb
		.prepare(
			`INSERT INTO intent_chapters
				(id, unit_type, unit_id, milestone_id, slice_id, task_id, intent, opened_at, metadata_json)
			VALUES
				(:id, :unitType, :unitId, :milestoneId, :sliceId, :taskId, :intent, :openedAt, :metadataJson)
			ON CONFLICT(id) DO NOTHING`,
		)
		.run({
			":id": id,
			":unitType": unitType,
			":unitId": unitId,
			":milestoneId": milestoneId ?? null,
			":sliceId": sliceId ?? null,
			":taskId": taskId ?? null,
			":intent": intent,
			":openedAt": now,
			":metadataJson": metadata ? JSON.stringify(metadata) : null,
		});
	return id;
}

export function closeIntentChapter(id, outcome = "done") {
	const currentDb = _getAdapter();
	if (!currentDb) throw new SFError(SF_STALE_STATE, "sf-db: No database open");
	const res = currentDb
		.prepare(
			`UPDATE intent_chapters
			SET closed_at = :closedAt, outcome = :outcome
			WHERE id = :id AND closed_at IS NULL`,
		)
		.run({
			":id": id,
			":closedAt": new Date().toISOString(),
			":outcome": outcome,
		});
	return (res?.changes ?? 0) > 0;
}

export function getOpenIntentChapters({ limit = 5 } = {}) {
	const currentDb = _getAdapter();
	if (!currentDb) return [];
	return currentDb
		.prepare(
			`SELECT id, unit_type as unitType, unit_id as unitId,
					milestone_id as milestoneId, slice_id as sliceId, task_id as taskId,
					intent, opened_at as openedAt, metadata_json as metadataJson
			FROM intent_chapters
			WHERE closed_at IS NULL
			ORDER BY opened_at DESC
			LIMIT :limit`,
		)
		.all({ ":limit": limit });
}

export function closeIntentChaptersForUnit(
	unitType,
	unitId,
	outcome = "cancelled",
) {
	const currentDb = _getAdapter();
	if (!currentDb) return 0;
	const res = currentDb
		.prepare(
			`UPDATE intent_chapters
			SET closed_at = :closedAt, outcome = :outcome
			WHERE unit_type = :unitType AND unit_id = :unitId AND closed_at IS NULL`,
		)
		.run({
			":closedAt": new Date().toISOString(),
			":outcome": outcome,
			":unitType": unitType,
			":unitId": unitId,
		});
	return res?.changes ?? 0;
}
