-- Docmaster: Schema inicial
-- Tabla principal de documentos
CREATE TABLE IF NOT EXISTS documents (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    page_id TEXT NOT NULL UNIQUE,
    namespace TEXT NOT NULL,
    title TEXT,
    code TEXT,
    version INTEGER DEFAULT 1,
    validity_date TEXT,
    area TEXT,
    last_editor TEXT,
    is_active INTEGER DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Registro maestro de formatos (compartidos entre documentos)
CREATE TABLE IF NOT EXISTS formats (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    media_id TEXT NOT NULL UNIQUE,
    display_name TEXT,
    code TEXT,
    version INTEGER DEFAULT 1,
    validity_date TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Relacion documento <-> formato (muchos a muchos)
CREATE TABLE IF NOT EXISTS document_formats (
    document_id INTEGER NOT NULL,
    format_id INTEGER NOT NULL,
    section_name TEXT DEFAULT 'ANEXOS Y REGISTROS',
    PRIMARY KEY (document_id, format_id),
    FOREIGN KEY (document_id) REFERENCES documents(id) ON DELETE CASCADE,
    FOREIGN KEY (format_id) REFERENCES formats(id) ON DELETE CASCADE
);

-- Historial de versiones
CREATE TABLE IF NOT EXISTS version_history (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    document_id INTEGER NOT NULL,
    old_version INTEGER,
    new_version INTEGER,
    changed_by TEXT,
    changed_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (document_id) REFERENCES documents(id) ON DELETE CASCADE
);

-- Indices
CREATE INDEX IF NOT EXISTS idx_documents_namespace ON documents(namespace);
CREATE INDEX IF NOT EXISTS idx_documents_code ON documents(code);
CREATE INDEX IF NOT EXISTS idx_formats_media_id ON formats(media_id);
CREATE INDEX IF NOT EXISTS idx_version_history_doc ON version_history(document_id);
