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

CREATE TABLE IF NOT EXISTS pacientes (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nombre VARCHAR(120) NOT NULL,
  rut VARCHAR(20) NOT NULL UNIQUE,
  direccion VARCHAR(200) DEFAULT NULL,
  telefono VARCHAR(50) DEFAULT NULL,
  mascota VARCHAR(120) DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS doctores (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nombre VARCHAR(120) NOT NULL,
  rut VARCHAR(20) NOT NULL UNIQUE,
  especialidad VARCHAR(120) DEFAULT NULL,
  direccion VARCHAR(200) DEFAULT NULL,
  telefono VARCHAR(50) DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS componentes (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nombre VARCHAR(160) NOT NULL UNIQUE,
  lote VARCHAR(80) DEFAULT NULL,
  vencimiento DATE DEFAULT NULL,
  precio_compra_g DECIMAL(12,2) NOT NULL DEFAULT 0,
  precio_venta_g DECIMAL(12,2) NOT NULL DEFAULT 0,
  estado ENUM('en_uso','no_uso') NOT NULL DEFAULT 'en_uso',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS preparados (
  id INT AUTO_INCREMENT PRIMARY KEY,
  numero_externo VARCHAR(50) NOT NULL,
  id_doctor INT NOT NULL,
  id_paciente INT NOT NULL,
  destino VARCHAR(160) DEFAULT NULL,
  forma_farmaceutica ENUM('cápsulas','polvo','cremas','shampoo','Gel','Ungüento','Locion','laca','spray','gotas','Jarabe','supositorios') NOT NULL,
  total DECIMAL(12,2) NOT NULL DEFAULT 0,
  unidad_medida VARCHAR(20) NOT NULL,
  excipiente VARCHAR(200) DEFAULT NULL,
  valor_calculado DECIMAL(12,0) NOT NULL DEFAULT 0,
  valor_real DECIMAL(12,0) DEFAULT NULL,
  lugar_entrega VARCHAR(160) DEFAULT NULL,
  fecha_elaboracion DATE NOT NULL,
  fecha_vencimiento DATE NOT NULL,
  observaciones TEXT DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE(numero_externo),
  INDEX(id_doctor),
  INDEX(id_paciente),
  CONSTRAINT fk_prep_doc FOREIGN KEY (id_doctor) REFERENCES doctores(id) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT fk_prep_pac FOREIGN KEY (id_paciente) REFERENCES pacientes(id) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS componentes_preparado (
  id INT AUTO_INCREMENT PRIMARY KEY,
  id_preparado INT NOT NULL,
  id_componente INT NOT NULL,
  nombre_componente VARCHAR(160) NOT NULL,
  unidad_medida VARCHAR(30) NOT NULL,
  cantidad DECIMAL(12,4) NOT NULL DEFAULT 0,
  compra_g DECIMAL(12,2) NOT NULL DEFAULT 0,
  venta_g DECIMAL(12,2) NOT NULL DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX(id_preparado),
  INDEX(id_componente),
  CONSTRAINT fk_cp_prep FOREIGN KEY (id_preparado) REFERENCES preparados(id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_cp_comp FOREIGN KEY (id_componente) REFERENCES componentes(id) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB;