-- Esquema de un crm.db vacío (clonado de la estructura del CRM Nubip).
-- Lo usa crear_clinica_nueva.php para crear clínicas nuevas sin datos.

CREATE TABLE auditoria (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        usuario_id INTEGER,
        usuario_nombre TEXT,
        accion TEXT NOT NULL,
        entidad_tipo TEXT,
        entidad_id TEXT,
        descripcion TEXT,
        datos TEXT,
        ip TEXT,
        fecha DATETIME DEFAULT CURRENT_TIMESTAMP
    );

CREATE TABLE bono_consumos (
                id                INTEGER PRIMARY KEY AUTOINCREMENT,
                bono_id           INTEGER NOT NULL,
                fecha             TEXT NOT NULL,
                usuario_id        INTEGER,
                reserva_event_id  TEXT,
                notas             TEXT,
                created_at        TEXT NOT NULL DEFAULT (datetime('now')), hora TEXT, terapia TEXT, terapeuta TEXT,
                FOREIGN KEY (bono_id)    REFERENCES bonos(id)    ON DELETE CASCADE,
                FOREIGN KEY (usuario_id) REFERENCES usuarios(id) ON DELETE SET NULL
            );

CREATE TABLE bonos (
                id                   INTEGER PRIMARY KEY AUTOINCREMENT,
                paciente_id          INTEGER NOT NULL,
                terapia              TEXT NOT NULL,
                nombre               TEXT,
                sesiones_totales     INTEGER NOT NULL,
                sesiones_consumidas  INTEGER NOT NULL DEFAULT 0,
                precio               REAL,
                fecha_compra         TEXT NOT NULL,
                fecha_caducidad      TEXT,
                estado               TEXT NOT NULL DEFAULT 'activo',
                notas                TEXT,
                created_by           INTEGER,
                created_at           TEXT NOT NULL DEFAULT (datetime('now')),
                updated_at           TEXT NOT NULL DEFAULT (datetime('now')), plantilla_id INTEGER REFERENCES bonos_plantillas(id) ON DELETE SET NULL,
                FOREIGN KEY (paciente_id) REFERENCES pacientes(id) ON DELETE CASCADE,
                FOREIGN KEY (created_by)  REFERENCES usuarios(id)  ON DELETE SET NULL
            );

CREATE TABLE bonos_plantillas (
                id                INTEGER PRIMARY KEY AUTOINCREMENT,
                nombre            TEXT NOT NULL,
                terapia           TEXT NOT NULL,
                sesiones_totales  INTEGER NOT NULL,
                precio            REAL,
                duracion_dias     INTEGER,
                descripcion       TEXT,
                activa            INTEGER NOT NULL DEFAULT 1,
                orden             INTEGER NOT NULL DEFAULT 0,
                created_by        INTEGER,
                created_at        TEXT NOT NULL DEFAULT (datetime('now')),
                updated_at        TEXT NOT NULL DEFAULT (datetime('now')),
                FOREIGN KEY (created_by) REFERENCES usuarios(id) ON DELETE SET NULL
            );

CREATE TABLE etiquetas (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            nombre TEXT UNIQUE NOT NULL,
            color TEXT
        );

CREATE TABLE ficheros (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            paciente_id INTEGER NOT NULL,
            interaccion_id INTEGER,
            nombre_original TEXT NOT NULL,
            nombre_almacenado TEXT NOT NULL,
            categoria TEXT,
            mime_type TEXT,
            tamano INTEGER,
            descripcion TEXT,
            subido_por INTEGER,
            fecha_subida TEXT NOT NULL DEFAULT (datetime('now')),
            FOREIGN KEY (paciente_id)    REFERENCES pacientes(id)    ON DELETE CASCADE,
            FOREIGN KEY (interaccion_id) REFERENCES interacciones(id) ON DELETE SET NULL,
            FOREIGN KEY (subido_por)     REFERENCES usuarios(id)      ON DELETE SET NULL
        );

CREATE TABLE interacciones (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            paciente_id INTEGER NOT NULL,
            tipo TEXT NOT NULL,
            direccion TEXT,
            titulo TEXT,
            contenido TEXT,
            duracion INTEGER,
            autor_id INTEGER,
            fecha TEXT NOT NULL,
            fecha_creacion TEXT NOT NULL DEFAULT (datetime('now')),
            FOREIGN KEY (paciente_id) REFERENCES pacientes(id) ON DELETE CASCADE,
            FOREIGN KEY (autor_id)    REFERENCES usuarios(id)  ON DELETE SET NULL
        );

CREATE TABLE paciente_etiquetas (
            paciente_id INTEGER NOT NULL,
            etiqueta_id INTEGER NOT NULL,
            PRIMARY KEY (paciente_id, etiqueta_id),
            FOREIGN KEY (paciente_id) REFERENCES pacientes(id) ON DELETE CASCADE,
            FOREIGN KEY (etiqueta_id) REFERENCES etiquetas(id) ON DELETE CASCADE
        );

CREATE TABLE pacientes (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            id_externo TEXT UNIQUE,
            nombre_completo TEXT NOT NULL,
            dni TEXT,
            fecha_nacimiento TEXT,
            sexo TEXT,
            profesion TEXT,
            telefono TEXT,
            telefono_fijo TEXT,
            email TEXT,
            direccion TEXT,
            codigo_postal TEXT,
            localidad TEXT,
            mutua TEXT,
            poliza TEXT,
            medico_derivador TEXT,
            origen TEXT,
            alergias TEXT,
            antecedentes TEXT,
            notas_internas TEXT,
            estado TEXT NOT NULL DEFAULT 'activo',
            fecha_alta TEXT NOT NULL DEFAULT (datetime('now')),
            fecha_modificacion TEXT NOT NULL DEFAULT (datetime('now')),
            fecha_sync TEXT
        , nombre TEXT, apellido1 TEXT, apellido2 TEXT);

CREATE TABLE usuarios (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            nombre TEXT NOT NULL,
            email TEXT UNIQUE,
            password_hash TEXT,
            rol TEXT NOT NULL DEFAULT 'usuario',
            activo INTEGER NOT NULL DEFAULT 1,
            fecha_alta TEXT NOT NULL DEFAULT (datetime('now'))
        , inicial TEXT, intentos_fallidos INTEGER DEFAULT 0, bloqueado_hasta DATETIME, ultimo_login DATETIME, password_changed_at DATETIME, fecha_modificacion DATETIME);

CREATE INDEX idx_aud_accion   ON auditoria(accion);

CREATE INDEX idx_aud_entidad  ON auditoria(entidad_tipo, entidad_id);

CREATE INDEX idx_aud_fecha    ON auditoria(fecha);

CREATE INDEX idx_aud_usuario  ON auditoria(usuario_id);

CREATE INDEX idx_bono_consumos_bono  ON bono_consumos(bono_id);

CREATE INDEX idx_bono_consumos_fecha ON bono_consumos(fecha);

CREATE INDEX idx_bonos_estado   ON bonos(estado);

CREATE INDEX idx_bonos_paciente ON bonos(paciente_id);

CREATE INDEX idx_bonos_plantilla ON bonos(plantilla_id);

CREATE INDEX idx_bonos_plantillas_activa ON bonos_plantillas(activa);

CREATE INDEX idx_bonos_plantillas_orden  ON bonos_plantillas(orden);

CREATE INDEX idx_fic_paciente   ON ficheros(paciente_id);

CREATE INDEX idx_int_fecha      ON interacciones(fecha);

CREATE INDEX idx_int_paciente   ON interacciones(paciente_id);

CREATE INDEX idx_pac_apellido1 ON pacientes(apellido1);

CREATE INDEX idx_pac_estado     ON pacientes(estado);

CREATE INDEX idx_pac_idext      ON pacientes(id_externo);

CREATE INDEX idx_pac_nombre     ON pacientes(nombre_completo);

CREATE INDEX idx_pac_telefono   ON pacientes(telefono);