-- Migración inicial de la base osint.duckdb.
--
-- Tres categorías de datos:
--   1. Tablas maestras con referencia a notas (schema main): índice del vault
--      (notes) + entidades estructuradas (persons, organizations, domains,
--      cases, places). Cada una lleva note_path: el path relativo de la nota
--      dentro del vault de Obsidian.
--   2. Tablas maestras DAV (schema main): contacts y events importados del
--      servidor Xandikos (CardDAV/CalDAV) — fuente de verdad.
--   3. Tablas derivadas (schema derived): SOLO datos computados. Regla dura:
--      ninguna tabla de derived lleva columna que referencie notas. Se
--      reconstruyen completas en cada ingest, por eso esta migración solo crea
--      el schema, no las tablas.

-- Índice completo del vault: una fila por nota .md.
CREATE TABLE IF NOT EXISTS notes (
    note_path   TEXT PRIMARY KEY,
    slug        TEXT,
    tipo        TEXT,
    title       TEXT,
    mtime       TIMESTAMP,
    frontmatter JSON
);

-- Fichas de persona (personas/<slug>.md), esquema canónico de CONVENTIONS.md 3b.
CREATE TABLE IF NOT EXISTS persons (
    slug             TEXT PRIMARY KEY,
    note_path        TEXT,
    nombre           TEXT,
    aliases          JSON,
    sexo             TEXT,
    fecha_nacimiento TEXT,
    dni              TEXT,
    telefono         TEXT,
    email            TEXT,
    direccion        TEXT,
    pais             TEXT,
    contexto         TEXT,
    fuente           TEXT,
    dav_uid          TEXT,
    tags             JSON,
    updated_at       TIMESTAMP
);

-- Resto de entidades del vault: organizaciones, dominios, casos y lugares.
CREATE TABLE IF NOT EXISTS organizations (
    slug        TEXT PRIMARY KEY,
    note_path   TEXT,
    nombre      TEXT,
    tags        JSON,
    frontmatter JSON,
    updated_at  TIMESTAMP
);

CREATE TABLE IF NOT EXISTS domains (
    slug        TEXT PRIMARY KEY,
    note_path   TEXT,
    nombre      TEXT,
    tags        JSON,
    frontmatter JSON,
    updated_at  TIMESTAMP
);

CREATE TABLE IF NOT EXISTS cases (
    slug        TEXT PRIMARY KEY,
    note_path   TEXT,
    nombre      TEXT,
    tags        JSON,
    frontmatter JSON,
    updated_at  TIMESTAMP
);

CREATE TABLE IF NOT EXISTS places (
    slug        TEXT PRIMARY KEY,
    note_path   TEXT,
    nombre      TEXT,
    tags        JSON,
    frontmatter JSON,
    updated_at  TIMESTAMP
);

-- Maestras DAV: contactos del addressbook CardDAV. note_path enlaza el
-- contacto con su ficha del vault cuando el matching lo consigue.
CREATE TABLE IF NOT EXISTS contacts (
    uid        TEXT PRIMARY KEY,
    collection TEXT,
    etag       TEXT,
    fn         TEXT,
    tels       JSON,
    emails     JSON,
    raw        TEXT,
    note_path  TEXT,
    updated_at TIMESTAMP
);

-- Maestras DAV: eventos de las colecciones CalDAV.
CREATE TABLE IF NOT EXISTS events (
    uid        TEXT PRIMARY KEY,
    calendar   TEXT,
    etag       TEXT,
    dtstart    TEXT,
    dtend      TEXT,
    all_day    BOOLEAN,
    summary    TEXT,
    location   TEXT,
    rrule      TEXT,
    raw        TEXT,
    updated_at TIMESTAMP
);

-- Schema para las tablas derivadas (solo datos computados, sin note_path).
CREATE SCHEMA IF NOT EXISTS derived;