// cloud/db.jsx — Couche d'accès aux données (Supabase)
// Toutes les opérations BD passent par ici.

const RaycastDB = (() => {
  function sb() {
    const c = window.RAYCAST_SUPABASE;
    if (!c) throw new Error("Supabase non configuré");
    return c;
  }

  // ─────────────────────────────────────────────
  // Profils
  // ─────────────────────────────────────────────
  async function listProfiles() {
    const { data, error } = await sb()
      .from("profiles")
      .select("id,name,role,initials")
      .order("name");
    if (error) throw error;
    return data || [];
  }

  // ─────────────────────────────────────────────
  // Clients (répertoire central)
  // ─────────────────────────────────────────────
  async function listClients({ search = "", limit = 200 } = {}) {
    let q = sb().from("clients").select("*").order("updated_at", { ascending: false });
    if (search) {
      const s = `%${search.toLowerCase()}%`;
      q = q.or(`name.ilike.${s},address_street.ilike.${s},address_city.ilike.${s},phone.ilike.${s},email.ilike.${s}`);
    }
    q = q.limit(limit);
    const { data, error } = await q;
    if (error) throw error;
    return data || [];
  }

  async function getClient(id) {
    const { data, error } = await sb().from("clients").select("*").eq("id", id).maybeSingle();
    if (error) throw error;
    return data;
  }

  /**
   * Upsert d'un client à partir du bloc `client` d'une soumission.
   * - Si client.client_id existe → met à jour
   * - Sinon → cherche un match par (nom, code postal) ou (nom, ville)
   * - Sinon → crée
   * Retourne l'id du client.
   */
  async function upsertClientFromState(c, userId) {
    if (!c || !c.name) return null;
    const payload = {
      name:             c.name,
      phone:            c.phone || null,
      email:            c.email || null,
      address_civic:    c.address_civic || null,
      address_apt:      c.address_apt || null,
      address_street:   c.address_street || null,
      address_city:     c.address_city || null,
      address_province: c.address_province || "QC",
      address_postal:   c.address_postal || null,
      region_id:        c.region_id || null,
      source:           c.source || null
    };

    // 1) Si on a un id direct
    if (c.client_id) {
      const { data, error } = await sb()
        .from("clients").update(payload).eq("id", c.client_id).select("id").maybeSingle();
      if (error) throw error;
      if (data) return data.id;
    }

    // 2) Match par (nom + code postal)
    if (c.address_postal) {
      const { data: m } = await sb()
        .from("clients")
        .select("id")
        .ilike("name", c.name)
        .eq("address_postal", c.address_postal)
        .limit(1);
      if (m && m.length) {
        await sb().from("clients").update(payload).eq("id", m[0].id);
        return m[0].id;
      }
    }

    // 3) Création
    const { data: ins, error: insErr } = await sb()
      .from("clients").insert({ ...payload, created_by: userId }).select("id").single();
    if (insErr) throw insErr;
    return ins.id;
  }

  // ─────────────────────────────────────────────
  // Numérotation atomique (2026-0001, 2026-0002…)
  // ─────────────────────────────────────────────
  async function nextSubmissionNumber(year) {
    const { data, error } = await sb().rpc("next_submission_number", { p_year: year });
    if (error) throw error;
    return data; // ex. "2026-0001"
  }

  // ─────────────────────────────────────────────
  // Soumissions
  // ─────────────────────────────────────────────

  /**
   * Liste enrichie (vue v_submissions) pour le répertoire.
   * filters : { search, status, creator_id, year, project_type }
   */
  async function listSubmissions(filters = {}) {
    let q = sb().from("v_submissions").select("*").order("created_at", { ascending: false });
    if (filters.search) {
      const s = `%${filters.search}%`;
      q = q.or(`number.ilike.${s},client_name.ilike.${s},client_city.ilike.${s}`);
    }
    if (filters.status)       q = q.eq("status", filters.status);
    if (filters.creator_id)   q = q.eq("created_by", filters.creator_id);
    if (filters.year)         q = q.eq("year", filters.year);
    if (filters.project_type) q = q.eq("project_type", filters.project_type);
    q = q.limit(500);
    const { data, error } = await q;
    if (error) throw error;
    return data || [];
  }

  async function getSubmission(id) {
    const { data, error } = await sb()
      .from("submissions").select("*").eq("id", id).maybeSingle();
    if (error) throw error;
    return data;
  }

  /**
   * Crée une nouvelle soumission en brouillon (pas de numéro tant que pas envoyée).
   * Retourne la ligne créée.
   */
  async function createSubmission({ state, calc, userId, clientId }) {
    const row = stateToRow({ state, calc, clientId, createdBy: userId });
    // V1.0.1 : attribuer un numéro dès la création (premier "Enregistrer").
    // L'attribution est atomique côté Postgres → pas de doublon possible.
    row.number = await nextSubmissionNumber(row.year);
    const { data, error } = await sb()
      .from("submissions").insert(row).select("*").single();
    if (error) throw error;
    return data;
  }

  /**
   * Sauvegarde une soumission existante.
   * - Si number === null et status === "envoye" → attribue un numéro
   */
  async function saveSubmission({ id, state, calc, userId, clientId, assignNumber = false, currentNumber = null }) {
    const row = stateToRow({ state, calc, clientId, createdBy: userId });
    // On ne réécrit pas created_by ni created_at lors d'un update
    delete row.created_by;

    if (assignNumber && !currentNumber) {
      row.number = await nextSubmissionNumber(row.year);
    }

    const { data, error } = await sb()
      .from("submissions").update(row).eq("id", id).select("*").single();
    if (error) throw error;
    return data;
  }

  async function setSubmissionStatus(id, status) {
    const { data, error } = await sb()
      .from("submissions").update({ status }).eq("id", id).select("number,status").single();
    if (error) throw error;
    return data;
  }

  async function deleteSubmission(id) {
    const { error } = await sb().from("submissions").delete().eq("id", id);
    if (error) throw error;
    return true;
  }

  /**
   * Construit la ligne `submissions` à partir de l'état React du configurateur.
   */
  function stateToRow({ state, calc, clientId, createdBy }) {
    const c = state.client || {};
    const s = state.solar || {};
    return {
      created_by:    createdBy,
      client_id:     clientId || null,
      year:          new Date().getFullYear(),
      project_type:  state.project_type || null,
      building_type: state.building_type || null,
      client_name:   c.name || null,
      client_city:   c.address_city || null,
      kw_total:      typeof s.kw === "number" ? s.kw : null,
      kwh_total:     typeof s.battery_kwh === "number" ? s.battery_kwh : (calc?.pack?.kwh || null),
      price_total:   calc?.costs?.total || calc?.total || null,
      data:          state
    };
  }

  return {
    listProfiles,
    listClients,
    getClient,
    upsertClientFromState,
    nextSubmissionNumber,
    listSubmissions,
    getSubmission,
    createSubmission,
    saveSubmission,
    setSubmissionStatus,
    deleteSubmission
  };
})();

window.RaycastDB = RaycastDB;