use std::fs;
use std::io::{BufReader, Read, Write};
use std::path::{Path, PathBuf};
use std::collections::HashSet;

use zip::read::ZipArchive;
use zip::write::SimpleFileOptions;
use zip::ZipWriter;

use argon2::{Algorithm, Argon2, Params, Version};
use sha2::{Digest, Sha256};
use uuid::Uuid;
use base64::{engine::general_purpose::STANDARD as BASE64, Engine as _};
use p256::ecdsa::{SigningKey, VerifyingKey};
use p256::elliptic_curve::rand_core::OsRng;
use p256::pkcs8::{DecodePrivateKey, EncodePrivateKey, EncodePublicKey, LineEnding};
use rand::RngCore;
use rcgen::{
    BasicConstraints, CertificateParams, DnType, ExtendedKeyUsagePurpose, IsCa, Issuer, KeyPair,
    KeyUsagePurpose, PKCS_ECDSA_P256_SHA256,
};
use rustls_pemfile::certs;
use rusqlite::{params, Connection};
use serde::{Deserialize, Serialize};
use zeroize::Zeroizing;

use crate::fingerprint;

const VAULT_DB_FILE: &str = "vault.db";
const VAULT_META_FILE: &str = "vault-meta.json";
const SALT_LEN: usize = 16;
const KEY_LEN: usize = 32;

#[derive(Debug, Serialize, Clone)]
pub struct DraftRecord {
    pub id: i64,
    pub artifact_title: String,
    pub author: String,
    pub description: Option<String>,
    pub date_created: Option<String>,
    pub license: Option<String>,
    pub keywords: Option<String>,
    pub genre: Option<String>,
    pub material: Option<String>,
    pub contributor: Option<String>,
    pub abstract_: Option<String>,
    pub physical_hash: Option<String>,
    pub perceptual_hash: Option<String>,
    pub draft_status: String,
    pub is_certified: bool,
    pub c2pa_manifest_id: Option<String>,
    pub c2pa_claim_id: Option<String>,
    pub asset_path: Option<String>,
    pub ipfs_cid: Option<String>,
    pub created_at: String,
    pub updated_at: String,
}

#[derive(Debug, Clone)]
pub struct DraftForSigning {
    pub id: i64,
    pub artifact_title: String,
    pub author: String,
    pub description: Option<String>,
    pub date_created: Option<String>,
    pub license: Option<String>,
    pub keywords: Option<String>,
    pub genre: Option<String>,
    pub material: Option<String>,
    pub contributor: Option<String>,
    pub abstract_: Option<String>,
    pub physical_hash: Option<String>,
}

#[derive(Debug, Serialize, Clone)]
pub struct SimilarAssetMatch {
    pub draft_id: i64,
    pub artifact_title: String,
    pub author: String,
    pub perceptual_hash: String,
    pub hamming_distance: u32,
}

#[derive(Debug, Serialize, Clone)]
pub struct VaultCorrelation {
    pub status: String,
    pub draft_id: Option<i64>,
    pub artifact_title: Option<String>,
    pub author: Option<String>,
}

#[derive(Debug, Serialize, Clone)]
pub struct IdentityRecord {
    pub id: i64,
    pub key_label: String,
    pub is_active: bool,
    pub is_production: bool,
    pub common_name: Option<String>,
    pub organization: Option<String>,
    pub country: Option<String>,
    pub issuer: Option<String>,
    pub not_after: Option<String>,
    pub has_certificate_chain: bool,
    pub public_key_fingerprint: Option<String>,
}

#[derive(Debug, Serialize, Clone)]
pub struct WatermarkIdentityMatch {
    pub identity_id: i64,
    pub display_name: String,
}

#[derive(Debug, Clone)]
pub struct IdentityMaterial {
    pub private_key_pem: Vec<u8>,
    pub certificate_chain_pem: Vec<u8>,
    pub is_production: bool,
}

#[derive(Debug, Serialize, Clone)]
pub struct TrustedContact {
    pub id: String,
    pub common_name: String,
    pub organization: Option<String>,
    pub certificate_pem: String,
    pub added_at: String,
    pub notes: Option<String>,
}

#[derive(Debug, Serialize, Clone)]
pub struct TagRecord {
    pub id: i64,
    pub uid: String,
    pub identity_id: Option<i64>,
    pub artwork_id: Option<i64>,
    pub status: String,
    pub tag_family: Option<String>,
    pub chip_subtype: Option<String>,
    pub key_version: Option<i32>,
    pub provisioning_profile: Option<String>,
    pub anchor_mode: Option<String>,
    pub enrolled_at: Option<String>,
    pub last_seen_at: String,
    pub notes: Option<String>,
    pub created_at: String,
    pub updated_at: String,
    pub last_ndef_write_at: Option<String>,
}

#[derive(Debug, Deserialize, Serialize)]
struct VaultMeta {
    salt_b64: String,
}

pub fn init_vault(vault_dir: &Path, master_password: &str) -> Result<(), String> {
    ensure_password(master_password)?;
    fs::create_dir_all(vault_dir).map_err(|e| format!("create vault directory: {e}"))?;
    let salt = load_or_create_salt(vault_dir)?;
    let key = derive_key(master_password, &salt)?;
    let conn = open_encrypted_db(&db_path(vault_dir), &key)?;
    create_schema(&conn)?;
    Ok(())
}

pub fn save_draft(
    vault_dir: &Path,
    master_password: &str,
    artifact_title: &str,
    author: &str,
    description: Option<&str>,
    date_created: Option<&str>,
    license: Option<&str>,
    keywords: Option<&str>,
    genre: Option<&str>,
    material: Option<&str>,
    contributor: Option<&str>,
    abstract_: Option<&str>,
    physical_hash: Option<&str>,
    draft_status: &str,
    perceptual_hash: Option<&str>,
    asset_path: Option<&str>,
) -> Result<i64, String> {
    ensure_password(master_password)?;
    ensure_non_empty("artifact title", artifact_title)?;
    ensure_non_empty("author", author)?;
    ensure_non_empty("draft status", draft_status)?;

    let salt = load_existing_salt(vault_dir)?;
    let key = derive_key(master_password, &salt)?;
    let conn = open_encrypted_db(&db_path(vault_dir), &key)?;
    create_schema(&conn)?;

    let clean_hash = physical_hash
        .map(str::trim)
        .filter(|h| !h.is_empty())
        .map(str::to_owned);
    let clean_perceptual_hash = perceptual_hash
        .map(str::trim)
        .filter(|h| !h.is_empty())
        .map(str::to_owned);
    let clean_asset_path = asset_path
        .map(str::trim)
        .filter(|p| !p.is_empty())
        .map(str::to_owned);
    let clean_desc = description.map(str::trim).filter(|s| !s.is_empty()).map(str::to_owned);
    let clean_date = date_created.map(str::trim).filter(|s| !s.is_empty()).map(str::to_owned);
    let clean_license = license.map(str::trim).filter(|s| !s.is_empty()).map(str::to_owned);
    let clean_keywords = keywords.map(str::trim).filter(|s| !s.is_empty()).map(str::to_owned);
    let clean_genre = genre.map(str::trim).filter(|s| !s.is_empty()).map(str::to_owned);
    let clean_material = material.map(str::trim).filter(|s| !s.is_empty()).map(str::to_owned);
    let clean_contributor = contributor.map(str::trim).filter(|s| !s.is_empty()).map(str::to_owned);
    let clean_abstract = abstract_.map(str::trim).filter(|s| !s.is_empty()).map(str::to_owned);

    conn.execute(
        "INSERT INTO drafts (artifact_title, author, description, date_created, license, keywords, genre, material, contributor, abstract, physical_hash, perceptual_hash, draft_status, asset_path, is_certified, c2pa_manifest_id)
         VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12, ?13, ?14, 0, NULL)",
        params![
            artifact_title.trim(),
            author.trim(),
            clean_desc,
            clean_date,
            clean_license,
            clean_keywords,
            clean_genre,
            clean_material,
            clean_contributor,
            clean_abstract,
            clean_hash,
            clean_perceptual_hash,
            draft_status.trim(),
            clean_asset_path,
        ],
    )
    .map_err(|e| format!("insert draft: {e}"))?;
    Ok(conn.last_insert_rowid())
}

/// Update an existing unsealed draft's metadata fields.
pub fn update_draft(
    vault_dir: &Path,
    master_password: &str,
    draft_id: i64,
    artifact_title: &str,
    author: &str,
    description: Option<&str>,
    date_created: Option<&str>,
    license: Option<&str>,
    keywords: Option<&str>,
    genre: Option<&str>,
    material: Option<&str>,
    contributor: Option<&str>,
    abstract_: Option<&str>,
    physical_hash: Option<&str>,
    asset_path: Option<&str>,
) -> Result<(), String> {
    ensure_password(master_password)?;
    let salt = load_existing_salt(vault_dir)?;
    let key = derive_key(master_password, &salt)?;
    let conn = open_encrypted_db(&db_path(vault_dir), &key)?;
    create_schema(&conn)?;

    let clean_hash = physical_hash
        .map(str::trim)
        .filter(|h| !h.is_empty())
        .map(str::to_owned);
    let clean_asset_path = asset_path
        .map(str::trim)
        .filter(|p| !p.is_empty())
        .map(str::to_owned);
    let clean_desc = description.map(str::trim).filter(|s| !s.is_empty()).map(str::to_owned);
    let clean_date = date_created.map(str::trim).filter(|s| !s.is_empty()).map(str::to_owned);
    let clean_license = license.map(str::trim).filter(|s| !s.is_empty()).map(str::to_owned);
    let clean_keywords = keywords.map(str::trim).filter(|s| !s.is_empty()).map(str::to_owned);
    let clean_genre = genre.map(str::trim).filter(|s| !s.is_empty()).map(str::to_owned);
    let clean_material = material.map(str::trim).filter(|s| !s.is_empty()).map(str::to_owned);
    let clean_contributor = contributor.map(str::trim).filter(|s| !s.is_empty()).map(str::to_owned);
    let clean_abstract = abstract_.map(str::trim).filter(|s| !s.is_empty()).map(str::to_owned);

    let rows = conn
        .execute(
            "UPDATE drafts SET artifact_title = ?1, author = ?2, description = ?3, date_created = ?4, license = ?5, keywords = ?6, genre = ?7, material = ?8, contributor = ?9, abstract = ?10, physical_hash = ?11, asset_path = ?12, updated_at = datetime('now')
             WHERE id = ?13 AND is_certified = 0 AND deleted_at IS NULL",
            params![
                artifact_title.trim(),
                author.trim(),
                clean_desc,
                clean_date,
                clean_license,
                clean_keywords,
                clean_genre,
                clean_material,
                clean_contributor,
                clean_abstract,
                clean_hash,
                clean_asset_path,
                draft_id,
            ],
        )
        .map_err(|e| format!("update draft: {e}"))?;
    if rows == 0 {
        return Err("Draft not found or already sealed.".to_string());
    }
    Ok(())
}

pub fn list_drafts(vault_dir: &Path, master_password: &str) -> Result<Vec<DraftRecord>, String> {
    ensure_password(master_password)?;

    let salt = load_existing_salt(vault_dir)?;
    let key = derive_key(master_password, &salt)?;
    let conn = open_encrypted_db(&db_path(vault_dir), &key)?;
    create_schema(&conn)?;

    let mut stmt = conn
        .prepare(
            "SELECT id, artifact_title, author, description, date_created, license, physical_hash, perceptual_hash, draft_status, is_certified, c2pa_manifest_id, c2pa_claim_id, asset_path, ipfs_cid, created_at, updated_at, keywords, genre, material, contributor, abstract
             FROM drafts
             WHERE deleted_at IS NULL
             ORDER BY updated_at DESC, id DESC",
        )
        .map_err(|e| format!("prepare draft query: {e}"))?;

    let rows = stmt
        .query_map([], |row| {
            Ok(DraftRecord {
                id: row.get(0)?,
                artifact_title: row.get(1)?,
                author: row.get(2)?,
                description: row.get(3)?,
                date_created: row.get(4)?,
                license: row.get(5)?,
                physical_hash: row.get(6)?,
                perceptual_hash: row.get(7)?,
                draft_status: row.get(8)?,
                is_certified: row.get::<_, i64>(9)? != 0,
                c2pa_manifest_id: row.get(10)?,
                c2pa_claim_id: row.get(11)?,
                asset_path: row.get(12)?,
                ipfs_cid: row.get(13)?,
                created_at: row.get(14)?,
                updated_at: row.get(15)?,
                keywords: row.get(16)?,
                genre: row.get(17)?,
                material: row.get(18)?,
                contributor: row.get(19)?,
                abstract_: row.get(20)?,
            })
        })
        .map_err(|e| format!("query drafts: {e}"))?;

    let mut drafts = Vec::new();
    for row in rows {
        drafts.push(row.map_err(|e| format!("read draft row: {e}"))?);
    }
    Ok(drafts)
}

pub fn get_draft_by_id(
    vault_dir: &Path,
    master_password: &str,
    draft_id: i64,
) -> Result<Option<DraftRecord>, String> {
    ensure_password(master_password)?;
    let salt = load_existing_salt(vault_dir)?;
    let key = derive_key(master_password, &salt)?;
    let conn = open_encrypted_db(&db_path(vault_dir), &key)?;
    create_schema(&conn)?;
    let result = conn.query_row(
        "SELECT id, artifact_title, author, description, date_created, license, physical_hash, perceptual_hash, draft_status, is_certified, c2pa_manifest_id, c2pa_claim_id, asset_path, ipfs_cid, created_at, updated_at, keywords, genre, material, contributor, abstract
         FROM drafts WHERE id = ?1 AND deleted_at IS NULL",
        params![draft_id],
        |row| {
            Ok(DraftRecord {
                id: row.get(0)?,
                artifact_title: row.get(1)?,
                author: row.get(2)?,
                description: row.get(3)?,
                date_created: row.get(4)?,
                license: row.get(5)?,
                physical_hash: row.get(6)?,
                perceptual_hash: row.get(7)?,
                draft_status: row.get(8)?,
                is_certified: row.get::<_, i64>(9)? != 0,
                c2pa_manifest_id: row.get(10)?,
                c2pa_claim_id: row.get(11)?,
                asset_path: row.get(12)?,
                ipfs_cid: row.get(13)?,
                created_at: row.get(14)?,
                updated_at: row.get(15)?,
                keywords: row.get(16)?,
                genre: row.get(17)?,
                material: row.get(18)?,
                contributor: row.get(19)?,
                abstract_: row.get(20)?,
            })
        },
    );
    match result {
        Ok(draft) => Ok(Some(draft)),
        Err(rusqlite::Error::QueryReturnedNoRows) => Ok(None),
        Err(e) => Err(format!("get draft by id: {e}")),
    }
}

/// Soft-delete a draft. Sets deleted_at so the row persists for referential integrity
/// (tags, future watermarks). The draft no longer appears in lists.
pub fn delete_draft(vault_dir: &Path, master_password: &str, draft_id: i64) -> Result<(), String> {
    ensure_password(master_password)?;
    let salt = load_existing_salt(vault_dir)?;
    let key = derive_key(master_password, &salt)?;
    let conn = open_encrypted_db(&db_path(vault_dir), &key)?;
    create_schema(&conn)?;
    let changed = conn
        .execute(
            "UPDATE drafts SET deleted_at = datetime('now') WHERE id = ?1 AND deleted_at IS NULL",
            params![draft_id],
        )
        .map_err(|e| format!("delete draft: {e}"))?;
    if changed == 0 {
        return Err(format!("draft id {} not found or already deleted", draft_id));
    }
    Ok(())
}

/// Find a draft by C2PA manifest ID. Searches all drafts including soft-deleted.
/// Returns (draft_id, is_deleted) if found. Used for restore-on-re-add when adding a sealed file.
pub fn find_draft_by_manifest_id(
    vault_dir: &Path,
    master_password: &str,
    manifest_id: &str,
) -> Result<Option<(i64, bool)>, String> {
    ensure_password(master_password)?;
    let id = manifest_id.trim();
    if id.is_empty() {
        return Ok(None);
    }
    let salt = load_existing_salt(vault_dir)?;
    let key = derive_key(master_password, &salt)?;
    let conn = open_encrypted_db(&db_path(vault_dir), &key)?;
    create_schema(&conn)?;
    let result = conn.query_row(
        "SELECT id, (deleted_at IS NOT NULL)
         FROM drafts
         WHERE c2pa_manifest_id = ?1
         ORDER BY (deleted_at IS NULL) DESC, updated_at DESC, id DESC
         LIMIT 1",
        params![id],
        |row| Ok((row.get::<_, i64>(0)?, row.get::<_, i64>(1)? != 0)),
    );
    match result {
        Ok(t) => Ok(Some(t)),
        Err(rusqlite::Error::QueryReturnedNoRows) => Ok(None),
        Err(e) => Err(format!("find draft by manifest id: {e}")),
    }
}

/// Returns (draft_id, is_deleted) for a draft id, including soft-deleted rows.
pub fn find_draft_by_id_with_deleted_state(
    vault_dir: &Path,
    master_password: &str,
    draft_id: i64,
) -> Result<Option<(i64, bool)>, String> {
    ensure_password(master_password)?;
    let salt = load_existing_salt(vault_dir)?;
    let key = derive_key(master_password, &salt)?;
    let conn = open_encrypted_db(&db_path(vault_dir), &key)?;
    create_schema(&conn)?;
    let result = conn.query_row(
        "SELECT id, (deleted_at IS NOT NULL)
         FROM drafts
         WHERE id = ?1
         LIMIT 1",
        params![draft_id],
        |row| Ok((row.get::<_, i64>(0)?, row.get::<_, i64>(1)? != 0)),
    );
    match result {
        Ok(t) => Ok(Some(t)),
        Err(rusqlite::Error::QueryReturnedNoRows) => Ok(None),
        Err(e) => Err(format!("find draft by id: {e}")),
    }
}

/// Restore a soft-deleted draft and set its asset_path. Sets deleted_at = NULL.
pub fn restore_draft(
    vault_dir: &Path,
    master_password: &str,
    draft_id: i64,
    asset_path: &str,
) -> Result<(), String> {
    ensure_password(master_password)?;
    let path_trimmed = asset_path.trim();
    let salt = load_existing_salt(vault_dir)?;
    let key = derive_key(master_password, &salt)?;
    let conn = open_encrypted_db(&db_path(vault_dir), &key)?;
    create_schema(&conn)?;
    let changed = conn
        .execute(
            "UPDATE drafts SET deleted_at = NULL, asset_path = ?1, updated_at = datetime('now') WHERE id = ?2 AND deleted_at IS NOT NULL",
            params![path_trimmed, draft_id],
        )
        .map_err(|e| format!("restore draft: {e}"))?;
    if changed == 0 {
        return Err(format!("draft id {} not found or not deleted", draft_id));
    }
    Ok(())
}

/// Update a draft's asset_path by id. Used when relinking an already-active draft.
pub fn update_draft_asset_path_by_id(
    vault_dir: &Path,
    master_password: &str,
    draft_id: i64,
    asset_path: &str,
) -> Result<(), String> {
    ensure_password(master_password)?;
    let path_trimmed = asset_path.trim();
    if path_trimmed.is_empty() {
        return Ok(());
    }
    let salt = load_existing_salt(vault_dir)?;
    let key = derive_key(master_password, &salt)?;
    let conn = open_encrypted_db(&db_path(vault_dir), &key)?;
    create_schema(&conn)?;
    conn.execute(
        "UPDATE drafts SET asset_path = ?1, updated_at = datetime('now') WHERE id = ?2 AND deleted_at IS NULL",
        params![path_trimmed, draft_id],
    )
    .map_err(|e| format!("update draft asset path: {e}"))?;
    Ok(())
}

/// Find a certified draft whose asset_path matches the given path.
/// Returns the draft id if exactly one match, for use when updating asset_path after transfer.
pub fn find_draft_id_by_asset_path(
    vault_dir: &Path,
    master_password: &str,
    asset_path: &str,
) -> Result<Option<i64>, String> {
    ensure_password(master_password)?;
    let path_trimmed = asset_path.trim();
    if path_trimmed.is_empty() {
        return Ok(None);
    }
    let salt = load_existing_salt(vault_dir)?;
    let key = derive_key(master_password, &salt)?;
    let conn = open_encrypted_db(&db_path(vault_dir), &key)?;
    create_schema(&conn)?;
    let result = conn.query_row(
        "SELECT id FROM drafts WHERE asset_path = ?1 AND is_certified = 1 AND deleted_at IS NULL ORDER BY updated_at DESC LIMIT 1",
        params![path_trimmed],
        |row| row.get(0),
    );
    match result {
        Ok(id) => Ok(Some(id)),
        Err(rusqlite::Error::QueryReturnedNoRows) => Ok(None),
        Err(e) => Err(format!("find draft by asset path: {e}")),
    }
}

/// Update a specific draft's asset_path when the file was sealed/transferred to a new path.
/// Only updates the given draft_id, so other vault records pointing to the same file remain unchanged.
pub fn update_draft_asset_path_for_draft(
    vault_dir: &Path,
    master_password: &str,
    draft_id: i64,
    old_path: &str,
    new_path: &str,
) -> Result<(), String> {
    ensure_password(master_password)?;
    let salt = load_existing_salt(vault_dir)?;
    let key = derive_key(master_password, &salt)?;
    let conn = open_encrypted_db(&db_path(vault_dir), &key)?;
    create_schema(&conn)?;
    let old_trimmed = old_path.trim();
    let new_trimmed = new_path.trim();
    if old_trimmed.is_empty() || new_trimmed.is_empty() {
        return Ok(());
    }
    conn.execute(
        "UPDATE drafts SET asset_path = ?1 WHERE id = ?2 AND asset_path = ?3 AND deleted_at IS NULL",
        params![new_trimmed, draft_id, old_trimmed],
    )
    .map_err(|e| format!("update draft asset path: {e}"))?;
    Ok(())
}

pub fn delete_identity(vault_dir: &Path, master_password: &str, identity_id: i64) -> Result<(), String> {
    ensure_password(master_password)?;
    let salt = load_existing_salt(vault_dir)?;
    let key = derive_key(master_password, &salt)?;
    let conn = open_encrypted_db(&db_path(vault_dir), &key)?;
    create_schema(&conn)?;
    let changed = conn
        .execute("DELETE FROM secure_keys WHERE id = ?1", params![identity_id])
        .map_err(|e| format!("delete identity: {e}"))?;
    if changed == 0 {
        return Err(format!("identity id {} not found", identity_id));
    }
    Ok(())
}

pub fn verify_vault_password(vault_dir: &Path, master_password: &str) -> Result<(), String> {
    ensure_password(master_password)?;
    let salt = load_existing_salt(vault_dir)?;
    let key = derive_key(master_password, &salt)?;
    let _conn = open_encrypted_db(&db_path(vault_dir), &key)?;
    Ok(())
}

/// Change the vault master password. Rekeys the SQLCipher database with a key derived from
/// the new password (salt is unchanged). Caller must ensure the vault is initialized.
pub fn change_vault_password(
    vault_dir: &Path,
    current_password: &str,
    new_password: &str,
) -> Result<(), String> {
    ensure_password(current_password)?;
    ensure_password(new_password)?;
    if current_password.trim() == new_password.trim() {
        return Err("New password must differ from current password.".to_string());
    }

    let salt = load_existing_salt(vault_dir)?;
    let old_key = derive_key(current_password, &salt)?;
    let new_key = derive_key(new_password, &salt)?;

    let conn = open_encrypted_db(&db_path(vault_dir), &old_key)?;
    let new_key_hex = Zeroizing::new(hex::encode(&new_key[..]));
    let pragma_sql = Zeroizing::new(format!(
        "PRAGMA rekey = \"x'{}'\";",
        new_key_hex.as_str()
    ));
    conn.execute_batch(pragma_sql.as_str())
        .map_err(|e| format!("rekey vault database: {e}"))?;
    Ok(())
}

pub fn import_private_key_from_file(
    vault_dir: &Path,
    master_password: &str,
    key_file_path: &str,
) -> Result<i64, String> {
    ensure_password(master_password)?;
    ensure_non_empty("key file path", key_file_path)?;
    let salt = load_existing_salt(vault_dir)?;
    let key = derive_key(master_password, &salt)?;
    let conn = open_encrypted_db(&db_path(vault_dir), &key)?;
    create_schema(&conn)?;

    let raw = fs::read(key_file_path).map_err(|e| format!("read key file: {e}"))?;
    validate_private_key_pem(&raw)?;

    let spki_der = public_key_der_from_private_key_pem(&raw)?;
    let pub_fp = public_key_fingerprint_sha256(&spki_der);

    conn.execute("UPDATE secure_keys SET is_active = 0 WHERE is_active = 1", [])
        .map_err(|e| format!("deactivate existing keys: {e}"))?;
    conn.execute(
        "INSERT INTO secure_keys (
            key_label, key_pem, private_key, certificate_chain, is_production, is_active, public_key_fingerprint
         ) VALUES (?1, ?2, ?3, NULL, 0, 1, ?4)",
        params!["default", raw, raw, pub_fp],
    )
    .map_err(|e| format!("store key in vault: {e}"))?;
    Ok(conn.last_insert_rowid())
}

#[allow(dead_code)]
pub fn get_active_private_key_pem(
    vault_dir: &Path,
    master_password: &str,
) -> Result<Vec<u8>, String> {
    ensure_password(master_password)?;
    let salt = load_existing_salt(vault_dir)?;
    let key = derive_key(master_password, &salt)?;
    let conn = open_encrypted_db(&db_path(vault_dir), &key)?;
    create_schema(&conn)?;

    conn.query_row(
        "SELECT COALESCE(private_key, key_pem) FROM secure_keys WHERE is_active = 1 ORDER BY id DESC LIMIT 1",
        [],
        |row| row.get::<_, Vec<u8>>(0),
    )
    .map_err(|_| "no active signing key in vault; import a private key first".to_string())
}

pub fn get_draft_for_signing(
    vault_dir: &Path,
    master_password: &str,
    draft_id: i64,
) -> Result<DraftForSigning, String> {
    ensure_password(master_password)?;
    let salt = load_existing_salt(vault_dir)?;
    let key = derive_key(master_password, &salt)?;
    let conn = open_encrypted_db(&db_path(vault_dir), &key)?;
    create_schema(&conn)?;

    conn.query_row(
        "SELECT id, artifact_title, author, description, date_created, license, keywords, genre, material, contributor, abstract, physical_hash
         FROM drafts
         WHERE id = ?1 AND deleted_at IS NULL",
        params![draft_id],
        |row| {
            Ok(DraftForSigning {
                id: row.get(0)?,
                artifact_title: row.get(1)?,
                author: row.get(2)?,
                description: row.get(3)?,
                date_created: row.get(4)?,
                license: row.get(5)?,
                keywords: row.get(6)?,
                genre: row.get(7)?,
                material: row.get(8)?,
                contributor: row.get(9)?,
                abstract_: row.get(10)?,
                physical_hash: row.get(11)?,
            })
        },
    )
    .map_err(|_| format!("draft id {} not found", draft_id))
}

pub fn mark_draft_certified(
    vault_dir: &Path,
    master_password: &str,
    draft_id: i64,
    c2pa_manifest_id: Option<&str>,
    c2pa_claim_id: Option<&str>,
    ipfs_cid: Option<&str>,
) -> Result<(), String> {
    ensure_password(master_password)?;
    let salt = load_existing_salt(vault_dir)?;
    let key = derive_key(master_password, &salt)?;
    let conn = open_encrypted_db(&db_path(vault_dir), &key)?;
    create_schema(&conn)?;

    let manifest_id = c2pa_manifest_id.map(str::trim).filter(|s| !s.is_empty());
    let claim_id = c2pa_claim_id.map(str::trim).filter(|s| !s.is_empty());
    let cid = ipfs_cid.map(str::trim).filter(|s| !s.is_empty());
    let changed = conn
        .execute(
        "UPDATE drafts
         SET is_certified = 1,
             draft_status = 'Certified',
             c2pa_manifest_id = ?1,
             c2pa_claim_id = ?2,
             ipfs_cid = ?3
         WHERE id = ?4 AND deleted_at IS NULL",
        params![manifest_id, claim_id, cid, draft_id],
    )
    .map_err(|e| format!("mark draft certified: {e}"))?;
    if changed == 0 {
        return Err(format!("draft id {} not found or deleted", draft_id));
    }
    Ok(())
}

pub fn update_draft_physical_hash(
    vault_dir: &Path,
    master_password: &str,
    draft_id: i64,
    physical_hash: Option<&str>,
) -> Result<(), String> {
    ensure_password(master_password)?;
    let salt = load_existing_salt(vault_dir)?;
    let key = derive_key(master_password, &salt)?;
    let conn = open_encrypted_db(&db_path(vault_dir), &key)?;
    create_schema(&conn)?;
    let clean_hash = physical_hash
        .map(str::trim)
        .filter(|h| !h.is_empty())
        .map(str::to_owned);
    let changed = conn
        .execute(
            "UPDATE drafts SET physical_hash = ?1, updated_at = datetime('now') WHERE id = ?2 AND deleted_at IS NULL",
            params![clean_hash, draft_id],
        )
        .map_err(|e| format!("update draft physical_hash: {e}"))?;
    if changed == 0 {
        return Err(format!("draft id {} not found or deleted", draft_id));
    }
    Ok(())
}

pub fn update_draft_perceptual_hash(
    vault_dir: &Path,
    master_password: &str,
    draft_id: i64,
    perceptual_hash: &str,
) -> Result<(), String> {
    ensure_password(master_password)?;
    ensure_non_empty("perceptual hash", perceptual_hash)?;
    let salt = load_existing_salt(vault_dir)?;
    let key = derive_key(master_password, &salt)?;
    let conn = open_encrypted_db(&db_path(vault_dir), &key)?;
    create_schema(&conn)?;

    let changed = conn
        .execute(
            "UPDATE drafts SET perceptual_hash = ?1 WHERE id = ?2 AND deleted_at IS NULL",
            params![perceptual_hash.trim(), draft_id],
        )
        .map_err(|e| format!("update draft perceptual hash: {e}"))?;
    if changed == 0 {
        return Err(format!("draft id {} not found", draft_id));
    }
    Ok(())
}

pub fn find_similar_assets(
    vault_dir: &Path,
    master_password: &str,
    target_bytes: &[u8],
    threshold: u32,
) -> Result<Vec<SimilarAssetMatch>, String> {
    ensure_password(master_password)?;
    let target_hash = fingerprint::generate_phash_checked(target_bytes)?;
    let salt = load_existing_salt(vault_dir)?;
    let key = derive_key(master_password, &salt)?;
    let conn = open_encrypted_db(&db_path(vault_dir), &key)?;
    create_schema(&conn)?;

    let mut stmt = conn
        .prepare(
            "SELECT id, artifact_title, author, perceptual_hash
             FROM drafts
             WHERE perceptual_hash IS NOT NULL AND deleted_at IS NULL
             ORDER BY updated_at DESC, id DESC",
        )
        .map_err(|e| format!("prepare perceptual hash query: {e}"))?;
    let rows = stmt
        .query_map([], |row| {
            Ok((
                row.get::<_, i64>(0)?,
                row.get::<_, String>(1)?,
                row.get::<_, String>(2)?,
                row.get::<_, String>(3)?,
            ))
        })
        .map_err(|e| format!("query perceptual hashes: {e}"))?;

    let mut out = Vec::new();
    for row in rows {
        let (draft_id, artifact_title, author, candidate_hash) =
            row.map_err(|e| format!("read perceptual hash row: {e}"))?;
        let distance =
            fingerprint::calculate_distance_checked(&target_hash, candidate_hash.trim())?;
        if distance < threshold {
            out.push(SimilarAssetMatch {
                draft_id,
                artifact_title,
                author,
                perceptual_hash: candidate_hash,
                hamming_distance: distance,
            });
        }
    }
    out.sort_by_key(|m| m.hamming_distance);
    Ok(out)
}

/// Find a trusted contact whose certificate fingerprint matches the given identifier (SHA256 hex).
pub fn find_trusted_contact_by_fingerprint(
    vault_dir: &Path,
    master_password: &str,
    fingerprint: &str,
) -> Result<Option<TrustedContact>, String> {
    let fp = fingerprint.trim();
    if fp.is_empty() {
        return Ok(None);
    }
    let contacts = list_trusted_contacts(vault_dir, master_password)?;
    for contact in contacts {
        let contact_fp = certificate_fingerprint_sha256(&contact.certificate_pem)?;
        if contact_fp.eq_ignore_ascii_case(fp) {
            return Ok(Some(contact));
        }
    }
    Ok(None)
}

pub fn correlate_by_physical_hash(
    vault_dir: &Path,
    master_password: &str,
    physical_hash: Option<&str>,
) -> Result<VaultCorrelation, String> {
    ensure_password(master_password)?;
    let hash = physical_hash
        .map(str::trim)
        .filter(|h| !h.is_empty())
        .ok_or_else(|| "no physical hash found in image manifest".to_string())?;

    let salt = load_existing_salt(vault_dir)?;
    let key = derive_key(master_password, &salt)?;
    let conn = open_encrypted_db(&db_path(vault_dir), &key)?;
    create_schema(&conn)?;

    let mut stmt = conn
        .prepare(
            "SELECT id, artifact_title, author
             FROM drafts
             WHERE physical_hash = ?1 AND deleted_at IS NULL
             ORDER BY updated_at DESC, id DESC
             LIMIT 1",
        )
        .map_err(|e| format!("prepare correlation query: {e}"))?;
    let mut rows = stmt
        .query(params![hash])
        .map_err(|e| format!("run correlation query: {e}"))?;
    if let Some(row) = rows.next().map_err(|e| format!("read correlation row: {e}"))? {
        return Ok(VaultCorrelation {
            status: "Match".to_string(),
            draft_id: Some(row.get(0).map_err(|e| format!("read draft id: {e}"))?),
            artifact_title: Some(row.get(1).map_err(|e| format!("read title: {e}"))?),
            author: Some(row.get(2).map_err(|e| format!("read author: {e}"))?),
        });
    }

    Ok(VaultCorrelation {
        status: "Orphan".to_string(),
        draft_id: None,
        artifact_title: None,
        author: None,
    })
}

pub fn generate_sovereign_identity(
    vault_dir: &Path,
    master_password: &str,
    name: &str,
    organization: &str,
    country: &str,
) -> Result<i64, String> {
    ensure_password(master_password)?;
    ensure_non_empty("name", name)?;
    ensure_non_empty("organization", organization)?;
    ensure_country(country)?;

    let ca_signing_key = SigningKey::random(&mut OsRng);
    let ca_pem = ca_signing_key
        .to_pkcs8_pem(LineEnding::LF)
        .map_err(|e| format!("encode CA key as PKCS#8: {e}"))?
        .to_string();
    let ca_key_pair =
        KeyPair::from_pkcs8_pem_and_sign_algo(&ca_pem, &PKCS_ECDSA_P256_SHA256)
            .map_err(|e| format!("load CA key into certificate engine: {e}"))?;

    let mut ca_params = CertificateParams::new(Vec::<String>::new())
        .map_err(|e| format!("build CA certificate params: {e}"))?;
    ca_params
        .distinguished_name
        .push(DnType::CommonName, format!("{} Local CA", organization.trim()));
    ca_params
        .distinguished_name
        .push(DnType::OrganizationName, organization.trim().to_string());
    ca_params
        .distinguished_name
        .push(DnType::CountryName, country.trim().to_string());
    ca_params.is_ca = IsCa::Ca(BasicConstraints::Unconstrained);
    ca_params.key_usages = vec![KeyUsagePurpose::KeyCertSign];
    let ca_cert = ca_params
        .self_signed(&ca_key_pair)
        .map_err(|e| format!("generate CA certificate: {e}"))?;

    let leaf_signing_key = SigningKey::random(&mut OsRng);
    let leaf_pem = leaf_signing_key
        .to_pkcs8_pem(LineEnding::LF)
        .map_err(|e| format!("encode signing key as PKCS#8: {e}"))?
        .to_string();
    let leaf_key_pair =
        KeyPair::from_pkcs8_pem_and_sign_algo(&leaf_pem, &PKCS_ECDSA_P256_SHA256)
            .map_err(|e| format!("load signing key into certificate engine: {e}"))?;

    let mut leaf_params = CertificateParams::new(Vec::<String>::new())
        .map_err(|e| format!("build signing certificate params: {e}"))?;
    leaf_params
        .distinguished_name
        .push(DnType::CommonName, name.trim().to_string());
    leaf_params
        .distinguished_name
        .push(DnType::OrganizationName, organization.trim().to_string());
    leaf_params
        .distinguished_name
        .push(DnType::CountryName, country.trim().to_string());
    leaf_params.use_authority_key_identifier_extension = true;
    leaf_params.key_usages = vec![
        KeyUsagePurpose::DigitalSignature,
        KeyUsagePurpose::ContentCommitment,
    ];
    leaf_params.extended_key_usages = vec![ExtendedKeyUsagePurpose::EmailProtection];
    let ca_issuer = Issuer::from_params(&ca_params, &ca_key_pair);
    let leaf_cert = leaf_params
        .signed_by(&leaf_key_pair, &ca_issuer)
        .map_err(|e| format!("sign leaf certificate with CA: {e}"))?;

    let cert_chain_pem = format!("{}\n{}", leaf_cert.pem(), ca_cert.pem());
    let (cn, _org, issuer, not_after, spki_der) = parse_leaf_certificate_metadata(&cert_chain_pem)?;
    let pub_fp = public_key_fingerprint_sha256(&spki_der);

    let salt = load_existing_salt(vault_dir)?;
    let key = derive_key(master_password, &salt)?;
    let conn = open_encrypted_db(&db_path(vault_dir), &key)?;
    create_schema(&conn)?;

    conn.execute("UPDATE secure_keys SET is_active = 0 WHERE is_active = 1", [])
        .map_err(|e| format!("deactivate existing keys: {e}"))?;
    let private_key_bytes = leaf_pem.as_bytes().to_vec();
    conn.execute(
        "INSERT INTO secure_keys (
            key_label, key_pem, private_key, certificate_chain, is_production, is_active,
            common_name, organization, country, issuer, not_after, public_key_fingerprint
        ) VALUES (?1, ?2, ?3, ?4, 0, 1, ?5, ?6, ?7, ?8, ?9, ?10)",
        params![
            format!("identity-{}", chrono_like_timestamp()),
            private_key_bytes,
            private_key_bytes,
            cert_chain_pem,
            cn,
            organization.trim(),
            country.trim(),
            issuer,
            not_after,
            pub_fp
        ],
    )
    .map_err(|e| format!("store generated identity: {e}"))?;

    Ok(conn.last_insert_rowid())
}

pub fn list_identities(vault_dir: &Path, master_password: &str) -> Result<Vec<IdentityRecord>, String> {
    ensure_password(master_password)?;
    let salt = load_existing_salt(vault_dir)?;
    let key = derive_key(master_password, &salt)?;
    let conn = open_encrypted_db(&db_path(vault_dir), &key)?;
    create_schema(&conn)?;

    let mut stmt = conn
        .prepare(
            "SELECT id, key_label, is_active, is_production, common_name, organization, country, issuer, not_after, certificate_chain, public_key_fingerprint
             FROM secure_keys
             ORDER BY is_active DESC, id DESC",
        )
        .map_err(|e| format!("prepare identities query: {e}"))?;
    let rows = stmt
        .query_map([], |row| {
            let chain: Option<String> = row.get(9)?;
            Ok(IdentityRecord {
                id: row.get(0)?,
                key_label: row.get(1)?,
                is_active: row.get::<_, i64>(2)? != 0,
                is_production: row.get::<_, i64>(3)? != 0,
                common_name: row.get(4)?,
                organization: row.get(5)?,
                country: row.get(6)?,
                issuer: row.get(7)?,
                not_after: row.get(8)?,
                has_certificate_chain: chain.map(|s| !s.trim().is_empty()).unwrap_or(false),
                public_key_fingerprint: row.get(10)?,
            })
        })
        .map_err(|e| format!("query identities: {e}"))?;
    let mut out = Vec::new();
    for row in rows {
        out.push(row.map_err(|e| format!("read identity row: {e}"))?);
    }
    Ok(out)
}

/// Match a watermark compressed P-256 public key to a vault identity.
pub fn find_identity_by_watermark_pubkey(
    vault_dir: &Path,
    master_password: &str,
    compressed_pubkey: &[u8; 33],
) -> Result<Option<WatermarkIdentityMatch>, String> {
    ensure_password(master_password)?;
    let salt = load_existing_salt(vault_dir)?;
    let key = derive_key(master_password, &salt)?;
    let conn = open_encrypted_db(&db_path(vault_dir), &key)?;
    create_schema(&conn)?;

    let mut stmt = conn
        .prepare(
            "SELECT id, COALESCE(private_key, key_pem), COALESCE(common_name, ''), COALESCE(key_label, '')
             FROM secure_keys
             ORDER BY is_active DESC, id DESC",
        )
        .map_err(|e| format!("prepare watermark identity query: {e}"))?;
    let rows = stmt
        .query_map([], |row| {
            Ok((
                row.get::<_, i64>(0)?,
                row.get::<_, Vec<u8>>(1)?,
                row.get::<_, String>(2)?,
                row.get::<_, String>(3)?,
            ))
        })
        .map_err(|e| format!("query watermark identities: {e}"))?;

    for row in rows {
        let (identity_id, private_key_pem, common_name, key_label) =
            row.map_err(|e| format!("read watermark identity row: {e}"))?;
        let pem = match std::str::from_utf8(&private_key_pem) {
            Ok(v) => v,
            Err(_) => continue,
        };
        let signing_key = match SigningKey::from_pkcs8_pem(pem) {
            Ok(v) => v,
            Err(_) => continue,
        };
        let verifying_key = VerifyingKey::from(&signing_key);
        let encoded = verifying_key.to_encoded_point(true);
        if encoded.as_bytes() == compressed_pubkey {
            let display_name = if !common_name.trim().is_empty() {
                common_name
            } else if !key_label.trim().is_empty() {
                key_label
            } else {
                format!("Identity #{identity_id}")
            };
            return Ok(Some(WatermarkIdentityMatch {
                identity_id,
                display_name,
            }));
        }
    }

    Ok(None)
}

pub fn set_active_identity(
    vault_dir: &Path,
    master_password: &str,
    identity_id: i64,
) -> Result<(), String> {
    ensure_password(master_password)?;
    let salt = load_existing_salt(vault_dir)?;
    let key = derive_key(master_password, &salt)?;
    let conn = open_encrypted_db(&db_path(vault_dir), &key)?;
    create_schema(&conn)?;

    conn.execute("UPDATE secure_keys SET is_active = 0", [])
        .map_err(|e| format!("deactivate identities: {e}"))?;
    let affected = conn
        .execute(
            "UPDATE secure_keys SET is_active = 1 WHERE id = ?1",
            params![identity_id],
        )
        .map_err(|e| format!("activate identity: {e}"))?;
    if affected == 0 {
        return Err(format!("identity id {} not found", identity_id));
    }
    Ok(())
}

pub fn generate_csr_to_file(
    vault_dir: &Path,
    master_password: &str,
    identity_id: i64,
    name: &str,
    organization: &str,
    country: &str,
    output_path: &str,
) -> Result<(), String> {
    ensure_password(master_password)?;
    ensure_non_empty("name", name)?;
    ensure_non_empty("organization", organization)?;
    ensure_country(country)?;
    ensure_non_empty("output path", output_path)?;

    let identity = get_identity_by_id(vault_dir, master_password, identity_id)?;
    let private_key_str = std::str::from_utf8(&identity.private_key_pem)
        .map_err(|_| "private key in vault is not valid UTF-8 PEM".to_string())?;
    let key_pair =
        KeyPair::from_pkcs8_pem_and_sign_algo(private_key_str, &PKCS_ECDSA_P256_SHA256)
            .map_err(|e| format!("load private key for CSR: {e}"))?;
    let mut params = CertificateParams::new(Vec::<String>::new())
        .map_err(|e| format!("build csr params: {e}"))?;
    params
        .distinguished_name
        .push(DnType::CommonName, name.trim().to_string());
    params
        .distinguished_name
        .push(DnType::OrganizationName, organization.trim().to_string());
    params
        .distinguished_name
        .push(DnType::CountryName, country.trim().to_string());
    let csr = params
        .serialize_request(&key_pair)
        .map_err(|e| format!("generate csr: {e}"))?;
    let csr_pem = csr.pem().map_err(|e| format!("encode csr pem: {e}"))?;
    fs::write(output_path, csr_pem).map_err(|e| format!("write csr file: {e}"))?;
    Ok(())
}

/// Import a CA-signed certificate chain as a new production identity.
/// The leaf cert in the imported chain must match the private key of the source identity.
/// Creates a separate identity row so the user can toggle between self-signed and production.
pub fn import_ca_certificate(
    vault_dir: &Path,
    master_password: &str,
    identity_id: i64,
    cert_chain_path: &str,
) -> Result<(), String> {
    ensure_password(master_password)?;
    ensure_non_empty("certificate chain path", cert_chain_path)?;
    let chain_bytes = fs::read(cert_chain_path).map_err(|e| format!("read certificate chain file: {e}"))?;
    let chain_text = std::str::from_utf8(&chain_bytes)
        .map_err(|_| "certificate chain must be PEM text".to_string())?
        .to_string();
    let (cn, _org, issuer, not_after, cert_spki_der) = parse_leaf_certificate_metadata(&chain_text)?;
    let identity = get_identity_by_id(vault_dir, master_password, identity_id)?;
    let key_spki_der = public_key_der_from_private_key_pem(&identity.private_key_pem)?;
    if cert_spki_der != key_spki_der {
        return Err("Certificate does not match the Private Key in this Vault slot.".to_string());
    }
    let pub_fp = public_key_fingerprint_sha256(&key_spki_der);

    let salt = load_existing_salt(vault_dir)?;
    let key = derive_key(master_password, &salt)?;
    let conn = open_encrypted_db(&db_path(vault_dir), &key)?;
    create_schema(&conn)?;

    let source_meta: (Option<String>, Option<String>, Option<String>) = conn
        .query_row(
            "SELECT common_name, organization, country FROM secure_keys WHERE id = ?1",
            params![identity_id],
            |row| Ok((row.get(0)?, row.get(1)?, row.get(2)?)),
        )
        .map_err(|e| format!("read source identity metadata: {e}"))?;

    conn.execute("UPDATE secure_keys SET is_active = 0 WHERE is_active = 1", [])
        .map_err(|e| format!("deactivate existing keys: {e}"))?;

    conn.execute(
        "INSERT INTO secure_keys (
            key_label, key_pem, private_key, certificate_chain, is_production, is_active,
            common_name, organization, country, issuer, not_after, public_key_fingerprint
        ) VALUES (?1, ?2, ?3, ?4, 1, 1, ?5, ?6, ?7, ?8, ?9, ?10)",
        params![
            format!("production-{}", chrono_like_timestamp()),
            &identity.private_key_pem,
            &identity.private_key_pem,
            chain_text,
            cn.or(source_meta.0),
            source_meta.1,
            source_meta.2,
            issuer,
            not_after,
            pub_fp
        ],
    )
    .map_err(|e| format!("store production identity: {e}"))?;
    Ok(())
}

pub fn export_private_key_backup(
    vault_dir: &Path,
    master_password: &str,
    identity_id: i64,
    output_path: &str,
) -> Result<(), String> {
    ensure_password(master_password)?;
    ensure_non_empty("output path", output_path)?;
    let identity = get_identity_by_id(vault_dir, master_password, identity_id)?;
    fs::write(output_path, &identity.private_key_pem)
        .map_err(|e| format!("write backup key file: {e}"))?;
    Ok(())
}

/// Import a contact from a certificate PEM string (e.g. extracted from signed message).
pub fn import_contact_certificate_from_pem(
    vault_dir: &Path,
    master_password: &str,
    certificate_pem: &str,
) -> Result<String, String> {
    ensure_password(master_password)?;
    ensure_non_empty("certificate PEM", certificate_pem)?;
    let (cn, org, _, _, _) = parse_leaf_certificate_metadata(certificate_pem)?;
    let common_name = cn.unwrap_or_else(|| "Unknown".to_string());
    let organization = org;
    let fingerprint = certificate_fingerprint_sha256(certificate_pem)?;
    let id = Uuid::new_v4().to_string();
    let salt = load_existing_salt(vault_dir)?;
    let key = derive_key(master_password, &salt)?;
    let conn = open_encrypted_db(&db_path(vault_dir), &key)?;
    create_schema(&conn)?;
    conn.execute(
        "INSERT INTO trusted_contacts (id, common_name, organization, certificate_pem, certificate_fingerprint, added_at)
         VALUES (?1, ?2, ?3, ?4, ?5, datetime('now'))",
        params![id, common_name, organization, certificate_pem, fingerprint],
    )
    .map_err(|e| format!("store trusted contact: {e}"))?;
    Ok(id)
}

/// Import the active identity's leaf (public) certificate to a .crt file.
pub fn import_contact_certificate(
    vault_dir: &Path,
    master_password: &str,
    file_path: &str,
) -> Result<String, String> {
    ensure_password(master_password)?;
    ensure_non_empty("certificate file path", file_path)?;
    let raw = fs::read(file_path).map_err(|e| format!("read certificate file: {e}"))?;
    let (leaf_der, cn, org) = match std::str::from_utf8(&raw) {
        Ok(chain_str) => {
            let mut reader = BufReader::new(chain_str.as_bytes());
            let certs: Vec<_> = certs(&mut reader)
                .collect::<Result<Vec<_>, _>>()
                .map_err(|e| format!("parse certificate PEM: {e}"))?;
            let leaf = certs
                .first()
                .ok_or_else(|| "certificate file is empty".to_string())?;
            let (cn, org, _, _, _) = parse_leaf_certificate_metadata(chain_str)?;
            (leaf.to_vec(), cn, org)
        }
        Err(_) => {
            let (cn, org, _, _, _) = parse_leaf_certificate_metadata_from_der(raw.as_ref())?;
            (raw, cn, org)
        }
    };
    let common_name = cn.unwrap_or_else(|| "Unknown".to_string());
    let organization = org;
    let pem = format!(
        "-----BEGIN CERTIFICATE-----\n{}\n-----END CERTIFICATE-----\n",
        chunked_base64(&leaf_der)
    );
    let fingerprint = certificate_fingerprint_sha256(&pem)?;
    let id = Uuid::new_v4().to_string();
    let salt = load_existing_salt(vault_dir)?;
    let key = derive_key(master_password, &salt)?;
    let conn = open_encrypted_db(&db_path(vault_dir), &key)?;
    create_schema(&conn)?;
    conn.execute(
        "INSERT INTO trusted_contacts (id, common_name, organization, certificate_pem, certificate_fingerprint, added_at)
         VALUES (?1, ?2, ?3, ?4, ?5, datetime('now'))",
        params![id, common_name, organization, pem, fingerprint],
    )
    .map_err(|e| format!("store trusted contact: {e}"))?;
    Ok(id)
}

pub fn list_trusted_contacts(
    vault_dir: &Path,
    master_password: &str,
) -> Result<Vec<TrustedContact>, String> {
    ensure_password(master_password)?;
    let salt = load_existing_salt(vault_dir)?;
    let key = derive_key(master_password, &salt)?;
    let conn = open_encrypted_db(&db_path(vault_dir), &key)?;
    create_schema(&conn)?;
    let mut stmt = conn
        .prepare(
            "SELECT id, common_name, organization, certificate_pem, added_at, notes
             FROM trusted_contacts ORDER BY added_at DESC",
        )
        .map_err(|e| format!("prepare trusted contacts query: {e}"))?;
    let rows = stmt
        .query_map([], |row| {
            Ok(TrustedContact {
                id: row.get(0)?,
                common_name: row.get(1)?,
                organization: row.get(2)?,
                certificate_pem: row.get(3)?,
                added_at: row.get(4)?,
                notes: row.get(5)?,
            })
        })
        .map_err(|e| format!("query trusted contacts: {e}"))?;
    let mut out = Vec::new();
    for row in rows {
        out.push(row.map_err(|e| format!("read trusted contact row: {e}"))?);
    }
    Ok(out)
}

pub fn delete_trusted_contact(
    vault_dir: &Path,
    master_password: &str,
    contact_id: &str,
) -> Result<(), String> {
    ensure_password(master_password)?;
    ensure_non_empty("contact id", contact_id)?;
    let salt = load_existing_salt(vault_dir)?;
    let key = derive_key(master_password, &salt)?;
    let conn = open_encrypted_db(&db_path(vault_dir), &key)?;
    create_schema(&conn)?;
    let changed = conn
        .execute("DELETE FROM trusted_contacts WHERE id = ?1", params![contact_id])
        .map_err(|e| format!("delete trusted contact: {e}"))?;
    if changed == 0 {
        return Err(format!("contact id {} not found", contact_id));
    }
    Ok(())
}

pub fn get_trusted_contact_by_id(
    vault_dir: &Path,
    master_password: &str,
    contact_id: &str,
) -> Result<TrustedContact, String> {
    ensure_password(master_password)?;
    ensure_non_empty("contact id", contact_id)?;
    let salt = load_existing_salt(vault_dir)?;
    let key = derive_key(master_password, &salt)?;
    let conn = open_encrypted_db(&db_path(vault_dir), &key)?;
    create_schema(&conn)?;
    conn.query_row(
        "SELECT id, common_name, organization, certificate_pem, added_at, notes
         FROM trusted_contacts WHERE id = ?1",
        params![contact_id],
        |row| {
            Ok(TrustedContact {
                id: row.get(0)?,
                common_name: row.get(1)?,
                organization: row.get(2)?,
                certificate_pem: row.get(3)?,
                added_at: row.get(4)?,
                notes: row.get(5)?,
            })
        },
    )
    .map_err(|_| format!("contact id {} not found", contact_id))
}

/// Update a contact's notes. Notes are free-form text for user annotations.
pub fn update_contact_notes(
    vault_dir: &Path,
    master_password: &str,
    contact_id: &str,
    notes: Option<&str>,
) -> Result<TrustedContact, String> {
    ensure_password(master_password)?;
    ensure_non_empty("contact id", contact_id)?;
    let salt = load_existing_salt(vault_dir)?;
    let key = derive_key(master_password, &salt)?;
    let conn = open_encrypted_db(&db_path(vault_dir), &key)?;
    create_schema(&conn)?;
    let notes_value = notes.map(str::trim).filter(|s| !s.is_empty());
    conn.execute(
        "UPDATE trusted_contacts SET notes = ?1 WHERE id = ?2",
        params![notes_value, contact_id],
    )
    .map_err(|e| format!("update contact notes: {e}"))?;
    if conn.changes() == 0 {
        return Err(format!("contact id {} not found", contact_id));
    }
    get_trusted_contact_by_id(vault_dir, master_password, contact_id)
}

// ---------------------------------------------------------------------------
// Tag persistence (Phase 1: Tag Management)
// ---------------------------------------------------------------------------

/// Returns the vault secret used for tag key derivation (HKDF IKM).
/// Same derivation as DB decryption key. Caller must use only for derive_tag_keys.
pub fn get_vault_secret_for_tag_derivation(
    vault_dir: &Path,
    master_password: &str,
) -> Result<Zeroizing<Vec<u8>>, String> {
    ensure_password(master_password)?;
    let salt = load_existing_salt(vault_dir)?;
    let key = derive_key(master_password, &salt)?;
    Ok(Zeroizing::new(key.as_ref().to_vec()))
}

/// Map preflight classification string to tags.status enum.
fn classification_to_status(classification: &str) -> &'static str {
    match classification {
        "factory_default_likely" | "plain_ndef" => "plain_ndef",
        "sdm_enabled" => "personalized_sdm",
        "not_ntag424_dna" | "uid_read_failed" => "discovered",
        _ => "discovered",
    }
}

/// Tags enrolled by the active identity with no linked artwork. Used for Seal tag dropdown.
pub fn list_tags_available_for_seal(
    vault_dir: &Path,
    master_password: &str,
    identity_id: i64,
) -> Result<Vec<TagRecord>, String> {
    ensure_password(master_password)?;
    let salt = load_existing_salt(vault_dir)?;
    let key = derive_key(master_password, &salt)?;
    let conn = open_encrypted_db(&db_path(vault_dir), &key)?;
    create_schema(&conn)?;
    let mut stmt = conn
        .prepare(
            "SELECT id, uid, identity_id, artwork_id, status, tag_family, chip_subtype,
                    key_version, provisioning_profile, anchor_mode, enrolled_at, last_seen_at,
                    notes, created_at, updated_at, last_ndef_write_at
             FROM tags
             WHERE identity_id = ?1 AND (artwork_id IS NULL OR artwork_id = 0)
                   AND status = 'personalized_sdm' AND deleted_at IS NULL
             ORDER BY last_seen_at DESC",
        )
        .map_err(|e| format!("prepare list_tags_available_for_seal: {e}"))?;
    let rows = stmt
        .query_map(params![identity_id], |row| {
            Ok(TagRecord {
                id: row.get(0)?,
                uid: row.get(1)?,
                identity_id: row.get(2)?,
                artwork_id: row.get(3)?,
                status: row.get(4)?,
                tag_family: row.get(5)?,
                chip_subtype: row.get(6)?,
                key_version: row.get(7)?,
                provisioning_profile: row.get(8)?,
                anchor_mode: row.get(9)?,
                enrolled_at: row.get(10)?,
                last_seen_at: row.get(11)?,
                notes: row.get(12)?,
                created_at: row.get(13)?,
                updated_at: row.get(14)?,
                last_ndef_write_at: row.get(15)?,
            })
        })
        .map_err(|e| format!("query tags available for seal: {e}"))?;
    let mut out = Vec::new();
    for row in rows {
        out.push(row.map_err(|e| format!("read tag row: {e}"))?);
    }
    Ok(out)
}

/// Sealed artworks not yet linked to any tag. Used for the "Link Artwork" dropdown on the Tag page.
pub fn list_sealed_artworks_for_linking(
    vault_dir: &Path,
    master_password: &str,
    _identity_id: i64,
) -> Result<Vec<DraftRecord>, String> {
    ensure_password(master_password)?;
    let salt = load_existing_salt(vault_dir)?;
    let key = derive_key(master_password, &salt)?;
    let conn = open_encrypted_db(&db_path(vault_dir), &key)?;
    create_schema(&conn)?;

    let mut stmt = conn
        .prepare(
            "SELECT id, artifact_title, author, description, date_created, license, physical_hash, perceptual_hash, draft_status, is_certified, c2pa_manifest_id, c2pa_claim_id, asset_path, ipfs_cid, created_at, updated_at, keywords, genre, material, contributor, abstract
             FROM drafts
             WHERE c2pa_manifest_id IS NOT NULL AND deleted_at IS NULL
               AND id NOT IN (SELECT artwork_id FROM tags WHERE artwork_id IS NOT NULL AND deleted_at IS NULL)
             ORDER BY updated_at DESC, id DESC",
        )
        .map_err(|e| format!("prepare list_sealed_artworks_for_linking: {e}"))?;

    let rows = stmt
        .query_map([], |row| {
            Ok(DraftRecord {
                id: row.get(0)?,
                artifact_title: row.get(1)?,
                author: row.get(2)?,
                description: row.get(3)?,
                date_created: row.get(4)?,
                license: row.get(5)?,
                physical_hash: row.get(6)?,
                perceptual_hash: row.get(7)?,
                draft_status: row.get(8)?,
                is_certified: row.get::<_, i64>(9)? != 0,
                c2pa_manifest_id: row.get(10)?,
                c2pa_claim_id: row.get(11)?,
                asset_path: row.get(12)?,
                ipfs_cid: row.get(13)?,
                created_at: row.get(14)?,
                updated_at: row.get(15)?,
                keywords: row.get(16)?,
                genre: row.get(17)?,
                material: row.get(18)?,
                contributor: row.get(19)?,
                abstract_: row.get(20)?,
            })
        })
        .map_err(|e| format!("query sealed artworks for linking: {e}"))?;
    let mut out = Vec::new();
    for row in rows {
        out.push(row.map_err(|e| format!("read draft row: {e}"))?);
    }
    Ok(out)
}

/// Remove artwork linkage from a tag (rollback helper).
pub fn unlink_tag_artwork(
    vault_dir: &Path,
    master_password: &str,
    tag_id: i64,
) -> Result<(), String> {
    ensure_password(master_password)?;
    let salt = load_existing_salt(vault_dir)?;
    let key = derive_key(master_password, &salt)?;
    let conn = open_encrypted_db(&db_path(vault_dir), &key)?;
    create_schema(&conn)?;
    conn.execute(
        "UPDATE tags SET artwork_id = NULL, updated_at = datetime('now') WHERE id = ?1 AND deleted_at IS NULL",
        params![tag_id],
    )
    .map_err(|e| format!("unlink tag artwork: {e}"))?;
    Ok(())
}

/// Link a tag to an artwork (draft) after sealing.
pub fn link_tag_to_artwork(
    vault_dir: &Path,
    master_password: &str,
    tag_id: i64,
    artwork_id: i64,
    identity_id: i64,
) -> Result<(), String> {
    ensure_password(master_password)?;
    let salt = load_existing_salt(vault_dir)?;
    let key = derive_key(master_password, &salt)?;
    let conn = open_encrypted_db(&db_path(vault_dir), &key)?;
    create_schema(&conn)?;

    let tag_identity_id: Option<i64> = conn
        .query_row(
            "SELECT identity_id FROM tags WHERE id = ?1 AND deleted_at IS NULL",
            params![tag_id],
            |row| row.get(0),
        )
        .map_err(|e| format!("fetch tag: {e}"))?;

    match tag_identity_id {
        Some(tid) if tid == identity_id => {}
        Some(_) => return Err("tag is not controlled by the active identity".to_string()),
        None => return Err("tag not found".to_string()),
    }

    // Only active drafts can be linked; soft-deleted drafts must remain hidden/inert.
    let artwork_exists: i64 = conn
        .query_row(
            "SELECT COUNT(*) FROM drafts WHERE id = ?1 AND deleted_at IS NULL",
            params![artwork_id],
            |row| row.get(0),
        )
        .map_err(|e| format!("fetch artwork: {e}"))?;
    if artwork_exists == 0 {
        return Err("artwork not found".to_string());
    }

    let already_linked: i64 = conn
        .query_row(
            "SELECT COUNT(*) FROM tags WHERE artwork_id = ?1 AND id != ?2 AND deleted_at IS NULL",
            params![artwork_id, tag_id],
            |row| row.get(0),
        )
        .unwrap_or(0);
    if already_linked > 0 {
        return Err("this artwork is already linked to another tag".to_string());
    }

    conn.execute(
        "UPDATE tags SET artwork_id = ?1, updated_at = datetime('now') WHERE id = ?2 AND deleted_at IS NULL",
        params![artwork_id, tag_id],
    )
    .map_err(|e| format!("link tag to artwork: {e}"))?;
    Ok(())
}

pub fn list_tags(
    vault_dir: &Path,
    master_password: &str,
) -> Result<Vec<TagRecord>, String> {
    ensure_password(master_password)?;
    let salt = load_existing_salt(vault_dir)?;
    let key = derive_key(master_password, &salt)?;
    let conn = open_encrypted_db(&db_path(vault_dir), &key)?;
    create_schema(&conn)?;
    let mut stmt = conn
        .prepare(
            "SELECT id, uid, identity_id, artwork_id, status, tag_family, chip_subtype,
                    key_version, provisioning_profile, anchor_mode, enrolled_at, last_seen_at,
                    notes, created_at, updated_at, last_ndef_write_at
             FROM tags WHERE deleted_at IS NULL ORDER BY last_seen_at DESC",
        )
        .map_err(|e| format!("prepare list tags query: {e}"))?;
    let rows = stmt
        .query_map([], |row| {
            Ok(TagRecord {
                id: row.get(0)?,
                uid: row.get(1)?,
                identity_id: row.get(2)?,
                artwork_id: row.get(3)?,
                status: row.get(4)?,
                tag_family: row.get(5)?,
                chip_subtype: row.get(6)?,
                key_version: row.get(7)?,
                provisioning_profile: row.get(8)?,
                anchor_mode: row.get(9)?,
                enrolled_at: row.get(10)?,
                last_seen_at: row.get(11)?,
                notes: row.get(12)?,
                created_at: row.get(13)?,
                updated_at: row.get(14)?,
                last_ndef_write_at: row.get(15)?,
            })
        })
        .map_err(|e| format!("query tags: {e}"))?;
    let mut out = Vec::new();
    for row in rows {
        out.push(row.map_err(|e| format!("read tag row: {e}"))?);
    }
    Ok(out)
}

/// Get a tag by its database id.
pub fn get_tag_by_id(
    vault_dir: &Path,
    master_password: &str,
    tag_id: i64,
) -> Result<TagRecord, String> {
    ensure_password(master_password)?;
    let salt = load_existing_salt(vault_dir)?;
    let key = derive_key(master_password, &salt)?;
    let conn = open_encrypted_db(&db_path(vault_dir), &key)?;
    create_schema(&conn)?;
    fetch_tag_by_id(&conn, tag_id)
        .map_err(|e| format!("tag not found: {e}"))
}

pub fn get_tag_by_uid(
    vault_dir: &Path,
    master_password: &str,
    uid: &str,
) -> Result<Option<TagRecord>, String> {
    ensure_password(master_password)?;
    ensure_non_empty("tag uid", uid)?;
    let salt = load_existing_salt(vault_dir)?;
    let key = derive_key(master_password, &salt)?;
    let conn = open_encrypted_db(&db_path(vault_dir), &key)?;
    create_schema(&conn)?;
    let result = conn.query_row(
        "SELECT id, uid, identity_id, artwork_id, status, tag_family, chip_subtype,
                key_version, provisioning_profile, anchor_mode, enrolled_at, last_seen_at,
                notes, created_at, updated_at, last_ndef_write_at
         FROM tags WHERE uid = ?1 AND deleted_at IS NULL",
        params![uid],
        |row| {
            Ok(TagRecord {
                id: row.get(0)?,
                uid: row.get(1)?,
                identity_id: row.get(2)?,
                artwork_id: row.get(3)?,
                status: row.get(4)?,
                tag_family: row.get(5)?,
                chip_subtype: row.get(6)?,
                key_version: row.get(7)?,
                provisioning_profile: row.get(8)?,
                anchor_mode: row.get(9)?,
                enrolled_at: row.get(10)?,
                last_seen_at: row.get(11)?,
                notes: row.get(12)?,
                created_at: row.get(13)?,
                updated_at: row.get(14)?,
                last_ndef_write_at: row.get(15)?,
            })
        },
    );
    match result {
        Ok(tag) => Ok(Some(tag)),
        Err(rusqlite::Error::QueryReturnedNoRows) => Ok(None),
        Err(e) => Err(format!("get tag by uid: {e}")),
    }
}

fn normalize_uid(uid: &str) -> String {
    uid.trim().to_uppercase()
}

/// Get the last verification URL for a tag from enrolled or rewritten events.
/// Rewritten events take precedence (most recent URL wins).
pub fn get_tag_last_verification_url(
    vault_dir: &Path,
    master_password: &str,
    tag_id: i64,
) -> Result<Option<String>, String> {
    ensure_password(master_password)?;
    let salt = load_existing_salt(vault_dir)?;
    let key = derive_key(master_password, &salt)?;
    let conn = open_encrypted_db(&db_path(vault_dir), &key)?;
    create_schema(&conn)?;
    let event_data: Option<String> = conn
        .query_row(
            "SELECT event_data FROM tag_events WHERE tag_id = ?1 AND event_type IN ('enrolled', 'rewritten') ORDER BY created_at DESC LIMIT 1",
            params![tag_id],
            |row| row.get(0),
        )
        .ok();
    let url = event_data
        .as_deref()
        .and_then(|s| {
            serde_json::from_str::<serde_json::Value>(s).ok()
                .and_then(|v| v.get("verification_url").and_then(|u| u.as_str().map(String::from)))
        });
    Ok(url)
}

/// Upsert a tag from preflight result. Creates or updates the tag record and appends a discovered event.
pub fn upsert_tag_from_preflight(
    vault_dir: &Path,
    master_password: &str,
    uid: &str,
    classification: &str,
    reader_name: Option<&str>,
    tag_family: Option<&str>,
    chip_subtype: Option<&str>,
) -> Result<TagRecord, String> {
    ensure_password(master_password)?;
    let uid = normalize_uid(uid);
    ensure_non_empty("tag uid", &uid)?;
    let salt = load_existing_salt(vault_dir)?;
    let key = derive_key(master_password, &salt)?;
    let conn = open_encrypted_db(&db_path(vault_dir), &key)?;
    create_schema(&conn)?;

    let status = classification_to_status(classification);
    let now = chrono::Utc::now().to_rfc3339();
    let event_data = format!(
        r#"{{"classification":"{}","reader":"{}"}}"#,
        classification,
        reader_name.unwrap_or("")
    );

    let existing_id: Option<i64> = match conn.query_row(
        "SELECT id FROM tags WHERE UPPER(uid) = UPPER(?1) ORDER BY (deleted_at IS NULL) DESC, identity_id DESC, updated_at DESC LIMIT 1",
        params![&uid],
        |row| row.get(0),
    ) {
        Ok(id) => Some(id),
        Err(rusqlite::Error::QueryReturnedNoRows) => None,
        Err(e) => return Err(format!("check existing tag: {e}")),
    };

    if let Some(tag_id) = existing_id {
        // Preserve "released" status on rescan so we can still take control. Restore if soft-deleted.
        let status_to_set = conn
            .query_row("SELECT status FROM tags WHERE id = ?1", params![tag_id], |row| row.get::<_, String>(0))
            .ok()
            .filter(|s: &String| s == "released")
            .map(|_| "released".to_string())
            .unwrap_or_else(|| status.to_string());
        conn.execute(
            "UPDATE tags SET uid = ?1, status = ?2, tag_family = ?3, chip_subtype = ?4, last_seen_at = ?5, updated_at = ?5, deleted_at = NULL WHERE id = ?6",
            params![&uid, status_to_set, tag_family, chip_subtype, now, tag_id],
        )
        .map_err(|e| format!("update tag: {e}"))?;
        append_tag_event_inner(&conn, tag_id, "discovered", Some(&event_data))?;
    } else {
        conn.execute(
            "INSERT INTO tags (uid, status, tag_family, chip_subtype, last_seen_at, created_at, updated_at)
             VALUES (?1, ?2, ?3, ?4, ?5, ?5, ?5)",
            params![&uid, status, tag_family, chip_subtype, now],
        )
        .map_err(|e| format!("insert tag: {e}"))?;
        let tag_id = conn.last_insert_rowid();
        append_tag_event_inner(&conn, tag_id, "discovered", Some(&event_data))?;
    }

    fetch_tag_by_uid(&conn, &uid)
        .map_err(|e| format!("fetch tag after upsert: {e}"))
}

/// Mark a tag as enrolled under an identity. Sets status, identity_id, key_version, enrolled_at, appends event.
/// Creates the tag record if it does not exist.
pub fn update_tag_enrolled_by_uid(
    vault_dir: &Path,
    master_password: &str,
    uid: &str,
    identity_id: i64,
    key_version: Option<i32>,
    verification_url: Option<&str>,
) -> Result<TagRecord, String> {
    ensure_password(master_password)?;
    let uid = normalize_uid(uid);
    ensure_non_empty("tag uid", &uid)?;
    let salt = load_existing_salt(vault_dir)?;
    let key = derive_key(master_password, &salt)?;
    let conn = open_encrypted_db(&db_path(vault_dir), &key)?;
    create_schema(&conn)?;

    let now = chrono::Utc::now().to_rfc3339();
    let event_data = verification_url
        .map(|u| format!(r#"{{"verification_url":"{}"}}"#, u.replace('"', "\\\"")))
        .unwrap_or_else(|| "{}".to_string());

    let existing_id: Option<i64> = match conn.query_row(
        "SELECT id FROM tags WHERE UPPER(uid) = UPPER(?1) ORDER BY (deleted_at IS NULL) DESC, identity_id DESC, updated_at DESC LIMIT 1",
        params![&uid],
        |row| row.get(0),
    ) {
        Ok(id) => Some(id),
        Err(rusqlite::Error::QueryReturnedNoRows) => None,
        Err(e) => return Err(format!("check tag: {e}")),
    };

    let tag_id = match existing_id {
        Some(id) => {
            if let Some(kv) = key_version {
                conn.execute(
                    "UPDATE tags SET uid = ?1, status = ?2, identity_id = ?3, key_version = ?4, enrolled_at = ?5, last_seen_at = ?5, updated_at = ?5, deleted_at = NULL WHERE id = ?6",
                    params![&uid, "personalized_sdm", identity_id, kv, now, id],
                )
            } else {
                conn.execute(
                    "UPDATE tags SET uid = ?1, status = ?2, identity_id = ?3, enrolled_at = ?4, last_seen_at = ?4, updated_at = ?4, deleted_at = NULL WHERE id = ?5",
                    params![&uid, "personalized_sdm", identity_id, now, id],
                )
            }
            .map_err(|e| format!("update tag enrolled: {e}"))?;
            id
        }
        None => {
            conn.execute(
                "INSERT INTO tags (uid, status, identity_id, key_version, enrolled_at, last_seen_at, created_at, updated_at)
                 VALUES (?1, ?2, ?3, ?4, ?5, ?5, ?5, ?5)",
                params![&uid, "personalized_sdm", identity_id, key_version, now],
            )
            .map_err(|e| format!("insert tag: {e}"))?;
            conn.last_insert_rowid()
        }
    };

    append_tag_event_inner(&conn, tag_id, "enrolled", Some(&event_data))?;

    fetch_tag_by_id(&conn, tag_id)
        .map_err(|e| format!("fetch tag after enroll: {e}"))
}

/// Update a tag's key_version (e.g. after rotating keys on a pre-rotation tag).
/// Tag must exist and be controlled by the given identity_id.
pub fn update_tag_key_version(
    vault_dir: &Path,
    master_password: &str,
    uid: &str,
    identity_id: i64,
    key_version: i32,
) -> Result<TagRecord, String> {
    ensure_password(master_password)?;
    let uid = normalize_uid(uid);
    ensure_non_empty("tag uid", &uid)?;
    let salt = load_existing_salt(vault_dir)?;
    let key = derive_key(master_password, &salt)?;
    let conn = open_encrypted_db(&db_path(vault_dir), &key)?;
    create_schema(&conn)?;

    let tag_id: i64 = conn
        .query_row(
            "SELECT id FROM tags WHERE UPPER(uid) = UPPER(?1) AND deleted_at IS NULL",
            params![&uid],
            |row| row.get(0),
        )
        .map_err(|_| "tag not found".to_string())?;

    let tag_identity_id: Option<i64> = conn
        .query_row("SELECT identity_id FROM tags WHERE id = ?1 AND deleted_at IS NULL", params![tag_id], |row| row.get(0))
        .map_err(|e| format!("fetch tag: {e}"))?;

    if tag_identity_id != Some(identity_id) {
        return Err("tag is not controlled by the active identity".to_string());
    }

    let now = chrono::Utc::now().to_rfc3339();
    conn.execute(
        "UPDATE tags SET key_version = ?1, last_seen_at = ?2, updated_at = ?2 WHERE id = ?3",
        params![key_version, now, tag_id],
    )
    .map_err(|e| format!("update key_version: {e}"))?;

    append_tag_event_inner(&conn, tag_id, "keys_rotated", Some(&format!(r#"{{"key_version":{}}}"#, key_version)))?;

    fetch_tag_by_id(&conn, tag_id)
        .map_err(|e| format!("fetch tag after update: {e}"))
}

/// Mark a physical tag as reset to delivery-like state.
/// Clears ownership and key metadata so future operations treat it as plain NDEF.
/// Restores a soft-deleted tag if one exists with this UID.
pub fn mark_tag_reset_by_uid(
    vault_dir: &Path,
    master_password: &str,
    uid: &str,
    identity_id: i64,
) -> Result<TagRecord, String> {
    ensure_password(master_password)?;
    let uid = normalize_uid(uid);
    ensure_non_empty("tag uid", &uid)?;
    let salt = load_existing_salt(vault_dir)?;
    let key = derive_key(master_password, &salt)?;
    let conn = open_encrypted_db(&db_path(vault_dir), &key)?;
    create_schema(&conn)?;

    let now = chrono::Utc::now().to_rfc3339();
    let existing: Option<(i64, Option<i64>)> = conn
        .query_row(
            "SELECT id, identity_id FROM tags WHERE UPPER(uid) = UPPER(?1) ORDER BY (deleted_at IS NULL) DESC LIMIT 1",
            params![&uid],
            |row| Ok((row.get(0)?, row.get(1)?)),
        )
        .ok();

    let tag_id = if let Some((id, tag_identity_id)) = existing {
        if let Some(tid) = tag_identity_id {
            if tid != identity_id {
                return Err(format!(
                    "tag is controlled by a different identity (identity_id={}); only the enrolling identity can reset it",
                    tid
                ));
            }
        }
        conn.execute(
            "UPDATE tags SET status = ?1, identity_id = NULL, artwork_id = NULL, key_version = NULL, enrolled_at = NULL, last_seen_at = ?2, updated_at = ?2, deleted_at = NULL WHERE id = ?3",
            params!["factory_default_likely", now, id],
        )
        .map_err(|e| format!("reset tag record: {e}"))?;
        id
    } else {
        conn.execute(
            "INSERT INTO tags (uid, status, last_seen_at, created_at, updated_at)
             VALUES (?1, ?2, ?3, ?3, ?3)",
            params![&uid, "factory_default_likely", now],
        )
        .map_err(|e| format!("insert reset tag: {e}"))?;
        conn.last_insert_rowid()
    };

    append_tag_event_inner(&conn, tag_id, "reset", Some("{}"))?;
    fetch_tag_by_id(&conn, tag_id).map_err(|e| format!("fetch tag after reset: {e}"))
}

/// Release tag control. Handles two cases:
/// 1. Managed tag: clear identity_id and artwork_id, set status to released, append event.
/// 2. Recover: tag was released but status was overwritten by rescan; restore status to released.
pub fn release_tag_control(
    vault_dir: &Path,
    master_password: &str,
    tag_id: i64,
    identity_id: i64,
) -> Result<TagRecord, String> {
    ensure_password(master_password)?;
    let salt = load_existing_salt(vault_dir)?;
    let key = derive_key(master_password, &salt)?;
    let conn = open_encrypted_db(&db_path(vault_dir), &key)?;
    create_schema(&conn)?;

    let tag_identity_id: Option<i64> = match conn.query_row(
        "SELECT identity_id FROM tags WHERE id = ?1 AND deleted_at IS NULL",
        params![tag_id],
        |row| row.get(0),
    ) {
        Ok(id) => id,
        Err(rusqlite::Error::QueryReturnedNoRows) => return Err("tag not found".to_string()),
        Err(e) => return Err(format!("fetch tag: {e}")),
    };

    let now = chrono::Utc::now().to_rfc3339();

    if let Some(tid) = tag_identity_id {
        if tid == identity_id {
            conn.execute(
                "UPDATE tags SET status = ?1, identity_id = NULL, artwork_id = NULL, last_seen_at = ?2, updated_at = ?2 WHERE id = ?3 AND deleted_at IS NULL",
                params!["released", now, tag_id],
            )
            .map_err(|e| format!("release tag: {e}"))?;
            append_tag_event_inner(&conn, tag_id, "released", Some("{}"))?;
        } else {
            return Err("tag is not controlled by the active identity".to_string());
        }
    } else {
        // Tag has no identity. Allow release if we have a prior "released" event (restore after rescan)
        // or if the tag is in our vault (discovered or enrolled). Tags only enter via preflight or enroll.
        let has_released: bool = conn
            .query_row(
                "SELECT 1 FROM tag_events WHERE tag_id = ?1 AND event_type = 'released' LIMIT 1",
                params![tag_id],
                |row| row.get::<_, i32>(0),
            )
            .map(|v| v == 1)
            .unwrap_or(false);
        if has_released {
            conn.execute(
                "UPDATE tags SET status = ?1, artwork_id = NULL, last_seen_at = ?2, updated_at = ?2 WHERE id = ?3 AND deleted_at IS NULL",
                params!["released", now, tag_id],
            )
            .map_err(|e| format!("release tag: {e}"))?;
        } else {
            // Tag in vault with no identity (e.g. discovered-only). Allow first release.
            conn.execute(
                "UPDATE tags SET status = ?1, artwork_id = NULL, last_seen_at = ?2, updated_at = ?2 WHERE id = ?3 AND deleted_at IS NULL",
                params!["released", now, tag_id],
            )
            .map_err(|e| format!("release tag: {e}"))?;
            append_tag_event_inner(&conn, tag_id, "released", Some("{}"))?;
        }
    }

    let uid: String = conn
        .query_row("SELECT uid FROM tags WHERE id = ?1", params![tag_id], |row| row.get(0))
        .map_err(|e| format!("fetch tag uid: {e}"))?;
    fetch_tag_by_uid(&conn, &uid)
        .map_err(|e| format!("fetch tag after release: {e}"))
}

/// Forget a tag record. Soft-deletes the tag (sets deleted_at) so the row persists for
/// referential integrity (tag_events, future watermarks). The physical tag is unchanged.
/// Rescanning the tag will restore it.
pub fn forget_tag(
    vault_dir: &Path,
    master_password: &str,
    tag_id: i64,
) -> Result<(), String> {
    ensure_password(master_password)?;
    let salt = load_existing_salt(vault_dir)?;
    let key = derive_key(master_password, &salt)?;
    let conn = open_encrypted_db(&db_path(vault_dir), &key)?;
    create_schema(&conn)?;

    let n = conn
        .execute(
            "UPDATE tags SET deleted_at = datetime('now') WHERE id = ?1 AND deleted_at IS NULL",
            params![tag_id],
        )
        .map_err(|e| format!("forget tag: {e}"))?;
    if n == 0 {
        return Err("tag not found or already forgotten".to_string());
    }
    Ok(())
}

fn fetch_tag_by_id(conn: &Connection, tag_id: i64) -> Result<TagRecord, String> {
    conn.query_row(
        "SELECT id, uid, identity_id, artwork_id, status, tag_family, chip_subtype,
                key_version, provisioning_profile, anchor_mode, enrolled_at, last_seen_at,
                notes, created_at, updated_at, last_ndef_write_at
         FROM tags WHERE id = ?1 AND deleted_at IS NULL",
        params![tag_id],
        |row| {
            Ok(TagRecord {
                id: row.get(0)?,
                uid: row.get(1)?,
                identity_id: row.get(2)?,
                artwork_id: row.get(3)?,
                status: row.get(4)?,
                tag_family: row.get(5)?,
                chip_subtype: row.get(6)?,
                key_version: row.get(7)?,
                provisioning_profile: row.get(8)?,
                anchor_mode: row.get(9)?,
                enrolled_at: row.get(10)?,
                last_seen_at: row.get(11)?,
                notes: row.get(12)?,
                created_at: row.get(13)?,
                updated_at: row.get(14)?,
                last_ndef_write_at: row.get(15)?,
            })
        },
    )
    .map_err(|e| format!("tag not found: {e}"))
}

fn fetch_tag_by_uid(conn: &Connection, uid: &str) -> Result<TagRecord, String> {
    conn.query_row(
        "SELECT id, uid, identity_id, artwork_id, status, tag_family, chip_subtype,
                key_version, provisioning_profile, anchor_mode, enrolled_at, last_seen_at,
                notes, created_at, updated_at, last_ndef_write_at
         FROM tags WHERE uid = ?1 AND deleted_at IS NULL",
        params![uid],
        |row| {
            Ok(TagRecord {
                id: row.get(0)?,
                uid: row.get(1)?,
                identity_id: row.get(2)?,
                artwork_id: row.get(3)?,
                status: row.get(4)?,
                tag_family: row.get(5)?,
                chip_subtype: row.get(6)?,
                key_version: row.get(7)?,
                provisioning_profile: row.get(8)?,
                anchor_mode: row.get(9)?,
                enrolled_at: row.get(10)?,
                last_seen_at: row.get(11)?,
                notes: row.get(12)?,
                created_at: row.get(13)?,
                updated_at: row.get(14)?,
                last_ndef_write_at: row.get(15)?,
            })
        },
    )
    .map_err(|e| format!("tag not found: {e}"))
}

fn append_tag_event_inner(
    conn: &Connection,
    tag_id: i64,
    event_type: &str,
    event_data: Option<&str>,
) -> Result<(), String> {
    conn.execute(
        "INSERT INTO tag_events (tag_id, event_type, event_data) VALUES (?1, ?2, ?3)",
        params![tag_id, event_type, event_data],
    )
    .map_err(|e| format!("append tag event: {e}"))?;
    Ok(())
}

/// Mark a tag's NDEF as freshly written (stamps `last_ndef_write_at`).
pub fn update_tag_last_ndef_write(
    vault_dir: &Path,
    master_password: &str,
    uid: &str,
) -> Result<(), String> {
    ensure_password(master_password)?;
    ensure_non_empty("tag uid", uid)?;
    let salt = load_existing_salt(vault_dir)?;
    let key = derive_key(master_password, &salt)?;
    let conn = open_encrypted_db(&db_path(vault_dir), &key)?;
    create_schema(&conn)?;
    conn.execute(
        "UPDATE tags SET last_ndef_write_at = CURRENT_TIMESTAMP WHERE UPPER(uid) = UPPER(?1) AND deleted_at IS NULL",
        params![uid],
    )
    .map_err(|e| format!("update last_ndef_write_at: {e}"))?;
    Ok(())
}

/// Record that a tag was rewritten with a new verification URL.
/// Appends a "rewritten" event so get_tag_last_verification_url returns the new URL.
pub fn record_tag_verification_url_rewrite(
    vault_dir: &Path,
    master_password: &str,
    uid: &str,
    verification_url: &str,
) -> Result<(), String> {
    ensure_password(master_password)?;
    ensure_non_empty("tag uid", uid)?;
    ensure_non_empty("verification url", verification_url)?;
    let uid = normalize_uid(uid);
    let salt = load_existing_salt(vault_dir)?;
    let key = derive_key(master_password, &salt)?;
    let conn = open_encrypted_db(&db_path(vault_dir), &key)?;
    create_schema(&conn)?;
    let tag_id: i64 = conn
        .query_row(
            "SELECT id FROM tags WHERE UPPER(uid) = UPPER(?1) AND deleted_at IS NULL ORDER BY identity_id DESC, updated_at DESC LIMIT 1",
            params![&uid],
            |row| row.get(0),
        )
        .map_err(|_| "tag not found".to_string())?;
    let event_data = format!(
        r#"{{"verification_url":"{}"}}"#,
        verification_url.replace('"', "\\\"")
    );
    append_tag_event_inner(&conn, tag_id, "rewritten", Some(&event_data))?;
    Ok(())
}

/// Update a tag's notes. Notes are free-form text for user annotations.
pub fn update_tag_notes(
    vault_dir: &Path,
    master_password: &str,
    tag_id: i64,
    notes: Option<&str>,
) -> Result<TagRecord, String> {
    ensure_password(master_password)?;
    let salt = load_existing_salt(vault_dir)?;
    let key = derive_key(master_password, &salt)?;
    let conn = open_encrypted_db(&db_path(vault_dir), &key)?;
    create_schema(&conn)?;
    let notes_value = notes.map(str::trim).filter(|s| !s.is_empty());
    conn.execute(
        "UPDATE tags SET notes = ?1, updated_at = datetime('now') WHERE id = ?2 AND deleted_at IS NULL",
        params![notes_value, tag_id],
    )
    .map_err(|e| format!("update tag notes: {e}"))?;
    if conn.changes() == 0 {
        return Err("tag not found".to_string());
    }
    fetch_tag_by_id(&conn, tag_id).map_err(|e| format!("fetch tag after notes update: {e}"))
}

/// Returns true if `signer_fingerprint` matches the leaf certificate of any vault identity.
pub fn signer_matches_vault_identity(
    vault_dir: &Path,
    master_password: &str,
    signer_fingerprint: &str,
) -> Result<bool, String> {
    let fp = signer_fingerprint.trim();
    if fp.is_empty() {
        return Ok(false);
    }
    ensure_password(master_password)?;
    let salt = load_existing_salt(vault_dir)?;
    let key = derive_key(master_password, &salt)?;
    let conn = open_encrypted_db(&db_path(vault_dir), &key)?;
    create_schema(&conn)?;

    let mut stmt = conn
        .prepare(
            "SELECT certificate_chain FROM secure_keys WHERE certificate_chain IS NOT NULL AND certificate_chain != ''",
        )
        .map_err(|e| format!("prepare identity fingerprint query: {e}"))?;
    let rows = stmt
        .query_map([], |row| row.get::<_, String>(0))
        .map_err(|e| format!("query identity chains: {e}"))?;

    for row in rows {
        let chain_pem = row.map_err(|e| format!("read identity chain: {e}"))?;
        if let Ok(identity_fp) = certificate_fingerprint_sha256(&chain_pem) {
            if identity_fp.eq_ignore_ascii_case(fp) {
                return Ok(true);
            }
        }
    }
    Ok(false)
}

/// Returns the set of leaf certificate SHA-256 fingerprints for all vault identities.
pub fn list_vault_identity_fingerprints(
    vault_dir: &Path,
    master_password: &str,
) -> Result<Vec<String>, String> {
    ensure_password(master_password)?;
    let salt = load_existing_salt(vault_dir)?;
    let key = derive_key(master_password, &salt)?;
    let conn = open_encrypted_db(&db_path(vault_dir), &key)?;
    create_schema(&conn)?;

    let mut stmt = conn
        .prepare(
            "SELECT certificate_chain FROM secure_keys WHERE certificate_chain IS NOT NULL AND certificate_chain != ''",
        )
        .map_err(|e| format!("prepare identity fingerprint query: {e}"))?;
    let rows = stmt
        .query_map([], |row| row.get::<_, String>(0))
        .map_err(|e| format!("query identity chains: {e}"))?;

    let mut fingerprints = Vec::new();
    for row in rows {
        let chain_pem = row.map_err(|e| format!("read identity chain: {e}"))?;
        if let Ok(fp) = certificate_fingerprint_sha256(&chain_pem) {
            fingerprints.push(fp.to_ascii_lowercase());
        }
    }
    Ok(fingerprints)
}

/// SHA-256 fingerprint of public key (SPKI DER bytes), hex-encoded, lowercase.
/// Same key = same fingerprint even when certificate is renewed.
pub fn public_key_fingerprint_sha256(spki_der: &[u8]) -> String {
    let hash = Sha256::digest(spki_der);
    hex::encode(hash).to_ascii_lowercase()
}

/// SHA256 fingerprint of certificate PEM (DER bytes), hex-encoded.
pub fn certificate_fingerprint_sha256(certificate_pem: &str) -> Result<String, String> {
    let mut reader = BufReader::new(certificate_pem.as_bytes());
    let certs: Vec<_> = certs(&mut reader)
        .collect::<Result<Vec<_>, _>>()
        .map_err(|e| format!("parse certificate: {e}"))?;
    let leaf = certs
        .first()
        .ok_or_else(|| "certificate is empty".to_string())?;
    let mut hasher = Sha256::new();
    hasher.update(leaf);
    Ok(hex::encode(hasher.finalize()))
}

pub fn export_public_certificate(
    vault_dir: &Path,
    master_password: &str,
    output_path: &str,
) -> Result<(), String> {
    ensure_password(master_password)?;
    ensure_non_empty("output path", output_path)?;
    let identity = get_active_identity_material(vault_dir, master_password)?;
    let chain_bytes = &identity.certificate_chain_pem;
    let chain_str = std::str::from_utf8(chain_bytes)
        .map_err(|_| "certificate chain is not valid UTF-8".to_string())?;
    let mut reader = BufReader::new(chain_str.as_bytes());
    let certs: Vec<_> = certs(&mut reader)
        .collect::<Result<Vec<_>, _>>()
        .map_err(|e| format!("parse certificate chain: {e}"))?;
    let leaf = certs
        .first()
        .ok_or_else(|| "certificate chain is empty".to_string())?;
    let pem = format!(
        "-----BEGIN CERTIFICATE-----\n{}\n-----END CERTIFICATE-----\n",
        chunked_base64(leaf)
    );
    fs::write(output_path, pem).map_err(|e| format!("write certificate file: {e}"))?;
    Ok(())
}

fn chunked_base64(data: &[u8]) -> String {
    let mut b64 = BASE64.encode(data);
    b64.retain(|c| c != '\n' && c != '\r');
    b64.as_bytes()
        .chunks(64)
        .map(|c| String::from_utf8_lossy(c).into_owned())
        .collect::<Vec<_>>()
        .join("\n")
}

/// Returns the id of the active identity, or error if none.
pub fn get_active_identity_id(
    vault_dir: &Path,
    master_password: &str,
) -> Result<i64, String> {
    ensure_password(master_password)?;
    let salt = load_existing_salt(vault_dir)?;
    let key = derive_key(master_password, &salt)?;
    let conn = open_encrypted_db(&db_path(vault_dir), &key)?;
    create_schema(&conn)?;
    conn.query_row(
        "SELECT id FROM secure_keys WHERE is_active = 1 ORDER BY id DESC LIMIT 1",
        [],
        |row| row.get(0),
    )
    .map_err(|_| "no active identity in vault".to_string())
}

pub fn get_active_identity_material(
    vault_dir: &Path,
    master_password: &str,
) -> Result<IdentityMaterial, String> {
    ensure_password(master_password)?;
    let salt = load_existing_salt(vault_dir)?;
    let key = derive_key(master_password, &salt)?;
    let conn = open_encrypted_db(&db_path(vault_dir), &key)?;
    create_schema(&conn)?;
    conn.query_row(
        "SELECT id, COALESCE(private_key, key_pem), certificate_chain, is_production
         FROM secure_keys
         WHERE is_active = 1
         ORDER BY id DESC
         LIMIT 1",
        [],
        |row| {
            let cert_chain: Option<String> = row.get(2)?;
            Ok(IdentityMaterial {
                private_key_pem: row.get(1)?,
                certificate_chain_pem: cert_chain.unwrap_or_default().into_bytes(),
                is_production: row.get::<_, i64>(3).unwrap_or(0) != 0,
            })
        },
    )
    .map_err(|_| "no active identity in vault".to_string())
    .and_then(|identity| {
        if identity.certificate_chain_pem.is_empty() {
            Err("active identity has no certificate chain; generate or import a certificate first".to_string())
        } else {
            Ok(identity)
        }
    })
}

fn get_identity_by_id(vault_dir: &Path, master_password: &str, identity_id: i64) -> Result<IdentityMaterial, String> {
    ensure_password(master_password)?;
    let salt = load_existing_salt(vault_dir)?;
    let key = derive_key(master_password, &salt)?;
    let conn = open_encrypted_db(&db_path(vault_dir), &key)?;
    create_schema(&conn)?;
    conn.query_row(
        "SELECT id, COALESCE(private_key, key_pem), COALESCE(certificate_chain, ''), is_production
         FROM secure_keys
         WHERE id = ?1",
        params![identity_id],
        |row| {
            Ok(IdentityMaterial {
                private_key_pem: row.get(1)?,
                certificate_chain_pem: row.get::<_, String>(2)?.into_bytes(),
                is_production: row.get::<_, i64>(3).unwrap_or(0) != 0,
            })
        },
    )
    .map_err(|_| format!("identity id {} not found", identity_id))
}

pub fn vault_initialized(vault_dir: &Path) -> bool {
    db_path(vault_dir).exists() && meta_path(vault_dir).exists()
}

/// Sanitize a path's file name for use inside the export zip. Keeps _sealed and _charmed intact.
fn sanitize_basename_for_export(path: &Path) -> String {
    let name = path
        .file_name()
        .and_then(|n| n.to_str())
        .unwrap_or("asset");
    name.chars()
        .map(|c| {
            if c.is_ascii_alphanumeric() || c == '_' || c == '.' || c == '-' {
                c
            } else {
                '_'
            }
        })
        .collect()
}

const VAULT_EXPORT_IMAGES_DIR: &str = "images";
const VAULT_EXPORT_PATHS_FILE: &str = "images/original_paths.txt";
const VAULT_EXPORT_README: &str = "README.md";

const VAULT_EXPORT_README_CONTENT: &str = r#"# Vault Export

This archive contains a backup of your Trust Systems vault: `vault.db`, `vault-meta.json`, and an `images/` folder.

## Images folder

The images folder is included for convenience. It contains copies of the image files that were linked to your draft records at the time of export.

- **To restore linkage**: Manually copy or move the files from `images/` back to their original locations (see `images/original_paths.txt` for the full paths). If the vault is then restored on the same machine with the same paths, draft records will again point to valid files.

- **Otherwise**: If you restore on a new machine or prefer not to restore paths, the draft records in the vault will still have metadata (title, author, hashes), but the file paths will not point to valid files. You can delete those draft records and re-add the artwork by selecting the files from the extracted `images/` folder.

## Restoring the vault

To restore from this export, use Restore from backup on the lock screen, or place `vault.db` and `vault-meta.json` into your Trust Systems vault directory and unlock with the same master password.
"#;

/// Export the vault to a zip file: vault.db, vault-meta.json, an images/ folder (copies of
/// all referenced asset files), images/original_paths.txt, and README.md.
pub fn export_vault_to_zip(
    vault_dir: &Path,
    master_password: &str,
    output_path: &Path,
) -> Result<(), String> {
    ensure_password(master_password)?;
    if !vault_initialized(vault_dir) {
        return Err("vault is not initialized".to_string());
    }

    let drafts = list_drafts(vault_dir, master_password)?;

    let out_file = fs::File::create(output_path).map_err(|e| format!("create export file: {e}"))?;
    let options = SimpleFileOptions::default().unix_permissions(0o644);
    let mut zip = ZipWriter::new(out_file);

    // Add vault.db
    let db = db_path(vault_dir);
    if db.exists() {
        zip.start_file(VAULT_DB_FILE, options)
            .map_err(|e| format!("zip add vault.db: {e}"))?;
        let mut f = fs::File::open(&db).map_err(|e| format!("read vault.db: {e}"))?;
        std::io::copy(&mut f, &mut zip).map_err(|e| format!("write vault.db to zip: {e}"))?;
    }

    // Add vault-meta.json
    let meta = meta_path(vault_dir);
    if meta.exists() {
        zip.start_file(VAULT_META_FILE, options)
            .map_err(|e| format!("zip add vault-meta.json: {e}"))?;
        let mut f = fs::File::open(&meta).map_err(|e| format!("read vault-meta.json: {e}"))?;
        std::io::copy(&mut f, &mut zip).map_err(|e| format!("write vault-meta.json to zip: {e}"))?;
    }

    let mut paths_lines: Vec<String> = Vec::new();

    for draft in &drafts {
        let Some(ref asset_path) = draft.asset_path else {
            continue;
        };
        let path = Path::new(asset_path.trim());
        if path.is_file() {
            let basename = sanitize_basename_for_export(path);
            let zip_name = format!("{}/draft_{}_{}", VAULT_EXPORT_IMAGES_DIR, draft.id, basename);
            paths_lines.push(format!("{} → {}", zip_name, asset_path));

            zip.start_file(&zip_name, options)
                .map_err(|e| format!("zip add image {}: {e}", path.display()))?;
            let mut f = fs::File::open(path).map_err(|e| format!("read asset {}: {e}", path.display()))?;
            std::io::copy(&mut f, &mut zip).map_err(|e| format!("write image to zip: {e}"))?;
        }
    }

    // Add images/original_paths.txt
    let paths_content = paths_lines.join("\n");
    zip.start_file(VAULT_EXPORT_PATHS_FILE, options)
        .map_err(|e| format!("zip add original_paths.txt: {e}"))?;
    zip.write_all(paths_content.as_bytes())
        .map_err(|e| format!("write original_paths.txt: {e}"))?;

    // Add README.md
    zip.start_file(VAULT_EXPORT_README, options)
        .map_err(|e| format!("zip add README.md: {e}"))?;
    zip.write_all(VAULT_EXPORT_README_CONTENT.as_bytes())
        .map_err(|e| format!("write README.md: {e}"))?;

    zip.finish().map_err(|e| format!("finish zip: {e}"))?;
    Ok(())
}

/// Maximum decompressed size for vault.db when importing (zip bomb protection).
const IMPORT_DB_MAX_BYTES: u64 = 256 * 1024 * 1024;
/// Maximum decompressed size for vault-meta.json when importing.
const IMPORT_META_MAX_BYTES: u64 = 64 * 1024;

/// Import vault from a Trust Systems export zip. Extracts only vault.db and vault-meta.json
/// to the vault directory. Safe to call when vault is locked (no open connections).
/// Overwrites existing vault files if present. Only accepts root-level vault.db and vault-meta.json.
pub fn import_vault_from_zip(zip_path: &Path, vault_dir: &Path) -> Result<(), String> {
    let file = fs::File::open(zip_path).map_err(|e| format!("open zip file: {e}"))?;
    let mut zip = ZipArchive::new(std::io::BufReader::new(file))
        .map_err(|e| format!("read zip archive: {e}"))?;

    let mut db_data = Vec::new();
    {
        let reader = zip
            .by_name(VAULT_DB_FILE)
            .map_err(|_| "zip does not contain vault.db".to_string())?;
        let mut limit = reader.take(IMPORT_DB_MAX_BYTES);
        std::io::copy(&mut limit, &mut db_data).map_err(|e| format!("extract vault.db: {e}"))?;
        if limit.limit() == 0 {
            return Err("vault.db in zip exceeds maximum size".to_string());
        }
    }

    let mut meta_data = Vec::new();
    {
        let reader = zip
            .by_name(VAULT_META_FILE)
            .map_err(|_| "zip does not contain vault-meta.json".to_string())?;
        let mut limit = reader.take(IMPORT_META_MAX_BYTES);
        std::io::copy(&mut limit, &mut meta_data).map_err(|e| format!("extract vault-meta.json: {e}"))?;
        if limit.limit() == 0 {
            return Err("vault-meta.json in zip exceeds maximum size".to_string());
        }
    }

    fs::create_dir_all(vault_dir).map_err(|e| format!("create vault directory: {e}"))?;
    fs::write(db_path(vault_dir), &db_data).map_err(|e| format!("write vault.db: {e}"))?;
    fs::write(meta_path(vault_dir), &meta_data).map_err(|e| format!("write vault-meta.json: {e}"))?;

    Ok(())
}

fn validate_private_key_pem(raw: &[u8]) -> Result<(), String> {
    let text = std::str::from_utf8(raw).map_err(|_| "key file is not valid UTF-8 PEM".to_string())?;
    let begin_ok = text.contains("-----BEGIN PRIVATE KEY-----")
        || text.contains("-----BEGIN EC PRIVATE KEY-----");
    let end_ok = text.contains("-----END PRIVATE KEY-----")
        || text.contains("-----END EC PRIVATE KEY-----");
    if !begin_ok || !end_ok {
        return Err("key file is not a valid PEM private key".to_string());
    }
    Ok(())
}

fn ensure_country(country: &str) -> Result<(), String> {
    let c = country.trim();
    if c.len() != 2 || !c.chars().all(|ch| ch.is_ascii_uppercase()) {
        return Err("country must be a 2-letter uppercase ISO-3166 code (e.g. US)".to_string());
    }
    Ok(())
}

fn parse_leaf_certificate_metadata(
    cert_chain_pem: &str,
) -> Result<(Option<String>, Option<String>, Option<String>, Option<String>, Vec<u8>), String> {
    let mut reader = BufReader::new(cert_chain_pem.as_bytes());
    let chain = certs(&mut reader)
        .collect::<Result<Vec<_>, _>>()
        .map_err(|e| format!("parse certificate chain PEM: {e}"))?;
    let first = chain
        .first()
        .ok_or_else(|| "certificate chain is empty".to_string())?;
    parse_leaf_certificate_metadata_from_der(first.as_ref())
}

/// Returns (common_name, organization, issuer_cn, not_after, spki_der).
/// common_name and organization from subject; issuer_cn from issuer.
fn parse_leaf_certificate_metadata_from_der(
    cert_der: &[u8],
) -> Result<(Option<String>, Option<String>, Option<String>, Option<String>, Vec<u8>), String> {
    let (_, cert) = x509_parser::parse_x509_certificate(cert_der)
        .map_err(|_| "unable to parse leaf certificate".to_string())?;
    let cn = cert
        .subject()
        .iter_common_name()
        .next()
        .and_then(|v| v.as_str().ok())
        .map(str::to_string);
    let org = cert
        .subject()
        .iter_organization()
        .next()
        .and_then(|v| v.as_str().ok())
        .map(str::to_string);
    let issuer_cn = cert
        .issuer()
        .iter_common_name()
        .next()
        .and_then(|v| v.as_str().ok())
        .map(str::to_string);
    let not_after = cert.validity().not_after.to_rfc2822().ok();
    let spki_der = cert.public_key().raw.to_vec();
    Ok((cn, org, issuer_cn, not_after, spki_der))
}

fn public_key_der_from_private_key_pem(private_key_pem: &[u8]) -> Result<Vec<u8>, String> {
    let pem = std::str::from_utf8(private_key_pem)
        .map_err(|_| "private key in vault is not valid UTF-8 PEM".to_string())?;
    let signing_key =
        SigningKey::from_pkcs8_pem(pem).map_err(|e| format!("parse private key from vault: {e}"))?;
    let verifying_key = VerifyingKey::from(&signing_key);
    let der = verifying_key
        .to_public_key_der()
        .map_err(|e| format!("encode public key DER: {e}"))?;
    Ok(der.as_bytes().to_vec())
}

fn chrono_like_timestamp() -> String {
    use std::time::{SystemTime, UNIX_EPOCH};
    SystemTime::now()
        .duration_since(UNIX_EPOCH)
        .map(|d| d.as_secs().to_string())
        .unwrap_or_else(|_| "0".to_string())
}

fn create_schema(conn: &Connection) -> Result<(), String> {
    conn.execute_batch(
        "CREATE TABLE IF NOT EXISTS drafts (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            artifact_title TEXT NOT NULL,
            author TEXT NOT NULL,
            physical_hash TEXT,
            perceptual_hash TEXT,
            draft_status TEXT NOT NULL,
            is_certified INTEGER NOT NULL DEFAULT 0,
            c2pa_manifest_id TEXT,
            asset_path TEXT,
            created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
        );
        CREATE TABLE IF NOT EXISTS secure_keys (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            key_label TEXT NOT NULL,
            key_pem BLOB NOT NULL,
            private_key BLOB,
            certificate_chain TEXT,
            is_production INTEGER NOT NULL DEFAULT 0,
            is_active INTEGER NOT NULL DEFAULT 1,
            common_name TEXT,
            organization TEXT,
            country TEXT,
            issuer TEXT,
            not_after TEXT,
            created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
        );
        CREATE TRIGGER IF NOT EXISTS drafts_set_updated_at
        AFTER UPDATE ON drafts
        FOR EACH ROW
        BEGIN
            UPDATE drafts SET updated_at = CURRENT_TIMESTAMP WHERE id = OLD.id;
        END;",
    )
    .map_err(|e| format!("create vault schema: {e}"))?;
    migrate_schema_v2(conn)?;
    migrate_schema_v3(conn)?;
    migrate_schema_v4(conn)?;
    migrate_schema_v5(conn)?;
    migrate_schema_v6(conn)?;
    migrate_schema_v7(conn)?;
    migrate_schema_v8(conn)?;
    migrate_schema_v9(conn)?;
    migrate_schema_v10(conn)?;
    migrate_schema_v11(conn)?;
    migrate_schema_v12(conn)?;
    migrate_schema_v13(conn)?;
    migrate_schema_v14(conn)?;
    migrate_schema_v15(conn)?;
    migrate_schema_v16(conn)?;
    migrate_schema_v17(conn)?;
    Ok(())
}

fn migrate_schema_v17(conn: &Connection) -> Result<(), String> {
    let version: i64 = conn
        .query_row("PRAGMA user_version;", [], |row| row.get(0))
        .map_err(|e| format!("read schema version: {e}"))?;
    if version >= 17 {
        return Ok(());
    }

    conn.execute_batch(
        "CREATE UNIQUE INDEX IF NOT EXISTS idx_drafts_manifest_active_unique
         ON drafts(c2pa_manifest_id)
         WHERE c2pa_manifest_id IS NOT NULL AND deleted_at IS NULL;",
    )
    .map_err(|e| format!("apply schema migration v17 index: {e}"))?;

    conn.execute_batch("PRAGMA user_version = 17;")
        .map_err(|e| format!("apply schema migration v17: {e}"))?;
    Ok(())
}

fn migrate_schema_v12(conn: &Connection) -> Result<(), String> {
    let version: i64 = conn
        .query_row("PRAGMA user_version;", [], |row| row.get(0))
        .map_err(|e| format!("read schema version: {e}"))?;
    if version >= 12 {
        return Ok(());
    }

    add_column_if_missing(
        conn,
        "tags",
        "last_ndef_write_at",
        "ALTER TABLE tags ADD COLUMN last_ndef_write_at TEXT;",
    )?;

    conn.execute_batch("PRAGMA user_version = 12;")
        .map_err(|e| format!("apply schema migration v12: {e}"))?;
    Ok(())
}

fn migrate_schema_v13(conn: &Connection) -> Result<(), String> {
    let version: i64 = conn
        .query_row("PRAGMA user_version;", [], |row| row.get(0))
        .map_err(|e| format!("read schema version: {e}"))?;
    if version >= 13 {
        return Ok(());
    }

    if table_exists(conn, "trusted_contacts")? {
        add_column_if_missing(
            conn,
            "trusted_contacts",
            "notes",
            "ALTER TABLE trusted_contacts ADD COLUMN notes TEXT;",
        )?;
    }

    conn.execute_batch("PRAGMA user_version = 13;")
        .map_err(|e| format!("apply schema migration v13: {e}"))?;
    Ok(())
}

fn migrate_schema_v14(conn: &Connection) -> Result<(), String> {
    let version: i64 = conn
        .query_row("PRAGMA user_version;", [], |row| row.get(0))
        .map_err(|e| format!("read schema version: {e}"))?;
    if version >= 14 {
        return Ok(());
    }

    add_column_if_missing(
        conn,
        "drafts",
        "c2pa_claim_id",
        "ALTER TABLE drafts ADD COLUMN c2pa_claim_id TEXT;",
    )?;

    conn.execute_batch("PRAGMA user_version = 14;")
        .map_err(|e| format!("apply schema migration v14: {e}"))?;
    Ok(())
}

fn migrate_schema_v15(conn: &Connection) -> Result<(), String> {
    let version: i64 = conn
        .query_row("PRAGMA user_version;", [], |row| row.get(0))
        .map_err(|e| format!("read schema version: {e}"))?;
    if version >= 15 {
        return Ok(());
    }

    for (col, sql) in [
        ("keywords", "ALTER TABLE drafts ADD COLUMN keywords TEXT;"),
        ("genre", "ALTER TABLE drafts ADD COLUMN genre TEXT;"),
        ("material", "ALTER TABLE drafts ADD COLUMN material TEXT;"),
        ("contributor", "ALTER TABLE drafts ADD COLUMN contributor TEXT;"),
        ("abstract", "ALTER TABLE drafts ADD COLUMN abstract TEXT;"),
    ] {
        add_column_if_missing(conn, "drafts", col, sql)?;
    }

    conn.execute_batch("PRAGMA user_version = 15;")
        .map_err(|e| format!("apply schema migration v15: {e}"))?;
    Ok(())
}

fn migrate_schema_v16(conn: &Connection) -> Result<(), String> {
    let version: i64 = conn
        .query_row("PRAGMA user_version;", [], |row| row.get(0))
        .map_err(|e| format!("read schema version: {e}"))?;
    if version >= 16 {
        return Ok(());
    }

    add_column_if_missing(
        conn,
        "drafts",
        "deleted_at",
        "ALTER TABLE drafts ADD COLUMN deleted_at TEXT;",
    )?;
    add_column_if_missing(
        conn,
        "tags",
        "deleted_at",
        "ALTER TABLE tags ADD COLUMN deleted_at TEXT;",
    )?;

    conn.execute_batch("PRAGMA user_version = 16;")
        .map_err(|e| format!("apply schema migration v16: {e}"))?;
    Ok(())
}

fn migrate_schema_v11(conn: &Connection) -> Result<(), String> {
    let version: i64 = conn
        .query_row("PRAGMA user_version;", [], |row| row.get(0))
        .map_err(|e| format!("read schema version: {e}"))?;
    if version >= 11 {
        return Ok(());
    }

    add_column_if_missing(
        conn,
        "drafts",
        "ipfs_cid",
        "ALTER TABLE drafts ADD COLUMN ipfs_cid TEXT;",
    )?;

    conn.execute_batch("PRAGMA user_version = 11;")
        .map_err(|e| format!("apply schema migration v11: {e}"))?;
    Ok(())
}

fn migrate_schema_v10(conn: &Connection) -> Result<(), String> {
    let version: i64 = conn
        .query_row("PRAGMA user_version;", [], |row| row.get(0))
        .map_err(|e| format!("read schema version: {e}"))?;
    if version >= 10 {
        return Ok(());
    }

    add_column_if_missing(
        conn,
        "drafts",
        "description",
        "ALTER TABLE drafts ADD COLUMN description TEXT;",
    )?;
    add_column_if_missing(
        conn,
        "drafts",
        "date_created",
        "ALTER TABLE drafts ADD COLUMN date_created TEXT;",
    )?;
    add_column_if_missing(
        conn,
        "drafts",
        "copyright_notice",
        "ALTER TABLE drafts ADD COLUMN copyright_notice TEXT;",
    )?;
    add_column_if_missing(
        conn,
        "drafts",
        "license",
        "ALTER TABLE drafts ADD COLUMN license TEXT;",
    )?;

    conn.execute_batch("PRAGMA user_version = 10;")
        .map_err(|e| format!("apply schema migration v10: {e}"))?;
    Ok(())
}

fn migrate_schema_v6(conn: &Connection) -> Result<(), String> {
    let version: i64 = conn
        .query_row("PRAGMA user_version;", [], |row| row.get(0))
        .map_err(|e| format!("read schema version: {e}"))?;
    if version >= 6 {
        return Ok(());
    }

    conn.execute_batch(
        "CREATE TABLE IF NOT EXISTS trusted_contacts (
            id TEXT PRIMARY KEY,
            common_name TEXT NOT NULL,
            organization TEXT,
            certificate_pem TEXT NOT NULL,
            added_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
        );",
    )
    .map_err(|e| format!("create trusted_contacts table: {e}"))?;

    conn.execute_batch("PRAGMA user_version = 6;")
        .map_err(|e| format!("apply schema migration v6: {e}"))?;
    Ok(())
}

fn migrate_schema_v7(conn: &Connection) -> Result<(), String> {
    let version: i64 = conn
        .query_row("PRAGMA user_version;", [], |row| row.get(0))
        .map_err(|e| format!("read schema version: {e}"))?;
    if version >= 7 {
        return Ok(());
    }
    if !table_exists(conn, "trusted_contacts")? {
        conn.execute_batch("PRAGMA user_version = 7;")
            .map_err(|e| format!("apply schema migration v7: {e}"))?;
        return Ok(());
    }

    add_column_if_missing(
        conn,
        "trusted_contacts",
        "certificate_fingerprint",
        "ALTER TABLE trusted_contacts ADD COLUMN certificate_fingerprint TEXT;",
    )?;

    let mut stmt = conn
        .prepare(
            "SELECT id, certificate_pem
             FROM trusted_contacts
             ORDER BY added_at DESC, id DESC",
        )
        .map_err(|e| format!("prepare trusted contact migration query: {e}"))?;
    let rows = stmt
        .query_map([], |row| {
            let id: String = row.get(0)?;
            let pem: String = row.get(1)?;
            Ok((id, pem))
        })
        .map_err(|e| format!("query trusted contact migration rows: {e}"))?;

    let mut seen_fingerprints = HashSet::new();
    let mut updates: Vec<(String, String)> = Vec::new();
    let mut duplicate_ids: Vec<String> = Vec::new();
    for row in rows {
        let (id, pem) = row.map_err(|e| format!("read trusted contact migration row: {e}"))?;
        let fp = certificate_fingerprint_sha256(&pem)?;
        let fp = fp.to_ascii_lowercase();
        if seen_fingerprints.insert(fp.clone()) {
            updates.push((id, fp));
        } else {
            duplicate_ids.push(id);
        }
    }

    for id in duplicate_ids {
        conn.execute("DELETE FROM trusted_contacts WHERE id = ?1", params![id])
            .map_err(|e| format!("deduplicate trusted contacts: {e}"))?;
    }
    for (id, fp) in updates {
        conn.execute(
            "UPDATE trusted_contacts SET certificate_fingerprint = ?1 WHERE id = ?2",
            params![fp, id],
        )
        .map_err(|e| format!("backfill trusted contact fingerprints: {e}"))?;
    }

    conn.execute_batch(
        "CREATE UNIQUE INDEX IF NOT EXISTS idx_trusted_contacts_certificate_fingerprint
         ON trusted_contacts (certificate_fingerprint);",
    )
    .map_err(|e| format!("create trusted contact fingerprint index: {e}"))?;

    conn.execute_batch("PRAGMA user_version = 7;")
        .map_err(|e| format!("apply schema migration v7: {e}"))?;
    Ok(())
}

fn migrate_schema_v8(conn: &Connection) -> Result<(), String> {
    // Always ensure tags tables exist (handles restored backups, partial migrations).
    conn.execute_batch(
        "CREATE TABLE IF NOT EXISTS tags (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            uid TEXT NOT NULL UNIQUE,
            identity_id INTEGER,
            artwork_id INTEGER,
            status TEXT NOT NULL,
            tag_family TEXT,
            chip_subtype TEXT,
            key_version INTEGER,
            provisioning_profile TEXT,
            anchor_mode TEXT,
            enrolled_at TEXT,
            last_seen_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
            notes TEXT,
            created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (identity_id) REFERENCES secure_keys(id),
            FOREIGN KEY (artwork_id) REFERENCES drafts(id)
        );
        CREATE TABLE IF NOT EXISTS tag_events (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            tag_id INTEGER NOT NULL,
            event_type TEXT NOT NULL,
            event_data TEXT,
            created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (tag_id) REFERENCES tags(id)
        );
        CREATE INDEX IF NOT EXISTS idx_tags_uid ON tags(uid);
        CREATE INDEX IF NOT EXISTS idx_tags_identity ON tags(identity_id);
        CREATE INDEX IF NOT EXISTS idx_tags_artwork ON tags(artwork_id);
        CREATE INDEX IF NOT EXISTS idx_tags_status ON tags(status);
        CREATE INDEX IF NOT EXISTS idx_tag_events_tag ON tag_events(tag_id);
        CREATE TRIGGER IF NOT EXISTS tags_set_updated_at
        AFTER UPDATE ON tags
        FOR EACH ROW
        BEGIN
            UPDATE tags SET updated_at = CURRENT_TIMESTAMP WHERE id = OLD.id;
        END;",
    )
    .map_err(|e| format!("create tags and tag_events tables: {e}"))?;

    let version: i64 = conn
        .query_row("PRAGMA user_version;", [], |row| row.get(0))
        .map_err(|e| format!("read schema version: {e}"))?;
    if version < 8 {
        conn.execute_batch("PRAGMA user_version = 8;")
            .map_err(|e| format!("apply schema migration v8: {e}"))?;
    }
    Ok(())
}

fn migrate_schema_v9(conn: &Connection) -> Result<(), String> {
    let version: i64 = conn
        .query_row("PRAGMA user_version;", [], |row| row.get(0))
        .map_err(|e| format!("read schema version: {e}"))?;
    if version >= 9 {
        return Ok(());
    }

    add_column_if_missing(
        conn,
        "secure_keys",
        "public_key_fingerprint",
        "ALTER TABLE secure_keys ADD COLUMN public_key_fingerprint TEXT;",
    )?;

    // Backfill public_key_fingerprint for existing identities
    let mut stmt = conn
        .prepare(
            "SELECT id, private_key, certificate_chain
             FROM secure_keys
             WHERE (private_key IS NOT NULL AND private_key != '') OR (certificate_chain IS NOT NULL AND certificate_chain != '')",
        )
        .map_err(|e| format!("prepare secure_keys backfill query: {e}"))?;
    let rows = stmt
        .query_map([], |row| {
            let id: i64 = row.get(0)?;
            let private_key: Option<Vec<u8>> = row.get(1)?;
            let cert_chain: Option<String> = row.get(2)?;
            Ok((id, private_key, cert_chain))
        })
        .map_err(|e| format!("query secure_keys backfill: {e}"))?;

    for row in rows {
        let (id, private_key, cert_chain) = row.map_err(|e| format!("read secure_keys row: {e}"))?;
        let spki_der = if let Some(ref chain) = cert_chain {
            if chain.trim().is_empty() {
                None
            } else {
                parse_leaf_certificate_metadata(chain)
                    .ok()
                    .map(|(_, _, _, _, spki)| spki)
            }
        } else {
            None
        };
        let spki_der = spki_der.or_else(|| {
            private_key.as_ref().and_then(|pk| {
                public_key_der_from_private_key_pem(pk).ok()
            })
        });
        if let Some(spki) = spki_der {
            let fp = public_key_fingerprint_sha256(&spki);
            conn.execute(
                "UPDATE secure_keys SET public_key_fingerprint = ?1 WHERE id = ?2",
                params![fp, id],
            )
            .map_err(|e| format!("update secure_keys public_key_fingerprint: {e}"))?;
        }
    }

    conn.execute_batch(
        "CREATE INDEX IF NOT EXISTS idx_secure_keys_public_key_fingerprint
         ON secure_keys (public_key_fingerprint);",
    )
    .map_err(|e| format!("create public_key_fingerprint index: {e}"))?;

    conn.execute_batch("PRAGMA user_version = 9;")
        .map_err(|e| format!("apply schema migration v9: {e}"))?;
    Ok(())
}

fn migrate_schema_v2(conn: &Connection) -> Result<(), String> {
    let version: i64 = conn
        .query_row("PRAGMA user_version;", [], |row| row.get(0))
        .map_err(|e| format!("read schema version: {e}"))?;
    if version >= 2 {
        return Ok(());
    }

    add_column_if_missing(
        conn,
        "drafts",
        "is_certified",
        "ALTER TABLE drafts ADD COLUMN is_certified INTEGER NOT NULL DEFAULT 0;",
    )?;
    add_column_if_missing(
        conn,
        "drafts",
        "c2pa_manifest_id",
        "ALTER TABLE drafts ADD COLUMN c2pa_manifest_id TEXT;",
    )?;

    conn.execute_batch(
        "UPDATE drafts SET is_certified = 0 WHERE is_certified IS NULL;
         PRAGMA user_version = 2;",
    )
    .map_err(|e| format!("apply schema migration v2: {e}"))?;
    Ok(())
}

fn migrate_schema_v3(conn: &Connection) -> Result<(), String> {
    let version: i64 = conn
        .query_row("PRAGMA user_version;", [], |row| row.get(0))
        .map_err(|e| format!("read schema version: {e}"))?;
    if version >= 3 {
        return Ok(());
    }

    add_column_if_missing(
        conn,
        "secure_keys",
        "private_key",
        "ALTER TABLE secure_keys ADD COLUMN private_key BLOB;",
    )?;
    add_column_if_missing(
        conn,
        "secure_keys",
        "certificate_chain",
        "ALTER TABLE secure_keys ADD COLUMN certificate_chain TEXT;",
    )?;
    add_column_if_missing(
        conn,
        "secure_keys",
        "is_production",
        "ALTER TABLE secure_keys ADD COLUMN is_production INTEGER NOT NULL DEFAULT 0;",
    )?;
    add_column_if_missing(
        conn,
        "secure_keys",
        "common_name",
        "ALTER TABLE secure_keys ADD COLUMN common_name TEXT;",
    )?;
    add_column_if_missing(
        conn,
        "secure_keys",
        "organization",
        "ALTER TABLE secure_keys ADD COLUMN organization TEXT;",
    )?;
    add_column_if_missing(
        conn,
        "secure_keys",
        "country",
        "ALTER TABLE secure_keys ADD COLUMN country TEXT;",
    )?;
    add_column_if_missing(
        conn,
        "secure_keys",
        "issuer",
        "ALTER TABLE secure_keys ADD COLUMN issuer TEXT;",
    )?;
    add_column_if_missing(
        conn,
        "secure_keys",
        "not_after",
        "ALTER TABLE secure_keys ADD COLUMN not_after TEXT;",
    )?;

    conn.execute_batch(
        "UPDATE secure_keys SET private_key = key_pem WHERE private_key IS NULL AND key_pem IS NOT NULL;
         UPDATE secure_keys SET key_pem = private_key WHERE key_pem IS NULL AND private_key IS NOT NULL;
         UPDATE secure_keys SET is_production = 0 WHERE is_production IS NULL;
         PRAGMA user_version = 3;",
    )
    .map_err(|e| format!("apply schema migration v3: {e}"))?;
    Ok(())
}

fn migrate_schema_v4(conn: &Connection) -> Result<(), String> {
    let version: i64 = conn
        .query_row("PRAGMA user_version;", [], |row| row.get(0))
        .map_err(|e| format!("read schema version: {e}"))?;
    if version >= 4 {
        return Ok(());
    }

    if table_exists(conn, "drafts")? {
        add_column_if_missing(
            conn,
            "drafts",
            "perceptual_hash",
            "ALTER TABLE drafts ADD COLUMN perceptual_hash TEXT;",
        )?;
    }

    // Compatibility path for environments that already model records as "assets".
    if table_exists(conn, "assets")? {
        add_column_if_missing(
            conn,
            "assets",
            "perceptual_hash",
            "ALTER TABLE assets ADD COLUMN perceptual_hash TEXT;",
        )?;
    }

    conn.execute_batch("PRAGMA user_version = 4;")
        .map_err(|e| format!("apply schema migration v4: {e}"))?;
    Ok(())
}

fn migrate_schema_v5(conn: &Connection) -> Result<(), String> {
    let version: i64 = conn
        .query_row("PRAGMA user_version;", [], |row| row.get(0))
        .map_err(|e| format!("read schema version: {e}"))?;
    if version >= 5 {
        return Ok(());
    }

    add_column_if_missing(
        conn,
        "drafts",
        "asset_path",
        "ALTER TABLE drafts ADD COLUMN asset_path TEXT;",
    )?;

    conn.execute_batch("PRAGMA user_version = 5;")
        .map_err(|e| format!("apply schema migration v5: {e}"))?;
    Ok(())
}

fn add_column_if_missing(
    conn: &Connection,
    table: &str,
    column: &str,
    alter_sql: &str,
) -> Result<(), String> {
    let mut stmt = conn
        .prepare(&format!("PRAGMA table_info({});", table))
        .map_err(|e| format!("inspect table {}: {e}", table))?;
    let cols = stmt
        .query_map([], |row| row.get::<_, String>(1))
        .map_err(|e| format!("read table info for {}: {e}", table))?;
    for col in cols {
        if col.map_err(|e| format!("parse table info row: {e}"))? == column {
            return Ok(());
        }
    }
    conn.execute_batch(alter_sql)
        .map_err(|e| format!("alter table {} add column {}: {e}", table, column))?;
    Ok(())
}

fn table_exists(conn: &Connection, table: &str) -> Result<bool, String> {
    let count: i64 = conn
        .query_row(
            "SELECT count(*) FROM sqlite_master WHERE type = 'table' AND name = ?1",
            params![table],
            |row| row.get(0),
        )
        .map_err(|e| format!("check table {} existence: {e}", table))?;
    Ok(count > 0)
}

fn open_encrypted_db(path: &Path, key: &Zeroizing<[u8; KEY_LEN]>) -> Result<Connection, String> {
    let conn = Connection::open(path).map_err(|e| format!("open vault database: {e}"))?;
    let key_hex = Zeroizing::new(hex::encode(&key[..]));
    let pragma_sql = Zeroizing::new(format!(
        "PRAGMA key = \"x'{}'\";
         PRAGMA cipher_compatibility = 4;",
        key_hex.as_str()
    ));
    conn.execute_batch(pragma_sql.as_str())
        .map_err(|e| format!("set encryption key: {e}"))?;

    conn.query_row("SELECT count(*) FROM sqlite_master", [], |_row| Ok(()))
        .map_err(|_| "invalid vault password".to_string())?;
    Ok(conn)
}

fn derive_key(master_password: &str, salt: &[u8]) -> Result<Zeroizing<[u8; KEY_LEN]>, String> {
    let params = Params::new(19_456, 2, 1, Some(KEY_LEN))
        .map_err(|e| format!("argon2 params: {e}"))?;
    let argon2 = Argon2::new(Algorithm::Argon2id, Version::V0x13, params);
    let mut out = Zeroizing::new([0u8; KEY_LEN]);
    argon2
        .hash_password_into(master_password.as_bytes(), salt, out.as_mut())
        .map_err(|e| format!("derive vault key: {e}"))?;
    Ok(out)
}

fn load_or_create_salt(vault_dir: &Path) -> Result<Vec<u8>, String> {
    if meta_path(vault_dir).exists() {
        return load_existing_salt(vault_dir);
    }

    let mut salt = [0u8; SALT_LEN];
    rand::rngs::OsRng.fill_bytes(&mut salt);

    let meta = VaultMeta {
        salt_b64: BASE64.encode(salt),
    };
    let payload =
        serde_json::to_string_pretty(&meta).map_err(|e| format!("serialize vault metadata: {e}"))?;
    fs::write(meta_path(vault_dir), payload).map_err(|e| format!("write vault metadata: {e}"))?;
    Ok(salt.to_vec())
}

fn load_existing_salt(vault_dir: &Path) -> Result<Vec<u8>, String> {
    let raw = fs::read_to_string(meta_path(vault_dir))
        .map_err(|_| "vault is not initialized; initialize it first".to_string())?;
    let meta: VaultMeta =
        serde_json::from_str(&raw).map_err(|e| format!("parse vault metadata: {e}"))?;
    let salt = BASE64
        .decode(meta.salt_b64.as_bytes())
        .map_err(|e| format!("decode vault salt: {e}"))?;
    if salt.len() != SALT_LEN {
        return Err("vault metadata is invalid".to_string());
    }
    Ok(salt)
}

fn db_path(vault_dir: &Path) -> PathBuf {
    vault_dir.join(VAULT_DB_FILE)
}

fn meta_path(vault_dir: &Path) -> PathBuf {
    vault_dir.join(VAULT_META_FILE)
}

fn ensure_password(master_password: &str) -> Result<(), String> {
    if master_password.trim().is_empty() {
        return Err("master password cannot be empty".to_string());
    }
    Ok(())
}

fn ensure_non_empty(field: &str, value: &str) -> Result<(), String> {
    if value.trim().is_empty() {
        return Err(format!("{field} cannot be empty"));
    }
    Ok(())
}

#[cfg(test)]
mod tests {
    use super::*;
    use std::path::Path;
    use std::time::{SystemTime, UNIX_EPOCH};

    fn make_temp_vault_dir() -> PathBuf {
        let nonce = SystemTime::now()
            .duration_since(UNIX_EPOCH)
            .expect("clock should be monotonic enough for tests")
            .as_nanos();
        let dir = std::env::temp_dir().join(format!("trust_systems_vault_test_{nonce}"));
        fs::create_dir_all(&dir).expect("create temp vault dir");
        dir
    }

    #[test]
    fn vault_roundtrip_write_then_read() {
        let dir = make_temp_vault_dir();
        let password = "test-password-123";

        init_vault(&dir, password).expect("initialize vault");
        let id = save_draft(
            &dir,
            password,
            "Artifact One",
            "Curator",
            None,
            None,
            None,
            None,
            None,
            None,
            None,
            None,
            Some("abc123"),
            "draft",
            None,
            Some("/tmp/artwork.jpg"),
        )
        .expect("save draft");
        assert!(id > 0);

        let drafts = list_drafts(&dir, password).expect("list drafts");
        assert_eq!(drafts.len(), 1);
        assert_eq!(drafts[0].artifact_title, "Artifact One");
        assert_eq!(drafts[0].author, "Curator");
        assert_eq!(drafts[0].physical_hash.as_deref(), Some("abc123"));
        assert!(drafts[0].perceptual_hash.is_none());
        assert_eq!(drafts[0].draft_status, "draft");
        assert!(!drafts[0].is_certified);
        assert!(drafts[0].c2pa_manifest_id.is_none());
        assert_eq!(drafts[0].asset_path.as_deref(), Some("/tmp/artwork.jpg"));

        let _ = fs::remove_dir_all(&dir);
    }

    #[test]
    fn tag_persistence_upsert_and_query() {
        let dir = make_temp_vault_dir();
        let password = "test-password";

        init_vault(&dir, password).expect("initialize vault");

        let tag = upsert_tag_from_preflight(
            &dir,
            password,
            "04AABBCCDDEEFF00",
            "factory_default_likely",
            Some("ACR1252U"),
            Some("NT4H2421Gx"),
            Some("0x02"),
        )
        .expect("upsert tag");

        assert_eq!(tag.uid, "04AABBCCDDEEFF00");
        assert_eq!(tag.status, "plain_ndef");
        assert_eq!(tag.tag_family.as_deref(), Some("NT4H2421Gx"));
        assert_eq!(tag.chip_subtype.as_deref(), Some("0x02"));
        assert!(tag.identity_id.is_none());
        assert!(tag.artwork_id.is_none());

        let list = list_tags(&dir, password).expect("list tags");
        assert_eq!(list.len(), 1);
        assert_eq!(list[0].uid, tag.uid);

        let found = get_tag_by_uid(&dir, password, "04AABBCCDDEEFF00").expect("get by uid");
        assert!(found.is_some());
        assert_eq!(found.unwrap().status, "plain_ndef");

        let none = get_tag_by_uid(&dir, password, "04UNKNOWN").expect("get unknown");
        assert!(none.is_none());

        // Upsert again (update)
        let updated = upsert_tag_from_preflight(
            &dir,
            password,
            "04AABBCCDDEEFF00",
            "sdm_enabled",
            Some("ACR1252U"),
            Some("NT4H2421Gx"),
            None,
        )
        .expect("upsert again");

        assert_eq!(updated.id, tag.id);
        assert_eq!(updated.status, "personalized_sdm");

        let _ = fs::remove_dir_all(&dir);
    }

    #[test]
    fn delete_draft_hides_row_and_blocks_mutations() {
        let dir = make_temp_vault_dir();
        let password = "test-password";

        init_vault(&dir, password).expect("initialize vault");
        let draft_id = save_draft(
            &dir,
            password,
            "Artifact Soft Delete",
            "Curator",
            None,
            None,
            None,
            None,
            None,
            None,
            None,
            None,
            Some("hash-before-delete"),
            "draft",
            None,
            Some("/tmp/soft-delete.jpg"),
        )
        .expect("save draft");

        delete_draft(&dir, password, draft_id).expect("delete draft");

        let drafts = list_drafts(&dir, password).expect("list drafts");
        assert!(drafts.is_empty());
        let draft = get_draft_by_id(&dir, password, draft_id).expect("get draft");
        assert!(draft.is_none());

        let certify_err = mark_draft_certified(
            &dir,
            password,
            draft_id,
            Some("manifest-after-delete"),
            None,
            None,
        )
        .expect_err("deleted draft should not be certifiable");
        assert!(certify_err.contains("not found or deleted"));

        let update_err = update_draft_physical_hash(
            &dir,
            password,
            draft_id,
            Some("nfc:uid:04DEADBEEF"),
        )
        .expect_err("deleted draft should not accept physical_hash updates");
        assert!(update_err.contains("not found or deleted"));

        let _ = fs::remove_dir_all(&dir);
    }

    #[test]
    fn forget_tag_hides_row_and_rescan_restores_same_record() {
        let dir = make_temp_vault_dir();
        let password = "test-password";

        init_vault(&dir, password).expect("initialize vault");
        let original = upsert_tag_from_preflight(
            &dir,
            password,
            "04AABBCCDDEEFF00",
            "factory_default_likely",
            Some("ACR1252U"),
            Some("NT4H2421Gx"),
            Some("0x02"),
        )
        .expect("upsert tag");

        forget_tag(&dir, password, original.id).expect("forget tag");
        let list = list_tags(&dir, password).expect("list tags");
        assert!(list.is_empty());
        let hidden = get_tag_by_uid(&dir, password, "04AABBCCDDEEFF00").expect("get forgotten tag");
        assert!(hidden.is_none());

        let restored = upsert_tag_from_preflight(
            &dir,
            password,
            "04AABBCCDDEEFF00",
            "sdm_enabled",
            Some("ACR1252U"),
            Some("NT4H2421Gx"),
            None,
        )
        .expect("rescan should restore forgotten tag");
        assert_eq!(restored.id, original.id);
        assert_eq!(restored.status, "personalized_sdm");

        let list_after = list_tags(&dir, password).expect("list tags after restore");
        assert_eq!(list_after.len(), 1);
        assert_eq!(list_after[0].id, original.id);

        let _ = fs::remove_dir_all(&dir);
    }

    #[test]
    fn restore_draft_by_manifest_id_relinks_soft_deleted() {
        let dir = make_temp_vault_dir();
        let password = "test-password";

        init_vault(&dir, password).expect("initialize vault");
        let draft_id = save_draft(
            &dir,
            password,
            "Sealed Artwork",
            "Curator",
            None,
            None,
            None,
            None,
            None,
            None,
            None,
            None,
            None,
            "draft",
            None,
            Some("/tmp/sealed.jpg"),
        )
        .expect("save draft");
        mark_draft_certified(&dir, password, draft_id, Some("urn:uuid:abc-123-manifest"), None, None)
            .expect("mark certified");
        delete_draft(&dir, password, draft_id).expect("delete draft");

        let drafts = list_drafts(&dir, password).expect("list drafts");
        assert!(drafts.is_empty());

        let found = find_draft_by_manifest_id(&dir, password, "urn:uuid:abc-123-manifest")
            .expect("find by manifest id");
        let (id, was_deleted) = found.expect("should find soft-deleted draft");
        assert_eq!(id, draft_id);
        assert!(was_deleted);

        restore_draft(&dir, password, draft_id, "/new/path/sealed.jpg").expect("restore draft");

        let drafts = list_drafts(&dir, password).expect("list drafts");
        assert_eq!(drafts.len(), 1);
        assert_eq!(drafts[0].id, draft_id);
        assert_eq!(drafts[0].asset_path.as_deref(), Some("/new/path/sealed.jpg"));
        assert!(drafts[0].is_certified);
        assert_eq!(drafts[0].c2pa_manifest_id.as_deref(), Some("urn:uuid:abc-123-manifest"));

        let _ = fs::remove_dir_all(&dir);
    }

    #[test]
    fn find_draft_by_manifest_id_returns_none_for_unknown() {
        let dir = make_temp_vault_dir();
        let password = "test-password";
        init_vault(&dir, password).expect("initialize vault");
        let found = find_draft_by_manifest_id(&dir, password, "urn:uuid:nonexistent")
            .expect("find by manifest id");
        assert!(found.is_none());
        let _ = fs::remove_dir_all(&dir);
    }

    #[test]
    fn find_draft_by_manifest_id_prefers_active_when_duplicates_exist() {
        let dir = make_temp_vault_dir();
        let password = "test-password";
        init_vault(&dir, password).expect("initialize vault");

        let old_id = save_draft(
            &dir,
            password,
            "Sealed Old",
            "Curator",
            None,
            None,
            None,
            None,
            None,
            None,
            None,
            None,
            None,
            "draft",
            None,
            Some("/tmp/old.jpg"),
        )
        .expect("save old draft");
        mark_draft_certified(&dir, password, old_id, Some("manifest-dup"), None, None)
            .expect("certify old draft");
        delete_draft(&dir, password, old_id).expect("soft-delete old draft");

        let active_id = save_draft(
            &dir,
            password,
            "Sealed Active",
            "Curator",
            None,
            None,
            None,
            None,
            None,
            None,
            None,
            None,
            None,
            "draft",
            None,
            Some("/tmp/active.jpg"),
        )
        .expect("save active draft");
        mark_draft_certified(&dir, password, active_id, Some("manifest-dup"), None, None)
            .expect("certify active draft");

        let found = find_draft_by_manifest_id(&dir, password, "manifest-dup")
            .expect("find by manifest id");
        let (id, was_deleted) = found.expect("should find draft");
        assert_eq!(id, active_id);
        assert!(!was_deleted);

        let _ = fs::remove_dir_all(&dir);
    }

    #[test]
    fn mark_draft_certified_rejects_duplicate_active_manifest_id() {
        let dir = make_temp_vault_dir();
        let password = "test-password";
        init_vault(&dir, password).expect("initialize vault");

        let first_id = save_draft(
            &dir,
            password,
            "First",
            "Curator",
            None,
            None,
            None,
            None,
            None,
            None,
            None,
            None,
            None,
            "draft",
            None,
            Some("/tmp/first.jpg"),
        )
        .expect("save first draft");
        mark_draft_certified(&dir, password, first_id, Some("manifest-unique"), None, None)
            .expect("certify first");

        let second_id = save_draft(
            &dir,
            password,
            "Second",
            "Curator",
            None,
            None,
            None,
            None,
            None,
            None,
            None,
            None,
            None,
            "draft",
            None,
            Some("/tmp/second.jpg"),
        )
        .expect("save second draft");

        let err = mark_draft_certified(&dir, password, second_id, Some("manifest-unique"), None, None)
            .expect_err("duplicate active manifest should fail");
        assert!(
            err.contains("UNIQUE constraint failed") || err.contains("idx_drafts_manifest_active_unique"),
            "unexpected error: {err}"
        );

        let _ = fs::remove_dir_all(&dir);
    }

    #[test]
    fn link_tag_to_soft_deleted_artwork_is_rejected() {
        let dir = make_temp_vault_dir();
        let password = "test-password";

        init_vault(&dir, password).expect("initialize vault");
        let identity_id = generate_sovereign_identity(&dir, password, "Jane Doe", "Trust Labs", "US")
            .expect("generate identity");
        let tag = update_tag_enrolled_by_uid(
            &dir,
            password,
            "04AABBCCDDEEFF00",
            identity_id,
            Some(1),
            Some("https://example.com/tap"),
        )
        .expect("enroll tag");
        let draft_id = save_draft(
            &dir,
            password,
            "Deleted Artwork",
            "Curator",
            None,
            None,
            None,
            None,
            None,
            None,
            None,
            None,
            Some("hash-artwork"),
            "draft",
            None,
            Some("/tmp/deleted-artwork.jpg"),
        )
        .expect("save draft");
        delete_draft(&dir, password, draft_id).expect("delete draft");

        let err = link_tag_to_artwork(&dir, password, tag.id, draft_id, identity_id)
            .expect_err("should reject linking to soft-deleted draft");
        assert!(err.contains("artwork not found"));

        let unchanged = get_tag_by_id(&dir, password, tag.id).expect("reload tag");
        assert!(unchanged.artwork_id.is_none());

        let _ = fs::remove_dir_all(&dir);
    }

    #[test]
    fn mark_tag_reset_clears_control_metadata() {
        let dir = make_temp_vault_dir();
        let password = "test-password";

        init_vault(&dir, password).expect("initialize vault");
        let identity_id = generate_sovereign_identity(&dir, password, "Jane Doe", "Trust Labs", "US")
            .expect("generate identity");
        update_tag_enrolled_by_uid(
            &dir,
            password,
            "04AABBCCDDEEFF00",
            identity_id,
            Some(1),
            Some("https://example.com/tap"),
        )
        .expect("enroll tag");

        let reset = mark_tag_reset_by_uid(&dir, password, "04AABBCCDDEEFF00", identity_id)
            .expect("reset tag record");

        assert_eq!(reset.status, "factory_default_likely");
        assert!(reset.identity_id.is_none());
        assert!(reset.artwork_id.is_none());
        assert!(reset.key_version.is_none());
        assert!(reset.enrolled_at.is_none());

        let _ = fs::remove_dir_all(&dir);
    }

    #[test]
    fn vault_rejects_wrong_password() {
        let dir = make_temp_vault_dir();

        init_vault(&dir, "correct-password").expect("initialize vault");
        let err = list_drafts(&dir, "wrong-password").unwrap_err();
        assert!(err.contains("invalid vault password"));

        let _ = fs::remove_dir_all(&dir);
    }

    #[test]
    fn change_vault_password_rekeys_and_preserves_data() {
        let dir = make_temp_vault_dir();
        let old_pw = "old-password";
        let new_pw = "new-password";

        init_vault(&dir, old_pw).expect("initialize vault");
        let id = save_draft(
            &dir,
            old_pw,
            "Test Artifact",
            "Test Author",
            None,
            None,
            None,
            None,
            None,
            None,
            None,
            None,
            Some("hash-xyz"),
            "draft",
            None,
            None,
        )
        .expect("save draft");

        change_vault_password(&dir, old_pw, new_pw).expect("change password");

        let err = list_drafts(&dir, old_pw).unwrap_err();
        assert!(err.contains("invalid vault password"));

        let drafts = list_drafts(&dir, new_pw).expect("list drafts with new password");
        assert_eq!(drafts.len(), 1);
        assert_eq!(drafts[0].id, id);
        assert_eq!(drafts[0].artifact_title, "Test Artifact");
        assert_eq!(drafts[0].author, "Test Author");

        let _ = fs::remove_dir_all(&dir);
    }

    #[test]
    fn import_key_and_load_active_key() {
        let dir = make_temp_vault_dir();
        let password = "correct-password";
        init_vault(&dir, password).expect("initialize vault");

        let key_path = Path::new(env!("CARGO_MANIFEST_DIR"))
            .join("..")
            .join("assets")
            .join("test_data")
            .join("es256_private.key");
        if !key_path.exists() {
            let _ = fs::remove_dir_all(&dir);
            return;
        }

        import_private_key_from_file(&dir, password, key_path.to_str().unwrap())
            .expect("import key");
        let pem = get_active_private_key_pem(&dir, password).expect("load key");
        let pem_text = String::from_utf8(pem).expect("pem bytes should be utf8");
        assert!(pem_text.contains("PRIVATE KEY"));

        let _ = fs::remove_dir_all(&dir);
    }

    #[test]
    fn mark_draft_certified_sets_manifest_id() {
        let dir = make_temp_vault_dir();
        let password = "correct-password";
        init_vault(&dir, password).expect("initialize vault");
        let id = save_draft(
            &dir,
            password,
            "Artifact",
            "Curator",
            None,
            None,
            None,
            None,
            None,
            None,
            None,
            None,
            Some("hash-1"),
            "Draft",
            None,
            None,
        )
            .expect("save draft");
        mark_draft_certified(&dir, password, id, Some("manifest-123"), None, None)
            .expect("mark certified");
        let drafts = list_drafts(&dir, password).expect("list drafts");
        assert_eq!(drafts[0].id, id);
        assert!(drafts[0].is_certified);
        assert_eq!(drafts[0].c2pa_manifest_id.as_deref(), Some("manifest-123"));

        let _ = fs::remove_dir_all(&dir);
    }

    #[test]
    fn migration_v2_preserves_existing_sprint2_data() {
        let dir = make_temp_vault_dir();
        let password = "correct-password";
        init_vault(&dir, password).expect("initialize vault");
        save_draft(
            &dir,
            password,
            "Artifact One",
            "Curator",
            None,
            None,
            None,
            None,
            None,
            None,
            None,
            None,
            Some("abc123"),
            "draft",
            None,
            None,
        )
            .expect("save sprint2 draft");

        // Build a simulated Sprint 2 schema/database state.
        let salt = load_existing_salt(&dir).expect("load salt");
        let key = derive_key(password, &salt).expect("derive key");
        let conn = open_encrypted_db(&db_path(&dir), &key).expect("open encrypted db");
        conn.execute_batch(
            "PRAGMA user_version = 1;
             DROP TRIGGER IF EXISTS drafts_set_updated_at;
             DROP TABLE IF EXISTS secure_keys;
             CREATE TABLE drafts_legacy (
                 id INTEGER PRIMARY KEY AUTOINCREMENT,
                 artifact_title TEXT NOT NULL,
                 author TEXT NOT NULL,
                 physical_hash TEXT,
                 draft_status TEXT NOT NULL,
                 created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
                 updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
             );
             INSERT INTO drafts_legacy (artifact_title, author, physical_hash, draft_status)
                 SELECT artifact_title, author, physical_hash, draft_status FROM drafts;
             DROP TABLE drafts;
             ALTER TABLE drafts_legacy RENAME TO drafts;",
        )
        .expect("prepare legacy schema");
        drop(conn);

        // Trigger migration path and ensure existing row still exists with v2 defaults.
        let drafts = list_drafts(&dir, password).expect("list drafts after migration");
        assert_eq!(drafts.len(), 1);
        assert_eq!(drafts[0].artifact_title, "Artifact One");
        assert_eq!(drafts[0].author, "Curator");
        assert_eq!(drafts[0].physical_hash.as_deref(), Some("abc123"));
        assert!(drafts[0].perceptual_hash.is_none());
        assert!(!drafts[0].is_certified);
        assert!(drafts[0].c2pa_manifest_id.is_none());

        let _ = fs::remove_dir_all(&dir);
    }

    #[test]
    fn generate_identity_and_list_status() {
        let dir = make_temp_vault_dir();
        let password = "correct-password";
        init_vault(&dir, password).expect("initialize vault");

        let id = generate_sovereign_identity(&dir, password, "Jane Doe", "Trust Labs", "US")
            .expect("generate identity");
        assert!(id > 0);

        let identities = list_identities(&dir, password).expect("list identities");
        assert_eq!(identities.len(), 1);
        assert_eq!(identities[0].id, id);
        assert!(identities[0].is_active);
        assert!(!identities[0].is_production);
        assert_eq!(identities[0].common_name.as_deref(), Some("Jane Doe"));
        assert!(identities[0].has_certificate_chain);

        let _ = fs::remove_dir_all(&dir);
    }

    #[test]
    fn csr_generation_creates_certificate_request_file() {
        let dir = make_temp_vault_dir();
        let password = "correct-password";
        init_vault(&dir, password).expect("initialize vault");
        let id = generate_sovereign_identity(&dir, password, "Jane Doe", "Trust Labs", "US")
            .expect("generate identity");
        let csr_path = std::env::temp_dir().join(format!(
            "trust_systems_test_{}.csr",
            std::process::id()
        ));
        let _ = fs::remove_file(&csr_path);

        generate_csr_to_file(
            &dir,
            password,
            id,
            "Jane Doe",
            "Trust Labs",
            "US",
            csr_path.to_str().unwrap(),
        )
        .expect("generate csr");

        let csr = fs::read_to_string(&csr_path).expect("read csr");
        assert!(csr.contains("BEGIN CERTIFICATE REQUEST"));

        let _ = fs::remove_file(&csr_path);
        let _ = fs::remove_dir_all(&dir);
    }

    #[test]
    fn import_ca_certificate_hard_fails_for_key_mismatch() {
        let dir = make_temp_vault_dir();
        let password = "correct-password";
        init_vault(&dir, password).expect("initialize vault");
        let id1 = generate_sovereign_identity(&dir, password, "Jane One", "Trust Labs", "US")
            .expect("generate identity 1");
        let id2 = generate_sovereign_identity(&dir, password, "Jane Two", "Trust Labs", "US")
            .expect("generate identity 2");

        let identity2 = get_identity_by_id(&dir, password, id2).expect("load identity 2");
        let chain_path = std::env::temp_dir().join(format!(
            "trust_systems_test_chain_{}.pem",
            std::process::id()
        ));
        let _ = fs::remove_file(&chain_path);
        fs::write(&chain_path, identity2.certificate_chain_pem).expect("write chain file");

        let err = import_ca_certificate(&dir, password, id1, chain_path.to_str().unwrap())
            .unwrap_err();
        assert!(err.contains("Certificate does not match"));

        let _ = fs::remove_file(&chain_path);
        let _ = fs::remove_dir_all(&dir);
    }

    #[test]
    fn export_private_key_backup_writes_pkcs8_pem() {
        let dir = make_temp_vault_dir();
        let password = "correct-password";
        init_vault(&dir, password).expect("initialize vault");
        let id = generate_sovereign_identity(&dir, password, "Jane Doe", "Trust Labs", "US")
            .expect("generate identity");

        let out = std::env::temp_dir().join(format!(
            "trust_systems_key_backup_{}.pem",
            std::process::id()
        ));
        let _ = fs::remove_file(&out);

        export_private_key_backup(&dir, password, id, out.to_str().unwrap())
            .expect("export backup");
        let pem = fs::read_to_string(&out).expect("read backup");
        assert!(pem.starts_with("-----BEGIN PRIVATE KEY-----"));

        let _ = fs::remove_file(&out);
        let _ = fs::remove_dir_all(&dir);
    }

    #[test]
    fn import_contact_certificate_rejects_duplicate_certificate() {
        let dir = make_temp_vault_dir();
        let password = "correct-password";
        init_vault(&dir, password).expect("initialize vault");
        generate_sovereign_identity(&dir, password, "Jane Doe", "Trust Labs", "US")
            .expect("generate identity");

        let cert_path = std::env::temp_dir().join(format!(
            "trust_systems_contact_cert_{}.crt",
            std::process::id()
        ));
        let _ = fs::remove_file(&cert_path);
        export_public_certificate(&dir, password, cert_path.to_str().unwrap())
            .expect("export public certificate");

        import_contact_certificate(&dir, password, cert_path.to_str().unwrap())
            .expect("first import should succeed");
        let err = import_contact_certificate(&dir, password, cert_path.to_str().unwrap())
            .expect_err("duplicate import should fail");
        assert!(err.contains("UNIQUE constraint failed") || err.contains("idx_trusted_contacts_certificate_fingerprint"));

        let _ = fs::remove_file(&cert_path);
        let _ = fs::remove_dir_all(&dir);
    }

    #[test]
    fn import_contact_certificate_accepts_der_certificate_file() {
        let dir = make_temp_vault_dir();
        let password = "correct-password";
        init_vault(&dir, password).expect("initialize vault");
        generate_sovereign_identity(&dir, password, "Jane Doe", "Trust Labs", "US")
            .expect("generate identity");

        let pem_path = std::env::temp_dir().join(format!(
            "trust_systems_contact_cert_pem_{}.crt",
            std::process::id()
        ));
        let der_path = std::env::temp_dir().join(format!(
            "trust_systems_contact_cert_der_{}.crt",
            std::process::id()
        ));
        let _ = fs::remove_file(&pem_path);
        let _ = fs::remove_file(&der_path);
        export_public_certificate(&dir, password, pem_path.to_str().unwrap())
            .expect("export public certificate");

        let pem_bytes = fs::read(&pem_path).expect("read exported cert pem");
        let pem_text = std::str::from_utf8(&pem_bytes).expect("pem utf8");
        let mut reader = BufReader::new(pem_text.as_bytes());
        let chain = certs(&mut reader)
            .collect::<Result<Vec<_>, _>>()
            .expect("parse pem cert");
        let leaf_der = chain.first().expect("leaf cert").as_ref();
        fs::write(&der_path, leaf_der).expect("write der cert");

        import_contact_certificate(&dir, password, der_path.to_str().unwrap())
            .expect("DER import should succeed");
        let contacts = list_trusted_contacts(&dir, password).expect("list contacts");
        assert_eq!(contacts.len(), 1);

        let _ = fs::remove_file(&pem_path);
        let _ = fs::remove_file(&der_path);
        let _ = fs::remove_dir_all(&dir);
    }
}
