CREATE DATABASE IF NOT EXISTS ficha_medica CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE ficha_medica;

CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS patients (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uk_patient_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS procedures_catalog (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uk_procedure_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS medications_catalog (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uk_medication_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS medical_followups (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  patient_id INT NOT NULL,
  followup_date DATE NOT NULL,
  followup_time TIME NOT NULL,
  procedure_id INT NULL,
  medication_id INT NULL,
  blood_pressure VARCHAR(20) NULL,
  oxygen_saturation DECIMAL(5,2) NULL,
  observation TEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_followups_user FOREIGN KEY (user_id) REFERENCES users(id),
  CONSTRAINT fk_followups_patient FOREIGN KEY (patient_id) REFERENCES patients(id),
  CONSTRAINT fk_followups_procedure FOREIGN KEY (procedure_id) REFERENCES procedures_catalog(id),
  CONSTRAINT fk_followups_medication FOREIGN KEY (medication_id) REFERENCES medications_catalog(id),
  INDEX idx_followup_date_time (followup_date, followup_time),
  INDEX idx_followup_user (user_id),
  INDEX idx_followup_patient (patient_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO users (id, name) VALUES (1, 'Hermes');
INSERT IGNORE INTO patients (id, name) VALUES (1, 'Yiyita');
INSERT IGNORE INTO procedures_catalog (name) VALUES ('Control general'), ('Curación'), ('Nebulización'), ('Control de signos vitales');
INSERT IGNORE INTO medications_catalog (name) VALUES ('Paracetamol'), ('Ibuprofeno'), ('Omeprazol'), ('Salbutamol');
