-- PharmaCare: multi-pharmacy POS + clinic + patient portal. ONE file, fresh install.
-- Works on shared hosting (cPanel): no CREATE DATABASE, CREATE USER or GRANT needed.
-- WARNING: this drops and recreates every table. Do not import over live data.
-- Select the database deepcore_pharmacy in phpMyAdmin first, then import this file.

SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS reminders, sale_items, sales, prescription_items, prescriptions,
  avatars, chat_messages, patient_complaints, expenses, consultations, condition_drugs, conditions,
  purchase_order_items, purchase_orders, suppliers, stock_movements, drug_batches, drugs,
  drug_interactions, drug_rules, drug_catalog, sms_outbox, audit_log, pharmacy_applications,
  patient_accounts, patients, staff_users, pharmacies;
SET FOREIGN_KEY_CHECKS = 1;

CREATE TABLE pharmacies (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  address VARCHAR(255) DEFAULT '',
  phone VARCHAR(30) DEFAULT '',
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Roles: admin (platform, no pharmacy), owner, doctor, pharmacist, cashier
CREATE TABLE staff_users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  pharmacy_id INT DEFAULT NULL,
  username VARCHAR(30) NOT NULL UNIQUE,
  name VARCHAR(120) NOT NULL,
  role ENUM('admin','owner','doctor','pharmacist','cashier') NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  must_change TINYINT(1) NOT NULL DEFAULT 0,
  failed_attempts INT NOT NULL DEFAULT 0,
  locked_until DATETIME DEFAULT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (pharmacy_id) REFERENCES pharmacies(id)
);

-- Patients are shared across the platform; a pharmacy only sees patients who
-- were registered there or who sent it a complaint / had a consultation with it.
CREATE TABLE patients (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  phone VARCHAR(30),
  age INT,
  sex ENUM('M','F') DEFAULT NULL,
  allergies VARCHAR(255) DEFAULT '',
  -- what "morning / afternoon / evening / night" means for this patient (used for reminders)
  t_morning TIME NOT NULL DEFAULT '08:00:00',
  t_afternoon TIME NOT NULL DEFAULT '14:00:00',
  t_evening TIME NOT NULL DEFAULT '20:00:00',
  t_night TIME NOT NULL DEFAULT '22:00:00',
  pregnant TINYINT(1) NOT NULL DEFAULT 0,
  sms_reminders TINYINT(1) NOT NULL DEFAULT 0,   -- patient agreed to get reminders by SMS
  pharmacy_id INT DEFAULT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (pharmacy_id) REFERENCES pharmacies(id)
);

CREATE TABLE patient_accounts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  patient_id INT NOT NULL UNIQUE,
  phone VARCHAR(20) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  failed_attempts INT NOT NULL DEFAULT 0,
  locked_until DATETIME DEFAULT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (patient_id) REFERENCES patients(id)
);

-- Shared list of medicines. Each pharmacy gets its own priced copy in `drugs`.
CREATE TABLE drug_catalog (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  strength VARCHAR(60) DEFAULT '',
  form VARCHAR(40) DEFAULT '',
  default_price DECIMAL(10,2) NOT NULL DEFAULT 0
);

-- Starter safety data. NOT exhaustive: a pharmacist or doctor must review and extend it.
CREATE TABLE drug_interactions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  catalog_a INT NOT NULL,
  catalog_b INT NOT NULL,
  severity ENUM('major','moderate') NOT NULL,
  message VARCHAR(255) NOT NULL,
  FOREIGN KEY (catalog_a) REFERENCES drug_catalog(id),
  FOREIGN KEY (catalog_b) REFERENCES drug_catalog(id)
);

CREATE TABLE drug_rules (
  id INT AUTO_INCREMENT PRIMARY KEY,
  catalog_id INT NOT NULL,
  rule ENUM('min_age','pregnancy') NOT NULL,
  value INT DEFAULT NULL,
  message VARCHAR(255) NOT NULL,
  FOREIGN KEY (catalog_id) REFERENCES drug_catalog(id)
);

-- Per-pharmacy inventory
CREATE TABLE drugs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  pharmacy_id INT NOT NULL,
  catalog_id INT DEFAULT NULL,
  name VARCHAR(120) NOT NULL,
  strength VARCHAR(60) DEFAULT '',
  form VARCHAR(40) DEFAULT '',
  price DECIMAL(10,2) NOT NULL,
  cost_price DECIMAL(10,2) NOT NULL DEFAULT 0,   -- what the pharmacy pays per unit (latest purchase)
  stock INT NOT NULL DEFAULT 0,
  reorder_level INT NOT NULL DEFAULT 20,
  expiry DATE DEFAULT NULL,
  INDEX (pharmacy_id, catalog_id),
  FOREIGN KEY (pharmacy_id) REFERENCES pharmacies(id),
  FOREIGN KEY (catalog_id) REFERENCES drug_catalog(id)
);

-- Stock is kept in batches (each with its own expiry and cost). drugs.stock and drugs.expiry
-- are totals the app keeps in sync: stock = sum of batches, expiry = the earliest batch expiry.
CREATE TABLE drug_batches (
  id INT AUTO_INCREMENT PRIMARY KEY,
  pharmacy_id INT NOT NULL,
  drug_id INT NOT NULL,
  batch_no VARCHAR(40) DEFAULT '',
  expiry DATE DEFAULT NULL,
  qty_received INT NOT NULL,
  qty_remaining INT NOT NULL,
  unit_cost DECIMAL(10,2) NOT NULL DEFAULT 0,
  source VARCHAR(20) DEFAULT 'manual',
  received_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX (drug_id, expiry),
  FOREIGN KEY (pharmacy_id) REFERENCES pharmacies(id),
  FOREIGN KEY (drug_id) REFERENCES drugs(id)
);

CREATE TABLE stock_movements (
  id INT AUTO_INCREMENT PRIMARY KEY,
  pharmacy_id INT NOT NULL,
  drug_id INT NOT NULL,
  batch_id INT DEFAULT NULL,
  kind ENUM('received','sale','refund','writeoff') NOT NULL,
  qty INT NOT NULL,                              -- signed: + into stock, - out of stock
  unit_cost DECIMAL(10,2) NOT NULL DEFAULT 0,
  staff_id INT DEFAULT NULL,
  note VARCHAR(200) DEFAULT '',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX (pharmacy_id, created_at),
  FOREIGN KEY (drug_id) REFERENCES drugs(id)
);

CREATE TABLE suppliers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  pharmacy_id INT NOT NULL,
  name VARCHAR(120) NOT NULL,
  phone VARCHAR(30) DEFAULT '',
  email VARCHAR(120) DEFAULT '',
  address VARCHAR(255) DEFAULT '',
  active TINYINT(1) NOT NULL DEFAULT 1,
  FOREIGN KEY (pharmacy_id) REFERENCES pharmacies(id)
);

CREATE TABLE purchase_orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  pharmacy_id INT NOT NULL,
  supplier_id INT NOT NULL,
  status ENUM('draft','ordered','received','cancelled') NOT NULL DEFAULT 'draft',
  notes VARCHAR(255) DEFAULT '',
  created_by INT DEFAULT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  ordered_at DATETIME DEFAULT NULL,
  received_at DATETIME DEFAULT NULL,
  INDEX (pharmacy_id, status),
  FOREIGN KEY (pharmacy_id) REFERENCES pharmacies(id),
  FOREIGN KEY (supplier_id) REFERENCES suppliers(id)
);

CREATE TABLE purchase_order_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  po_id INT NOT NULL,
  drug_id INT NOT NULL,
  qty INT NOT NULL,
  unit_cost DECIMAL(10,2) NOT NULL DEFAULT 0,
  received_qty INT NOT NULL DEFAULT 0,
  FOREIGN KEY (po_id) REFERENCES purchase_orders(id),
  FOREIGN KEY (drug_id) REFERENCES drugs(id)
);

-- Knowledge base for diagnosis SUGGESTIONS (a clinician must confirm)
CREATE TABLE conditions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  keywords VARCHAR(500) NOT NULL,
  advice VARCHAR(500) DEFAULT ''
);

CREATE TABLE condition_drugs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  condition_id INT NOT NULL,
  catalog_id INT NOT NULL,
  units_per_dose INT NOT NULL DEFAULT 1,
  times_per_day INT NOT NULL DEFAULT 1,
  days INT NOT NULL DEFAULT 3,
  timing VARCHAR(60) NOT NULL DEFAULT 'morning,evening',
  instructions VARCHAR(255) DEFAULT '',
  FOREIGN KEY (condition_id) REFERENCES conditions(id),
  FOREIGN KEY (catalog_id) REFERENCES drug_catalog(id)
);

CREATE TABLE consultations (
  id INT AUTO_INCREMENT PRIMARY KEY,
  pharmacy_id INT NOT NULL,
  patient_id INT NOT NULL,
  complaint TEXT NOT NULL,
  suggested TEXT,
  diagnosis VARCHAR(255) DEFAULT NULL,
  status ENUM('pending','confirmed') DEFAULT 'pending',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (pharmacy_id) REFERENCES pharmacies(id),
  FOREIGN KEY (patient_id) REFERENCES patients(id)
);

CREATE TABLE patient_complaints (
  id INT AUTO_INCREMENT PRIMARY KEY,
  pharmacy_id INT NOT NULL,
  patient_id INT NOT NULL,
  complaint TEXT NOT NULL,
  urgent TINYINT(1) NOT NULL DEFAULT 0,
  urgent_note VARCHAR(255) DEFAULT '',
  status ENUM('new','reviewed') NOT NULL DEFAULT 'new',
  consultation_id INT DEFAULT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX (pharmacy_id, status, created_at),
  FOREIGN KEY (pharmacy_id) REFERENCES pharmacies(id),
  FOREIGN KEY (patient_id) REFERENCES patients(id),
  FOREIGN KEY (consultation_id) REFERENCES consultations(id)
);

CREATE TABLE prescriptions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  pharmacy_id INT NOT NULL,
  consultation_id INT NOT NULL,
  patient_id INT NOT NULL,
  created_by INT DEFAULT NULL,
  status ENUM('sent','dispensed') DEFAULT 'sent',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  dispensed_at DATETIME DEFAULT NULL,
  INDEX (pharmacy_id, status),
  FOREIGN KEY (pharmacy_id) REFERENCES pharmacies(id),
  FOREIGN KEY (consultation_id) REFERENCES consultations(id),
  FOREIGN KEY (patient_id) REFERENCES patients(id),
  FOREIGN KEY (created_by) REFERENCES staff_users(id)
);

CREATE TABLE prescription_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  prescription_id INT NOT NULL,
  drug_id INT NOT NULL,
  units_per_dose INT NOT NULL,
  times_per_day INT NOT NULL,
  days INT NOT NULL,
  timing VARCHAR(60) NOT NULL DEFAULT 'morning,evening',
  instructions VARCHAR(255) DEFAULT '',
  qty INT NOT NULL,
  FOREIGN KEY (prescription_id) REFERENCES prescriptions(id),
  FOREIGN KEY (drug_id) REFERENCES drugs(id)
);

CREATE TABLE sales (
  id INT AUTO_INCREMENT PRIMARY KEY,
  pharmacy_id INT NOT NULL,
  prescription_id INT DEFAULT NULL,
  patient_id INT DEFAULT NULL,
  staff_id INT DEFAULT NULL,
  total DECIMAL(10,2) NOT NULL,                  -- negative for a refund
  method ENUM('cash','momo','card') DEFAULT 'cash',
  kind ENUM('sale','refund') NOT NULL DEFAULT 'sale',
  refund_of INT DEFAULT NULL,
  reason VARCHAR(200) DEFAULT '',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX (pharmacy_id, created_at),
  FOREIGN KEY (pharmacy_id) REFERENCES pharmacies(id)
);

CREATE TABLE sale_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  sale_id INT NOT NULL,
  drug_id INT NOT NULL,
  qty INT NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  cost DECIMAL(10,2) NOT NULL DEFAULT 0,   -- unit cost at the moment of sale, so profit stays correct later
  batch_id INT DEFAULT NULL,
  refund_of_item INT DEFAULT NULL,         -- set on refund lines (qty is negative)
  FOREIGN KEY (sale_id) REFERENCES sales(id),
  FOREIGN KEY (drug_id) REFERENCES drugs(id)
);

CREATE TABLE expenses (
  id INT AUTO_INCREMENT PRIMARY KEY,
  pharmacy_id INT NOT NULL,
  expense_date DATE NOT NULL,
  category VARCHAR(40) NOT NULL,
  description VARCHAR(200) DEFAULT '',
  amount DECIMAL(10,2) NOT NULL,
  staff_id INT DEFAULT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX (pharmacy_id, expense_date),
  FOREIGN KEY (pharmacy_id) REFERENCES pharmacies(id)
);

CREATE TABLE audit_log (
  id INT AUTO_INCREMENT PRIMARY KEY,
  pharmacy_id INT DEFAULT NULL,
  staff_id INT DEFAULT NULL,
  staff_name VARCHAR(120) DEFAULT '',
  action VARCHAR(40) NOT NULL,
  detail VARCHAR(500) DEFAULT '',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX (pharmacy_id, created_at)
);

CREATE TABLE sms_outbox (
  id INT AUTO_INCREMENT PRIMARY KEY,
  pharmacy_id INT DEFAULT NULL,
  patient_id INT DEFAULT NULL,
  phone VARCHAR(20) NOT NULL,
  message VARCHAR(480) NOT NULL,
  kind ENUM('reminder','ready','test') NOT NULL,
  reminder_id INT DEFAULT NULL,
  status ENUM('queued','sent','failed','skipped') NOT NULL DEFAULT 'queued',
  attempts INT NOT NULL DEFAULT 0,
  provider_ref VARCHAR(80) DEFAULT '',
  error VARCHAR(255) DEFAULT '',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  sent_at DATETIME DEFAULT NULL,
  INDEX (status, created_at),
  INDEX (pharmacy_id, created_at)
);

CREATE TABLE pharmacy_applications (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  address VARCHAR(255) DEFAULT '',
  phone VARCHAR(30) DEFAULT '',
  owner_name VARCHAR(120) NOT NULL,
  owner_username VARCHAR(30) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  status ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  reason VARCHAR(200) DEFAULT '',
  ip VARCHAR(45) DEFAULT '',
  pharmacy_id INT DEFAULT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  decided_at DATETIME DEFAULT NULL,
  INDEX (status, created_at)
);

CREATE TABLE reminders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  patient_id INT NOT NULL,
  prescription_item_id INT NOT NULL,
  remind_at DATETIME NOT NULL,
  label VARCHAR(30) NOT NULL DEFAULT '',
  message VARCHAR(255) NOT NULL,
  done TINYINT(1) DEFAULT 0,
  sms_queued TINYINT(1) NOT NULL DEFAULT 0,
  INDEX (remind_at, done),
  FOREIGN KEY (patient_id) REFERENCES patients(id)
);

-- ---------- Seed data (prices in GH₵; edit to your own) ----------
INSERT INTO drug_catalog (id,name,strength,form,default_price) VALUES
(1,'Artemether/Lumefantrine','20/120mg','tablet',1.50),
(2,'Paracetamol','500mg','tablet',0.20),
(3,'Amoxicillin','500mg','capsule',0.80),
(4,'Oral Rehydration Salts','','sachet',2.00),
(5,'Cetirizine','10mg','tablet',0.50),
(6,'Omeprazole','20mg','capsule',0.90),
(7,'Ciprofloxacin','500mg','tablet',1.20),
(8,'Vitamin C','100mg','tablet',0.10),
(9,'Ibuprofen','400mg','tablet',0.30),
(10,'Diclofenac','50mg','tablet',0.40),
(11,'Metronidazole','400mg','tablet',0.30),
(12,'Azithromycin','500mg','tablet',2.50);

INSERT INTO pharmacies (id,name,address,phone) VALUES (1,'Main Pharmacy','Accra','');

INSERT INTO drugs (pharmacy_id,catalog_id,name,strength,form,price,cost_price,stock,reorder_level,expiry)
SELECT 1,id,name,strength,form,default_price,ROUND(default_price*0.7,2),
  CASE id WHEN 1 THEN 300 WHEN 2 THEN 1000 WHEN 3 THEN 400 WHEN 4 THEN 80 WHEN 5 THEN 200 WHEN 6 THEN 250 WHEN 7 THEN 150 ELSE 500 END,
  CASE id WHEN 1 THEN 60 WHEN 2 THEN 200 WHEN 3 THEN 100 WHEN 4 THEN 20 WHEN 5 THEN 40 WHEN 6 THEN 50 WHEN 7 THEN 40 ELSE 100 END,
  '2027-12-31'
FROM drug_catalog;

-- Opening stock for the demo pharmacy, as one batch per drug
INSERT INTO drug_batches (pharmacy_id,drug_id,batch_no,expiry,qty_received,qty_remaining,unit_cost,source)
SELECT pharmacy_id,id,'OPENING',expiry,stock,stock,cost_price,'opening' FROM drugs WHERE pharmacy_id = 1;

-- Starter safety rules (review with a pharmacist before real use)
INSERT INTO drug_interactions (catalog_a,catalog_b,severity,message) VALUES
(1,7,'moderate','Artemether/lumefantrine and ciprofloxacin can both prolong the QT interval (heart rhythm). Use together only if the benefit outweighs the risk.'),
(1,12,'moderate','Artemether/lumefantrine and azithromycin can both prolong the QT interval (heart rhythm). Use together only if the benefit outweighs the risk.'),
(7,12,'moderate','Ciprofloxacin and azithromycin can both prolong the QT interval (heart rhythm).'),
(9,10,'major','Ibuprofen and diclofenac are both NSAIDs. Taking two together raises the risk of stomach bleeding and kidney harm without better pain relief.');

INSERT INTO drug_rules (catalog_id,rule,value,message) VALUES
(7,'min_age',18,'Ciprofloxacin is generally avoided under 18 years of age. Check before prescribing.'),
(7,'pregnancy',NULL,'Ciprofloxacin is generally avoided in pregnancy.'),
(9,'pregnancy',NULL,'NSAIDs such as ibuprofen are generally avoided in pregnancy, especially from 28 weeks.'),
(10,'pregnancy',NULL,'NSAIDs such as diclofenac are generally avoided in pregnancy, especially from 28 weeks.'),
(11,'pregnancy',NULL,'Metronidazole in early pregnancy: check with a doctor first.');

INSERT INTO conditions (id,name,keywords,advice) VALUES
(1,'Malaria (suspected)','fever,chills,headache,body pain,sweating,shivering,weakness','Confirm with a malaria test (RDT/microscopy) before treating. Encourage fluids and rest.'),
(2,'Common cold / upper respiratory infection','cough,runny nose,sneezing,sore throat,blocked nose,catarrh','Rest, warm fluids. Antibiotics are usually not needed.'),
(3,'Gastroenteritis','diarrhoea,diarrhea,vomiting,nausea,stomach pain,loose stool,watery stool','Rehydrate. Refer if blood in stool, severe dehydration or symptoms over 3 days.'),
(4,'Urinary tract infection (suspected)','burning urine,painful urination,frequent urination,lower abdominal pain,cloudy urine','Confirm with urinalysis. Refer if fever, flank pain or pregnancy.'),
(5,'Gastritis / acid reflux','heartburn,epigastric pain,burning stomach,acid,bloating,sour taste','Avoid spicy food and late meals. Refer if black stool or persistent vomiting.'),
(6,'Tension headache','headache,stress,neck pain,tight head','Rest, hydrate. Refer if sudden severe headache, vision changes or head injury.');

-- timing = which parts of the day: morning, afternoon, evening, night (comma separated)
INSERT INTO condition_drugs (condition_id,catalog_id,units_per_dose,times_per_day,days,timing,instructions) VALUES
(1,1,4,2,3,'morning,evening','Take with food or milk. Complete all doses.'),
(1,2,2,3,3,'morning,afternoon,evening','For fever or pain. Maximum 8 tablets in 24 hours.'),
(2,5,1,1,5,'night','Take at night; may cause drowsiness.'),
(2,2,2,3,3,'morning,afternoon,evening','For fever or pain. Maximum 8 tablets in 24 hours.'),
(2,8,1,2,5,'morning,evening','Take after food.'),
(3,4,1,3,3,'morning,afternoon,evening','Dissolve 1 sachet in 1 litre of clean water; sip after each loose stool.'),
(4,7,1,2,3,'morning,evening','Take with plenty of water. Complete the course.'),
(4,2,2,3,3,'morning,afternoon,evening','For pain. Maximum 8 tablets in 24 hours.'),
(5,6,1,1,14,'morning','Take 30 minutes before breakfast.'),
(6,2,2,3,3,'morning,afternoon,evening','Take after food. Maximum 8 tablets in 24 hours.');

-- Starter logins. Both are forced to choose a new password at first login.
--   admin  (platform administrator: creates pharmacies)   password: 705c742782
--   owner  (owner of "Main Pharmacy": creates its staff)  password: ebab0571fe
INSERT INTO staff_users (pharmacy_id,username,name,role,password_hash,must_change) VALUES
(NULL,'admin','Platform Admin','admin','$2y$10$Ezize3BJY4lOMtHikfPwmOJC3pMEwqwMTOs8/RNfnlf9N79F01EDm',1),
(1,'owner','Main Pharmacy Owner','owner','$2y$10$PLTx5yqh3qcw338FmPTXb.lxtIHrz5e.UfRnM5GGREor1a2g2ffbq',1);

-- Chat between a patient and a pharmacy (one conversation per patient and pharmacy)
CREATE TABLE chat_messages (
  id INT AUTO_INCREMENT PRIMARY KEY,
  pharmacy_id INT NOT NULL,
  patient_id INT NOT NULL,
  sender ENUM('patient','staff','system') NOT NULL,
  staff_name VARCHAR(120) DEFAULT '',
  body VARCHAR(2000) NOT NULL,
  urgent TINYINT(1) NOT NULL DEFAULT 0,
  read_by_staff TINYINT(1) NOT NULL DEFAULT 0,
  read_by_patient TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX (pharmacy_id, patient_id, id),
  INDEX (patient_id, pharmacy_id, id),
  FOREIGN KEY (pharmacy_id) REFERENCES pharmacies(id),
  FOREIGN KEY (patient_id) REFERENCES patients(id)
);

-- Profile pictures: patients, staff and pharmacy logos (small JPEGs, 256x256)
CREATE TABLE avatars (
  owner_type ENUM('patient','staff','pharmacy') NOT NULL,
  owner_id INT NOT NULL,
  data MEDIUMBLOB NOT NULL,
  ver INT NOT NULL,
  PRIMARY KEY (owner_type, owner_id)
);
