CREATE TABLE IF NOT EXISTS patient_notes (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  id_patient INT NOT NULL,

  -- Nota “pegada” a algo o general
  entity_type ENUM('patient','appointment','charge','payment') NOT NULL DEFAULT 'patient',
  entity_id BIGINT UNSIGNED NULL,

  -- Texto orgánico (markdown simple o texto normal)
  body MEDIUMTEXT NOT NULL,

  -- Auditoría
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by INT NULL,
  updated_at DATETIME NULL,
  updated_by INT NULL,

  -- “flags” opcionales
  pinned TINYINT(1) NOT NULL DEFAULT 0,

  PRIMARY KEY (id),
  KEY idx_patient_time (id_patient, created_at),
  KEY idx_entity (entity_type, entity_id),
  FULLTEXT KEY ft_body (body)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


ALTER TABLE appointments
  MODIFY id_patient INT(11) NULL,
  ADD COLUMN temp_patient_name  VARCHAR(160) NULL,
  ADD COLUMN temp_patient_phone VARCHAR(20)  NULL,
  ADD COLUMN is_first_time TINYINT(1) NOT NULL DEFAULT 0,
  ADD KEY idx_app_temp (temp_patient_phone);

ALTER TABLE charges
  ADD COLUMN note_id BIGINT UNSIGNED NULL,
  ADD KEY idx_note_id (note_id);