/* ============================================================
   MiDosis
   Sistema personal de control de medicamentos
   MySQL / MariaDB
   ============================================================ */

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "-05:00";

START TRANSACTION;


/* ============================================================
   1. CONFIGURACIÓN GENERAL
   ============================================================ */

CREATE TABLE IF NOT EXISTS configuracion (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,

    nombre_persona VARCHAR(100) NOT NULL DEFAULT 'Carolina',

    mensaje_bienvenida VARCHAR(255)
        DEFAULT 'Tu medicación está bajo control.',

    zona_horaria VARCHAR(100)
        NOT NULL DEFAULT 'America/Bogota',

    hora_inicio_dia TIME
        NOT NULL DEFAULT '05:00:00',

    hora_fin_dia TIME
        NOT NULL DEFAULT '23:59:59',

    racha_activa TINYINT(1)
        NOT NULL DEFAULT 1,

    recordatorios_activos TINYINT(1)
        NOT NULL DEFAULT 1,

    sonido_activo TINYINT(1)
        NOT NULL DEFAULT 1,

    vibracion_activa TINYINT(1)
        NOT NULL DEFAULT 1,

    tema VARCHAR(20)
        NOT NULL DEFAULT 'claro',

    color_principal VARCHAR(20)
        NOT NULL DEFAULT '#7C3AED',

    creado_en DATETIME
        NOT NULL DEFAULT CURRENT_TIMESTAMP,

    actualizado_en DATETIME
        NOT NULL DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,

    PRIMARY KEY (id)

) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;


/* ============================================================
   CONFIGURACIÓN INICIAL
   ============================================================ */

INSERT INTO configuracion (
    id,
    nombre_persona,
    mensaje_bienvenida
)

SELECT
    1,
    'Carolina',
    'Tu medicación está bajo control.'

WHERE NOT EXISTS (
    SELECT 1
    FROM configuracion
    WHERE id = 1
);


/* ============================================================
   2. MEDICAMENTOS
   ============================================================ */

CREATE TABLE IF NOT EXISTS medicamentos (

    id INT UNSIGNED NOT NULL AUTO_INCREMENT,

    nombre VARCHAR(150) NOT NULL,

    nombre_generico VARCHAR(150)
        DEFAULT NULL,

    presentacion VARCHAR(100)
        DEFAULT NULL,

    cantidad DECIMAL(10,2)
        NOT NULL DEFAULT 1,

    unidad VARCHAR(50)
        NOT NULL DEFAULT 'unidad',

    via_administracion VARCHAR(50)
        DEFAULT NULL,

    color VARCHAR(20)
        NOT NULL DEFAULT '#7C3AED',

    icono VARCHAR(20)
        NOT NULL DEFAULT '💊',

    indicacion TEXT
        DEFAULT NULL,

    notas TEXT
        DEFAULT NULL,

    fecha_inicio DATE
        DEFAULT NULL,

    fecha_fin DATE
        DEFAULT NULL,

    activo TINYINT(1)
        NOT NULL DEFAULT 1,

    creado_en DATETIME
        NOT NULL DEFAULT CURRENT_TIMESTAMP,

    actualizado_en DATETIME
        NOT NULL DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,

    PRIMARY KEY (id),

    INDEX idx_medicamentos_activo (activo),

    INDEX idx_medicamentos_nombre (nombre)

) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;


/* ============================================================
   3. HORARIOS DE MEDICAMENTOS
   ============================================================ */

CREATE TABLE IF NOT EXISTS medicamento_horarios (

    id INT UNSIGNED NOT NULL AUTO_INCREMENT,

    medicamento_id INT UNSIGNED NOT NULL,

    hora TIME NOT NULL,

    momento ENUM(
        'mañana',
        'tarde',
        'noche'
    ) NOT NULL DEFAULT 'mañana',

    dosis DECIMAL(10,2)
        NOT NULL DEFAULT 1,

    unidad_dosis VARCHAR(50)
        NOT NULL DEFAULT 'unidad',

    dias_semana VARCHAR(50)
        NOT NULL DEFAULT '1,2,3,4,5,6,7',

    activo TINYINT(1)
        NOT NULL DEFAULT 1,

    creado_en DATETIME
        NOT NULL DEFAULT CURRENT_TIMESTAMP,

    actualizado_en DATETIME
        NOT NULL DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,

    PRIMARY KEY (id),

    INDEX idx_horarios_medicamento (
        medicamento_id
    ),

    INDEX idx_horarios_hora (
        hora
    ),

    INDEX idx_horarios_momento (
        momento
    ),

    CONSTRAINT fk_horarios_medicamento

        FOREIGN KEY (
            medicamento_id
        )

        REFERENCES medicamentos(id)

        ON DELETE CASCADE

        ON UPDATE CASCADE

) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;


/* ============================================================
   4. TOMAS
   ============================================================ */

CREATE TABLE IF NOT EXISTS tomas (

    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,

    medicamento_id INT UNSIGNED NOT NULL,

    horario_id INT UNSIGNED NOT NULL,

    fecha DATE NOT NULL,

    hora_programada TIME NOT NULL,

    hora_toma DATETIME DEFAULT NULL,

    estado ENUM(
        'pendiente',
        'tomado',
        'omitido',
        'atrasado'
    ) NOT NULL DEFAULT 'pendiente',

    origen ENUM(
        'manual',
        'recordatorio',
        'sistema'
    ) NOT NULL DEFAULT 'manual',

    motivo_omision TEXT
        DEFAULT NULL,

    notas TEXT
        DEFAULT NULL,

    creado_en DATETIME
        NOT NULL DEFAULT CURRENT_TIMESTAMP,

    actualizado_en DATETIME
        NOT NULL DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,

    PRIMARY KEY (id),

    UNIQUE KEY uq_toma_horario_fecha (
        horario_id,
        fecha
    ),

    INDEX idx_tomas_fecha (
        fecha
    ),

    INDEX idx_tomas_estado (
        estado
    ),

    INDEX idx_tomas_medicamento (
        medicamento_id
    ),

    CONSTRAINT fk_tomas_medicamento

        FOREIGN KEY (
            medicamento_id
        )

        REFERENCES medicamentos(id)

        ON DELETE CASCADE

        ON UPDATE CASCADE,

    CONSTRAINT fk_tomas_horario

        FOREIGN KEY (
            horario_id
        )

        REFERENCES medicamento_horarios(id)

        ON DELETE CASCADE

        ON UPDATE CASCADE

) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;


/* ============================================================
   5. HISTORIAL DE CAMBIOS DE TOMAS
   ============================================================ */

CREATE TABLE IF NOT EXISTS historial_tomas (

    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,

    toma_id BIGINT UNSIGNED NOT NULL,

    medicamento_id INT UNSIGNED NOT NULL,

    accion ENUM(
        'tomar',
        'desmarcar',
        'omitir',
        'editar'
    ) NOT NULL,

    estado_anterior VARCHAR(30)
        DEFAULT NULL,

    estado_nuevo VARCHAR(30)
        DEFAULT NULL,

    fecha_hora DATETIME
        NOT NULL DEFAULT CURRENT_TIMESTAMP,

    observacion TEXT
        DEFAULT NULL,

    PRIMARY KEY (id),

    INDEX idx_historial_toma (
        toma_id
    ),

    INDEX idx_historial_fecha (
        fecha_hora
    ),

    CONSTRAINT fk_historial_toma

        FOREIGN KEY (
            toma_id
        )

        REFERENCES tomas(id)

        ON DELETE CASCADE

) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;


/* ============================================================
   6. INVENTARIO DE MEDICAMENTOS
   ============================================================ */

CREATE TABLE IF NOT EXISTS inventario_medicamentos (

    id INT UNSIGNED NOT NULL AUTO_INCREMENT,

    medicamento_id INT UNSIGNED NOT NULL,

    cantidad_actual DECIMAL(10,2)
        NOT NULL DEFAULT 0,

    cantidad_inicial DECIMAL(10,2)
        NOT NULL DEFAULT 0,

    cantidad_minima DECIMAL(10,2)
        NOT NULL DEFAULT 0,

    unidad VARCHAR(50)
        NOT NULL DEFAULT 'unidad',

    fecha_compra DATE
        DEFAULT NULL,

    fecha_vencimiento DATE
        DEFAULT NULL,

    lote VARCHAR(100)
        DEFAULT NULL,

    notas TEXT
        DEFAULT NULL,

    activo TINYINT(1)
        NOT NULL DEFAULT 1,

    actualizado_en DATETIME
        NOT NULL DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,

    PRIMARY KEY (id),

    UNIQUE KEY uq_inventario_medicamento (
        medicamento_id
    ),

    INDEX idx_inventario_vencimiento (
        fecha_vencimiento
    ),

    CONSTRAINT fk_inventario_medicamento

        FOREIGN KEY (
            medicamento_id
        )

        REFERENCES medicamentos(id)

        ON DELETE CASCADE

        ON UPDATE CASCADE

) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;


/* ============================================================
   7. MOVIMIENTOS DE INVENTARIO
   ============================================================ */

CREATE TABLE IF NOT EXISTS movimientos_inventario (

    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,

    medicamento_id INT UNSIGNED NOT NULL,

    tipo ENUM(
        'entrada',
        'consumo',
        'ajuste',
        'devolucion'
    ) NOT NULL,

    cantidad DECIMAL(10,2)
        NOT NULL,

    cantidad_anterior DECIMAL(10,2)
        NOT NULL,

    cantidad_nueva DECIMAL(10,2)
        NOT NULL,

    motivo VARCHAR(255)
        DEFAULT NULL,

    fecha_hora DATETIME
        NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (id),

    INDEX idx_movimientos_medicamento (
        medicamento_id
    ),

    INDEX idx_movimientos_fecha (
        fecha_hora
    ),

    CONSTRAINT fk_movimientos_medicamento

        FOREIGN KEY (
            medicamento_id
        )

        REFERENCES medicamentos(id)

        ON DELETE CASCADE

) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;


/* ============================================================
   8. RECORDATORIOS
   ============================================================ */

CREATE TABLE IF NOT EXISTS recordatorios (

    id INT UNSIGNED NOT NULL AUTO_INCREMENT,

    medicamento_id INT UNSIGNED NOT NULL,

    horario_id INT UNSIGNED DEFAULT NULL,

    minutos_antes INT
        NOT NULL DEFAULT 0,

    tipo ENUM(
        'web',
        'sonido',
        'vibracion'
    ) NOT NULL DEFAULT 'web',

    activo TINYINT(1)
        NOT NULL DEFAULT 1,

    creado_en DATETIME
        NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (id),

    INDEX idx_recordatorios_medicamento (
        medicamento_id
    ),

    CONSTRAINT fk_recordatorios_medicamento

        FOREIGN KEY (
            medicamento_id
        )

        REFERENCES medicamentos(id)

        ON DELETE CASCADE,

    CONSTRAINT fk_recordatorios_horario

        FOREIGN KEY (
            horario_id
        )

        REFERENCES medicamento_horarios(id)

        ON DELETE CASCADE

) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;


/* ============================================================
   9. RACHA
   ============================================================ */

CREATE TABLE IF NOT EXISTS rachas (

    id INT UNSIGNED NOT NULL AUTO_INCREMENT,

    fecha DATE NOT NULL,

    medicamentos_programados INT
        NOT NULL DEFAULT 0,

    medicamentos_tomados INT
        NOT NULL DEFAULT 0,

    porcentaje DECIMAL(5,2)
        NOT NULL DEFAULT 0,

    dia_completo TINYINT(1)
        NOT NULL DEFAULT 0,

    creado_en DATETIME
        NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (id),

    UNIQUE KEY uq_racha_fecha (
        fecha
    ),

    INDEX idx_racha_completo (
        dia_completo
    )

) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;


/* ============================================================
   10. NOTAS PERSONALES
   ============================================================ */

CREATE TABLE IF NOT EXISTS notas (

    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,

    titulo VARCHAR(150)
        NOT NULL,

    contenido TEXT
        NOT NULL,

    fecha DATE
        NOT NULL,

    color VARCHAR(20)
        NOT NULL DEFAULT '#7C3AED',

    fijada TINYINT(1)
        NOT NULL DEFAULT 0,

    creada_en DATETIME
        NOT NULL DEFAULT CURRENT_TIMESTAMP,

    actualizada_en DATETIME
        NOT NULL DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,

    PRIMARY KEY (id),

    INDEX idx_notas_fecha (
        fecha
    ),

    INDEX idx_notas_fijada (
        fijada
    )

) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;


/* ============================================================
   11. AUDITORÍA
   ============================================================ */

CREATE TABLE IF NOT EXISTS auditoria (

    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,

    modulo VARCHAR(100)
        NOT NULL,

    accion VARCHAR(100)
        NOT NULL,

    referencia_id BIGINT UNSIGNED
        DEFAULT NULL,

    descripcion TEXT
        DEFAULT NULL,

    ip VARCHAR(45)
        DEFAULT NULL,

    fecha_hora DATETIME
        NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (id),

    INDEX idx_auditoria_modulo (
        modulo
    ),

    INDEX idx_auditoria_fecha (
        fecha_hora
    ),

    INDEX idx_auditoria_referencia (
        referencia_id
    )

) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;


/* ============================================================
   12. VISTA PARA EL DASHBOARD
   ============================================================ */

CREATE OR REPLACE VIEW vista_medicamentos_dashboard AS

SELECT

    m.id AS medicamento_id,

    m.nombre,

    m.nombre_generico,

    m.presentacion,

    m.cantidad,

    m.unidad,

    m.color,

    m.icono,

    m.indicacion,

    m.notas AS notas_medicamento,

    mh.id AS horario_id,

    mh.hora,

    mh.momento,

    mh.dosis,

    mh.unidad_dosis,

    mh.dias_semana,

    mh.activo AS horario_activo,

    t.id AS toma_id,

    t.fecha,

    t.hora_programada,

    t.hora_toma,

    t.estado AS estado_toma,

    t.origen,

    t.notas AS notas_toma

FROM medicamentos m

INNER JOIN medicamento_horarios mh

    ON mh.medicamento_id = m.id

LEFT JOIN tomas t

    ON t.horario_id = mh.id

WHERE

    m.activo = 1

    AND mh.activo = 1;


/* ============================================================
   13. ÍNDICES ADICIONALES
   ============================================================ */

CREATE INDEX idx_medicamentos_fecha_inicio
ON medicamentos (fecha_inicio);

CREATE INDEX idx_medicamentos_fecha_fin
ON medicamentos (fecha_fin);

CREATE INDEX idx_tomas_fecha_hora
ON tomas (fecha, hora_programada);

CREATE INDEX idx_horarios_activo_hora
ON medicamento_horarios (activo, hora);


/* ============================================================
   FINAL
   ============================================================ */

COMMIT;