-- ============================================================================
-- Oderith Dental — Script SQL Completo
-- ============================================================================
-- Base de datos para sistema de gestión de clínica odontológica.
-- Multi-tenancy por doctor: cada doctor tiene sus datos aislados.
--
-- Ejecutar este script como alternativa a SQLAlchemy db.create_all()
-- Compatible con MySQL 5.7+ / MariaDB 10.3+
-- ============================================================================

-- Crear la base de datos (ajustar el nombre según cPanel)
CREATE DATABASE IF NOT EXISTS oderith_dental
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

USE oderith_dental;

-- ============================================================================
-- TABLA: patients (Pacientes)
-- Aislamiento por doctor_id — cada doctor ve solo sus pacientes
-- ============================================================================
CREATE TABLE IF NOT EXISTS patients (
    id                  INT AUTO_INCREMENT PRIMARY KEY,
    doctor_id           INT          NOT NULL                   COMMENT 'Doctor propietario (multi-tenancy)',
    name                VARCHAR(100) NOT NULL                   COMMENT 'Nombre completo del paciente',
    cedula              VARCHAR(20)  NOT NULL                   COMMENT 'Cédula de identidad',
    birth_date          DATE         DEFAULT NULL               COMMENT 'Fecha de nacimiento',
    gender              VARCHAR(10)  DEFAULT NULL               COMMENT 'Género: M/F/Otro',
    phone               VARCHAR(20)  DEFAULT NULL               COMMENT 'Teléfono / WhatsApp',
    email               VARCHAR(150) DEFAULT NULL               COMMENT 'Correo electrónico',
    address             TEXT         DEFAULT NULL               COMMENT 'Dirección de residencia',
    medical_conditions  JSON         DEFAULT NULL               COMMENT 'Condiciones médicas: diabetes, hipertension, cardiopatias, alergias, hepatitis, vih, asma, epilepsia, embarazo, otros',
    medications         TEXT         DEFAULT NULL               COMMENT 'Medicamentos actuales',
    allergies_detail    TEXT         DEFAULT NULL               COMMENT 'Detalle de alergias',
    observations        TEXT         DEFAULT NULL               COMMENT 'Observaciones generales',
    created_at          DATETIME     DEFAULT CURRENT_TIMESTAMP  COMMENT 'Fecha de registro',
    updated_at          DATETIME     DEFAULT CURRENT_TIMESTAMP
                                     ON UPDATE CURRENT_TIMESTAMP COMMENT 'Última actualización',

    -- Cédula única por doctor (un paciente no puede repetir cédula dentro del mismo doctor)
    UNIQUE KEY uq_doctor_cedula (doctor_id, cedula),
    INDEX idx_patients_doctor (doctor_id),
    INDEX idx_patients_name (doctor_id, name),
    INDEX idx_patients_phone (doctor_id, phone),

    CONSTRAINT fk_patients_doctor
        FOREIGN KEY (doctor_id) REFERENCES doctors(id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Pacientes — aislados por doctor (multi-tenancy)';

-- ============================================================================
-- TABLA: appointments (Citas / Consultas)
-- ============================================================================
CREATE TABLE IF NOT EXISTS appointments (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    doctor_id   INT          NOT NULL                   COMMENT 'Doctor propietario',
    patient_id  INT          NOT NULL                   COMMENT 'Paciente citado',
    date        DATE         NOT NULL                   COMMENT 'Fecha de la cita',
    start_time  TIME         NOT NULL                   COMMENT 'Hora de inicio',
    end_time    TIME         DEFAULT NULL               COMMENT 'Hora de finalización',
    reason      TEXT         DEFAULT NULL               COMMENT 'Motivo de la consulta',
    status      VARCHAR(20)  DEFAULT 'Programada'       COMMENT 'Estado: Programada, Confirmada, En curso, Completada, Cancelada',
    notes       TEXT         DEFAULT NULL               COMMENT 'Notas de la cita',
    created_at  DATETIME     DEFAULT CURRENT_TIMESTAMP  COMMENT 'Fecha de creación',

    INDEX idx_appointments_doctor_date (doctor_id, date),
    INDEX idx_appointments_patient (patient_id),
    INDEX idx_appointments_status (doctor_id, status),

    CONSTRAINT fk_appointments_doctor
        FOREIGN KEY (doctor_id) REFERENCES doctors(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_appointments_patient
        FOREIGN KEY (patient_id) REFERENCES patients(id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Citas odontológicas';

-- ============================================================================
-- TABLA: budgets (Presupuestos)
-- ============================================================================
CREATE TABLE IF NOT EXISTS budgets (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    doctor_id       INT            NOT NULL                   COMMENT 'Doctor propietario',
    patient_id      INT            NOT NULL                   COMMENT 'Paciente del presupuesto',
    subtotal        DECIMAL(10,2)  DEFAULT 0.00               COMMENT 'Subtotal antes de descuento',
    discount_type   VARCHAR(10)    DEFAULT NULL               COMMENT 'Tipo de descuento: percent / fixed',
    discount_value  DECIMAL(10,2)  DEFAULT 0.00               COMMENT 'Valor del descuento (% o monto fijo)',
    discount_amount DECIMAL(10,2)  DEFAULT 0.00               COMMENT 'Monto calculado del descuento',
    total           DECIMAL(10,2)  DEFAULT 0.00               COMMENT 'Total final (subtotal - descuento)',
    status          VARCHAR(20)    DEFAULT 'Pendiente'        COMMENT 'Estado: Pendiente, Aprobado, En progreso, Completado, Rechazado',
    notes           TEXT           DEFAULT NULL               COMMENT 'Notas del presupuesto',
    created_at      DATETIME       DEFAULT CURRENT_TIMESTAMP  COMMENT 'Fecha de creación',
    updated_at      DATETIME       DEFAULT CURRENT_TIMESTAMP
                                   ON UPDATE CURRENT_TIMESTAMP COMMENT 'Última actualización',

    INDEX idx_budgets_doctor (doctor_id),
    INDEX idx_budgets_patient (patient_id),
    INDEX idx_budgets_status (doctor_id, status),

    CONSTRAINT fk_budgets_doctor
        FOREIGN KEY (doctor_id) REFERENCES doctors(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_budgets_patient
        FOREIGN KEY (patient_id) REFERENCES patients(id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Presupuestos odontológicos';

-- ============================================================================
-- TABLA: budget_items (Ítems de presupuesto)
-- ============================================================================
CREATE TABLE IF NOT EXISTS budget_items (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    budget_id   INT            NOT NULL                   COMMENT 'Presupuesto padre',
    description VARCHAR(255)   NOT NULL                   COMMENT 'Descripción del tratamiento',
    teeth       VARCHAR(50)    DEFAULT NULL               COMMENT 'Dientes afectados (ej: 11,12,21)',
    quantity    INT            DEFAULT 1                  COMMENT 'Cantidad',
    unit_price  DECIMAL(10,2)  NOT NULL                   COMMENT 'Precio unitario',
    subtotal    DECIMAL(10,2)  DEFAULT NULL               COMMENT 'Subtotal: cantidad × precio unitario',

    INDEX idx_budget_items_budget (budget_id),

    CONSTRAINT fk_budget_items_budget
        FOREIGN KEY (budget_id) REFERENCES budgets(id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Líneas individuales de un presupuesto';

-- ============================================================================
-- TABLA: accounts (Cuentas por cobrar)
-- ============================================================================
CREATE TABLE IF NOT EXISTS accounts (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    doctor_id    INT            NOT NULL                   COMMENT 'Doctor propietario',
    patient_id   INT            NOT NULL                   COMMENT 'Paciente deudor',
    budget_id    INT            DEFAULT NULL               COMMENT 'Presupuesto asociado (opcional)',
    total_amount DECIMAL(10,2)  NOT NULL                   COMMENT 'Monto total de la cuenta',
    paid_amount  DECIMAL(10,2)  DEFAULT 0.00               COMMENT 'Monto total pagado',
    balance      DECIMAL(10,2)  DEFAULT NULL               COMMENT 'Saldo pendiente (total - pagado)',
    status       VARCHAR(20)    DEFAULT 'Pendiente'        COMMENT 'Estado: Pendiente / Pagado',
    created_at   DATETIME       DEFAULT CURRENT_TIMESTAMP  COMMENT 'Fecha de creación',

    INDEX idx_accounts_doctor (doctor_id),
    INDEX idx_accounts_patient (patient_id),
    INDEX idx_accounts_budget (budget_id),
    INDEX idx_accounts_status (doctor_id, status),

    CONSTRAINT fk_accounts_doctor
        FOREIGN KEY (doctor_id) REFERENCES doctors(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_accounts_patient
        FOREIGN KEY (patient_id) REFERENCES patients(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_accounts_budget
        FOREIGN KEY (budget_id) REFERENCES budgets(id)
        ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Cuentas por cobrar vinculadas a pacientes';

-- ============================================================================
-- TABLA: payments (Pagos)
-- ============================================================================
CREATE TABLE IF NOT EXISTS payments (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    account_id   INT            NOT NULL                   COMMENT 'Cuenta a la que aplica el pago',
    amount       DECIMAL(10,2)  NOT NULL                   COMMENT 'Monto del pago',
    method       VARCHAR(30)    DEFAULT NULL               COMMENT 'Método: Efectivo, Transferencia, Tarjeta, Otro',
    reference    VARCHAR(100)   DEFAULT NULL               COMMENT 'Referencia de pago / Nro. transacción',
    notes        TEXT           DEFAULT NULL               COMMENT 'Notas del pago',
    payment_date DATETIME       DEFAULT CURRENT_TIMESTAMP  COMMENT 'Fecha y hora del pago',

    INDEX idx_payments_account (account_id),
    INDEX idx_payments_date (payment_date),

    CONSTRAINT fk_payments_account
        FOREIGN KEY (account_id) REFERENCES accounts(id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Pagos individuales aplicados a cuentas por cobrar';

-- ============================================================================
-- TABLA: odontograms (Odontogramas digitales)
-- ============================================================================
CREATE TABLE IF NOT EXISTS odontograms (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    patient_id  INT      NOT NULL                       COMMENT 'Paciente del odontograma',
    doctor_id   INT      NOT NULL                       COMMENT 'Doctor propietario',
    data        JSON     DEFAULT NULL                   COMMENT 'Datos del odontograma en JSON: {"teeth": {"11": {"O": "caries", ...}, ...}}',
    notes       TEXT     DEFAULT NULL                   COMMENT 'Notas del odontograma',
    updated_at  DATETIME DEFAULT CURRENT_TIMESTAMP
                         ON UPDATE CURRENT_TIMESTAMP    COMMENT 'Última actualización',

    -- Un odontograma por paciente por doctor
    UNIQUE KEY uq_patient_doctor_odontogram (patient_id, doctor_id),
    INDEX idx_odontograms_doctor (doctor_id),

    CONSTRAINT fk_odontograms_patient
        FOREIGN KEY (patient_id) REFERENCES patients(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_odontograms_doctor
        FOREIGN KEY (doctor_id) REFERENCES doctors(id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Odontogramas digitales — estado de cada diente y cara';

-- ============================================================================
-- TABLA: whatsapp_logs (Registro de mensajes WhatsApp)
-- ============================================================================
CREATE TABLE IF NOT EXISTS whatsapp_logs (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    doctor_id       INT          NOT NULL                   COMMENT 'Doctor que envió el mensaje',
    patient_id      INT          DEFAULT NULL               COMMENT 'Paciente destinatario',
    appointment_id  INT          DEFAULT NULL               COMMENT 'Cita relacionada (opcional)',
    message         TEXT         DEFAULT NULL               COMMENT 'Contenido del mensaje',
    phone           VARCHAR(20)  DEFAULT NULL               COMMENT 'Número de teléfono destino',
    sent_at         DATETIME     DEFAULT CURRENT_TIMESTAMP  COMMENT 'Fecha y hora de envío',

    INDEX idx_whatsapp_doctor (doctor_id),
    INDEX idx_whatsapp_patient (patient_id),
    INDEX idx_whatsapp_date (doctor_id, sent_at),

    CONSTRAINT fk_whatsapp_doctor
        FOREIGN KEY (doctor_id) REFERENCES doctors(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_whatsapp_patient
        FOREIGN KEY (patient_id) REFERENCES patients(id)
        ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_whatsapp_appointment
        FOREIGN KEY (appointment_id) REFERENCES appointments(id)
        ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Registro de mensajes WhatsApp enviados a pacientes';

-- ============================================================================
-- NOTAS DE INSTALACIÓN
-- ============================================================================
-- 1. Crear la base de datos en cPanel → MySQL Databases
-- 2. Crear un usuario MySQL y asignarle todos los privilegios
-- 3. Ejecutar este script desde phpMyAdmin o línea de comandos
-- 4. Regenerar el hash de la clave del admin con el script Python indicado arriba
-- 5. Actualizar DATABASE_URL en el archivo .env con las credenciales correctas
-- ============================================================================
