-- IMPORTANT: Do not CREATE DATABASE or USE a database here.
-- In cPanel hosting, create/select the database in cPanel/phpMyAdmin first,
-- then import this file while that database is selected.
CREATE TABLE IF NOT EXISTS roles (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50) NOT NULL UNIQUE, description VARCHAR(255) NULL);
CREATE TABLE IF NOT EXISTS permissions (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(80) NOT NULL UNIQUE);
CREATE TABLE IF NOT EXISTS role_permissions (role_id INT UNSIGNED NOT NULL, permission_id INT UNSIGNED NOT NULL, PRIMARY KEY(role_id,permission_id), FOREIGN KEY(role_id) REFERENCES roles(id) ON DELETE CASCADE, FOREIGN KEY(permission_id) REFERENCES permissions(id) ON DELETE CASCADE);
CREATE TABLE IF NOT EXISTS users (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, role_id INT UNSIGNED NOT NULL, name VARCHAR(120) NOT NULL, email VARCHAR(190) NOT NULL UNIQUE, password_hash VARCHAR(255) NOT NULL, qualification VARCHAR(120) NULL, registration_no VARCHAR(80) NULL, sterilized TINYINT(1) NULL, microchip_present TINYINT(1) NULL, microchip_serial VARCHAR(80) NULL, markings TEXT NULL, age VARCHAR(50) NULL, vaccinated TINYINT(1) NULL, vaccinated_date DATE NULL, active TINYINT(1) NOT NULL DEFAULT 1, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(role_id) REFERENCES roles(id));
-- Client account_no is auto-generated by the application as VSP-###### when omitted.
CREATE TABLE IF NOT EXISTS clients (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, account_no VARCHAR(40) UNIQUE, id_number VARCHAR(40) NULL, id_document_path VARCHAR(500) NULL, discount_eligible TINYINT(1) NOT NULL DEFAULT 0, discount_percent DECIMAL(5,2) NOT NULL DEFAULT 0, bad_debt TINYINT(1) NOT NULL DEFAULT 0, bad_debt_comment TEXT NULL, first_name VARCHAR(80) NOT NULL, last_name VARCHAR(80) NOT NULL, phone VARCHAR(40), whatsapp VARCHAR(40), email VARCHAR(190), address TEXT, notes TEXT, active TINYINT(1) DEFAULT 1, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
CREATE TABLE IF NOT EXISTS patients (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, patient_number VARCHAR(50) NOT NULL UNIQUE, client_id BIGINT UNSIGNED NOT NULL, name VARCHAR(100) NOT NULL, species VARCHAR(50) NOT NULL, breed VARCHAR(100), sex VARCHAR(20), date_of_birth DATE NULL, weight_kg DECIMAL(7,2), microchip VARCHAR(80), colour VARCHAR(80), alerts TEXT, notes TEXT, sterilized TINYINT(1) NULL, microchip_present TINYINT(1) NULL, microchip_serial VARCHAR(80) NULL, markings TEXT NULL, age VARCHAR(50) NULL, vaccinated TINYINT(1) NULL, vaccinated_date DATE NULL, active TINYINT(1) NOT NULL DEFAULT 1, deceased TINYINT(1) NOT NULL DEFAULT 0, deceased_date DATE NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY(client_id) REFERENCES clients(id));
CREATE TABLE IF NOT EXISTS patient_weight_history (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 patient_id BIGINT UNSIGNED NOT NULL,
 weight_kg DECIMAL(7,2) NOT NULL,
 recorded_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 recorded_by INT UNSIGNED NULL,
 notes VARCHAR(255) NULL,
 FOREIGN KEY(patient_id) REFERENCES patients(id) ON DELETE CASCADE,
 FOREIGN KEY(recorded_by) REFERENCES users(id) ON DELETE SET NULL,
 INDEX idx_weight_patient_date(patient_id,recorded_at)
);
CREATE TABLE IF NOT EXISTS patient_documents (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 patient_id BIGINT UNSIGNED NOT NULL,
 document_type VARCHAR(80) NOT NULL,
 file_name VARCHAR(255) NOT NULL,
 file_path VARCHAR(500) NOT NULL,
 uploaded_by INT UNSIGNED NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(patient_id) REFERENCES patients(id) ON DELETE CASCADE,
 FOREIGN KEY(uploaded_by) REFERENCES users(id) ON DELETE SET NULL
);
CREATE TABLE IF NOT EXISTS appointment_types (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL, duration_minutes INT DEFAULT 30, colour VARCHAR(20) DEFAULT '#0f766e', active TINYINT(1) DEFAULT 1);
CREATE TABLE IF NOT EXISTS appointments (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, patient_id BIGINT UNSIGNED NOT NULL, vet_id INT UNSIGNED NULL, appointment_type_id INT UNSIGNED NULL, starts_at DATETIME NOT NULL, status ENUM('booked','arrived','waiting','with_vet','awaiting_payment','completed','cancelled','no_show') DEFAULT 'booked', reason VARCHAR(255), notes TEXT, created_by INT UNSIGNED NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(patient_id) REFERENCES patients(id), FOREIGN KEY(vet_id) REFERENCES users(id), FOREIGN KEY(appointment_type_id) REFERENCES appointment_types(id), FOREIGN KEY(created_by) REFERENCES users(id));
CREATE TABLE IF NOT EXISTS consultations (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, appointment_id BIGINT UNSIGNED NULL, patient_id BIGINT UNSIGNED NOT NULL, vet_id INT UNSIGNED NOT NULL, reason TEXT, history TEXT, examination TEXT, vitals TEXT, diagnosis TEXT, treatment TEXT, follow_up TEXT, follow_up_required TINYINT(1) NOT NULL DEFAULT 0, follow_up_date DATE NULL, follow_up_time TIME NULL, follow_up_reason VARCHAR(255) NULL, follow_up_notes TEXT NULL, hospitalize TINYINT(1) NOT NULL DEFAULT 0, hospitalization_reason TEXT NULL, hospitalization_notes TEXT NULL, status ENUM('draft','completed') DEFAULT 'draft', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, completed_at DATETIME NULL, FOREIGN KEY(appointment_id) REFERENCES appointments(id) ON DELETE SET NULL, FOREIGN KEY(patient_id) REFERENCES patients(id), FOREIGN KEY(vet_id) REFERENCES users(id));
CREATE TABLE IF NOT EXISTS products (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, sku VARCHAR(60) UNIQUE, name VARCHAR(150) NOT NULL, category VARCHAR(100), is_otc TINYINT(1) NOT NULL DEFAULT 0, dosage_form VARCHAR(80) NULL, strength VARCHAR(80) NULL, schedule VARCHAR(20) NULL, cost_price DECIMAL(12,2) DEFAULT 0, selling_price DECIMAL(12,2) DEFAULT 0, stock_qty DECIMAL(12,3) DEFAULT 0, min_stock DECIMAL(12,3) DEFAULT 0, active TINYINT(1) DEFAULT 1);
CREATE TABLE IF NOT EXISTS stock_movements (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 product_id BIGINT UNSIGNED NOT NULL,
 movement_type ENUM('opening','purchase','sale','adjustment','return','wastage','transfer') NOT NULL,
 qty DECIMAL(12,3) NOT NULL,
 reference_type VARCHAR(60) NULL,
 reference_id BIGINT UNSIGNED NULL,
 batch_id BIGINT UNSIGNED NULL,
 notes TEXT NULL,
 created_by INT UNSIGNED NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(product_id) REFERENCES products(id) ON DELETE RESTRICT,
 FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL,
 INDEX idx_stock_movements_product_date(product_id,created_at),
 INDEX idx_stock_movements_reference(reference_type,reference_id)
);
CREATE TABLE IF NOT EXISTS consultation_items (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 consultation_id BIGINT UNSIGNED NOT NULL,
 product_id BIGINT UNSIGNED NOT NULL,
 item_type ENUM('product','service','medication') DEFAULT 'product',
 qty DECIMAL(12,3) NOT NULL DEFAULT 1,
 dosage VARCHAR(255) NULL,
 dosage_form VARCHAR(80) NULL,
 strength VARCHAR(80) NULL,
 repeat_count INT NOT NULL DEFAULT 0,
 unit_price DECIMAL(12,2) NOT NULL DEFAULT 0,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(consultation_id) REFERENCES consultations(id) ON DELETE CASCADE,
 FOREIGN KEY(product_id) REFERENCES products(id) ON DELETE RESTRICT,
 INDEX idx_consultation_items_consultation(consultation_id)
);
CREATE TABLE IF NOT EXISTS invoices (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, client_id BIGINT UNSIGNED NOT NULL, patient_id BIGINT UNSIGNED NULL, consultation_id BIGINT UNSIGNED NULL, invoice_no VARCHAR(50) UNIQUE NOT NULL, status ENUM('draft','unpaid','partial','paid','void') DEFAULT 'unpaid', on_account TINYINT(1) NOT NULL DEFAULT 0, subtotal DECIMAL(12,2) DEFAULT 0, tax DECIMAL(12,2) DEFAULT 0, total DECIMAL(12,2) DEFAULT 0, issued_at DATETIME DEFAULT CURRENT_TIMESTAMP, due_at DATETIME NULL, discount_percent DECIMAL(5,2) NOT NULL DEFAULT 0, discount_amount DECIMAL(12,2) NOT NULL DEFAULT 0, FOREIGN KEY(client_id) REFERENCES clients(id), FOREIGN KEY(patient_id) REFERENCES patients(id) ON DELETE SET NULL, FOREIGN KEY(consultation_id) REFERENCES consultations(id) ON DELETE SET NULL);
CREATE TABLE IF NOT EXISTS invoice_items (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, invoice_id BIGINT UNSIGNED NOT NULL, product_id BIGINT UNSIGNED NULL, description VARCHAR(255) NOT NULL, qty DECIMAL(12,3) DEFAULT 1, unit_price DECIMAL(12,2) DEFAULT 0, line_total DECIMAL(12,2) DEFAULT 0, FOREIGN KEY(invoice_id) REFERENCES invoices(id) ON DELETE CASCADE, FOREIGN KEY(product_id) REFERENCES products(id) ON DELETE SET NULL);
CREATE TABLE IF NOT EXISTS payments (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, invoice_id BIGINT UNSIGNED NOT NULL, payment_method ENUM('cash','card','eft','online','account') NOT NULL, amount DECIMAL(12,2) NOT NULL, reference VARCHAR(120), paid_at DATETIME DEFAULT CURRENT_TIMESTAMP, received_by INT UNSIGNED NULL, FOREIGN KEY(invoice_id) REFERENCES invoices(id), FOREIGN KEY(received_by) REFERENCES users(id));
CREATE TABLE IF NOT EXISTS client_payments (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, client_id BIGINT UNSIGNED NOT NULL, payment_method ENUM('cash','card','eft','online') NOT NULL, amount DECIMAL(12,2) NOT NULL, reference VARCHAR(120) NULL, paid_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, received_by INT UNSIGNED NULL, notes TEXT NULL, FOREIGN KEY(client_id) REFERENCES clients(id) ON DELETE RESTRICT, FOREIGN KEY(received_by) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_client_payments_client_date(client_id,paid_at));
CREATE TABLE IF NOT EXISTS client_payment_arrangements (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, client_id BIGINT UNSIGNED NOT NULL, status ENUM('active','completed','cancelled') NOT NULL DEFAULT 'active', arrangement_date DATE NOT NULL, first_payment_date DATE NULL, payment_value DECIMAL(12,2) NOT NULL DEFAULT 0, frequency VARCHAR(40) NULL, next_payment_date DATE NULL, end_date DATE NULL, details TEXT NULL, comments TEXT NULL, created_by INT UNSIGNED NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NULL, FOREIGN KEY(client_id) REFERENCES clients(id) ON DELETE CASCADE, FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_cpa_client_status(client_id,status));
CREATE TABLE IF NOT EXISTS client_profile_comments (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, client_id BIGINT UNSIGNED NOT NULL, section VARCHAR(50) NOT NULL DEFAULT 'General Account', comment TEXT NOT NULL, created_by INT UNSIGNED NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(client_id) REFERENCES clients(id) ON DELETE CASCADE, FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_cpc_client_date(client_id,created_at));
CREATE TABLE IF NOT EXISTS client_account_adjustments (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 client_id BIGINT UNSIGNED NOT NULL,
 invoice_id BIGINT UNSIGNED NULL,
 adjustment_no VARCHAR(50) NOT NULL UNIQUE,
 adjustment_type ENUM('credit_note','debit_note','write_off') NOT NULL,
 amount DECIMAL(12,2) NOT NULL,
 reason VARCHAR(255) NOT NULL,
 notes TEXT NULL,
 created_by INT UNSIGNED NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(client_id) REFERENCES clients(id) ON DELETE RESTRICT,
 FOREIGN KEY(invoice_id) REFERENCES invoices(id) ON DELETE SET NULL,
 FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL,
 INDEX idx_client_adjustments_client_date(client_id,created_at),
 INDEX idx_client_adjustments_invoice(invoice_id)
);
CREATE TABLE IF NOT EXISTS payment_allocations (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, client_payment_id BIGINT UNSIGNED NOT NULL, invoice_id BIGINT UNSIGNED NOT NULL, amount DECIMAL(12,2) NOT NULL, allocated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, allocated_by INT UNSIGNED NULL, FOREIGN KEY(client_payment_id) REFERENCES client_payments(id) ON DELETE CASCADE, FOREIGN KEY(invoice_id) REFERENCES invoices(id) ON DELETE RESTRICT, FOREIGN KEY(allocated_by) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_payment_allocations_invoice(invoice_id), INDEX idx_payment_allocations_payment(client_payment_id));
CREATE TABLE IF NOT EXISTS cashups (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id INT UNSIGNED NOT NULL, business_date DATE NOT NULL, opening_float DECIMAL(12,2) DEFAULT 0, expected_cash DECIMAL(12,2) DEFAULT 0, actual_cash DECIMAL(12,2) DEFAULT 0, variance DECIMAL(12,2) DEFAULT 0, status ENUM('open','closed','approved') DEFAULT 'open', notes TEXT, count_5 DECIMAL(12,2) NOT NULL DEFAULT 0, count_10 DECIMAL(12,2) NOT NULL DEFAULT 0, count_20 DECIMAL(12,2) NOT NULL DEFAULT 0, count_50 DECIMAL(12,2) NOT NULL DEFAULT 0, count_100 DECIMAL(12,2) NOT NULL DEFAULT 0, count_200 DECIMAL(12,2) NOT NULL DEFAULT 0, count_coins DECIMAL(12,2) NOT NULL DEFAULT 0, coin_r5 DECIMAL(12,2) NOT NULL DEFAULT 0, coin_r2 DECIMAL(12,2) NOT NULL DEFAULT 0, coin_r1 DECIMAL(12,2) NOT NULL DEFAULT 0, coin_50c DECIMAL(12,2) NOT NULL DEFAULT 0, coin_20c DECIMAL(12,2) NOT NULL DEFAULT 0, coin_10c DECIMAL(12,2) NOT NULL DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(user_id) REFERENCES users(id));
CREATE TABLE IF NOT EXISTS bankings (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, business_date DATE NOT NULL, method VARCHAR(30) NOT NULL, amount DECIMAL(12,2) NOT NULL, reference VARCHAR(120), banked_at DATETIME NULL, status ENUM('pending','banked','reconciled') DEFAULT 'pending', created_by INT UNSIGNED NULL, FOREIGN KEY(created_by) REFERENCES users(id));
CREATE TABLE IF NOT EXISTS audit_logs (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id INT UNSIGNED NULL, action VARCHAR(100) NOT NULL, entity_type VARCHAR(80), entity_id BIGINT UNSIGNED NULL, details JSON NULL, ip_address VARCHAR(45), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL);
INSERT IGNORE INTO roles(name,description) VALUES ('Practice Manager','Full management access'),('Reception','Front desk and billing'),('Veterinarian','Clinical access'),('Vet Technician','Clinical support'),('Super User','System owner with full configuration access');
INSERT IGNORE INTO permissions(name) VALUES ('dashboard.view'),('clients.manage'),('patients.manage'),('appointments.manage'),('consultations.manage'),('billing.manage'),('payments.manage'),('cashup.manage'),('banking.manage'),('stock.manage'),('reports.view'),('users.manage'),('settings.manage'),('admin.view');
INSERT IGNORE INTO permissions(name) VALUES ('menu.dashboard'),('menu.clinical'),('menu.clinical.appointments'),('menu.clinical.patients'),('menu.clinical.clients'),('menu.clinical.consultations'),('menu.clinical.prescriptions'),('menu.clinical.routine_meds'),('menu.clinical.otc'),('menu.clinical.vaccinations'),('menu.clinical.hospital'),('menu.clinical.reports'),('menu.accounting'),('menu.accounting.billing'),('menu.accounting.notes'),('menu.accounting.cashup'),('menu.accounting.day_end'),('menu.accounting.banking'),('menu.accounting.debtors'),('menu.accounting.clinical_use'),('menu.accounting.reports'),('menu.inventory'),('menu.inventory.stock'),('menu.inventory.purchase_orders'),('menu.inventory.grv'),('menu.inventory.credit_notes'),('menu.inventory.suppliers'),('menu.inventory.procedures'),('menu.inventory.categories'),('menu.inventory.reports'),('menu.admin'),('menu.admin.portal'),('menu.admin.reminders'),('menu.admin.users'),('menu.admin.permissions'),('menu.admin.month_end');
INSERT IGNORE INTO role_permissions SELECT r.id,p.id FROM roles r CROSS JOIN permissions p WHERE r.name IN ('Practice Manager','Super User');
INSERT IGNORE INTO role_permissions SELECT r.id,p.id FROM roles r CROSS JOIN permissions p WHERE r.name IN ('Practice Manager','Super User') AND p.name LIKE 'menu.%';
INSERT IGNORE INTO role_permissions SELECT r.id,p.id FROM roles r JOIN permissions p ON p.name IN ('menu.dashboard','menu.clinical','menu.clinical.appointments','menu.clinical.patients','menu.clinical.clients','menu.accounting','menu.accounting.billing','menu.accounting.notes') WHERE r.name='Reception';
INSERT IGNORE INTO role_permissions SELECT r.id,p.id FROM roles r JOIN permissions p ON p.name IN ('menu.dashboard','menu.clinical','menu.clinical.patients','menu.clinical.appointments','menu.clinical.consultations','menu.clinical.prescriptions','menu.clinical.routine_meds','menu.clinical.otc','menu.clinical.vaccinations','menu.clinical.hospital','menu.clinical.reports','menu.inventory','menu.inventory.stock','menu.inventory.procedures','menu.inventory.reports') WHERE r.name='Veterinarian';
INSERT IGNORE INTO role_permissions SELECT r.id,p.id FROM roles r JOIN permissions p ON p.name IN ('menu.dashboard','menu.clinical','menu.clinical.patients','menu.clinical.appointments','menu.clinical.consultations','menu.clinical.prescriptions','menu.clinical.routine_meds','menu.clinical.otc','menu.clinical.vaccinations','menu.clinical.hospital','menu.clinical.reports','menu.inventory','menu.inventory.stock','menu.inventory.procedures','menu.inventory.reports','menu.accounting','menu.accounting.clinical_use') WHERE r.name='Vet Technician';
INSERT INTO role_permissions SELECT r.id,p.id FROM roles r JOIN permissions p ON p.name IN ('dashboard.view','clients.manage','patients.manage','appointments.manage','billing.manage','payments.manage','cashup.manage') WHERE r.name='Reception';
INSERT INTO role_permissions SELECT r.id,p.id FROM roles r JOIN permissions p ON p.name IN ('dashboard.view','patients.manage','appointments.manage','consultations.manage','admin.view') WHERE r.name='Veterinarian';
INSERT INTO role_permissions SELECT r.id,p.id FROM roles r JOIN permissions p ON p.name IN ('dashboard.view','patients.manage','appointments.manage','consultations.manage','stock.manage') WHERE r.name='Vet Technician';
INSERT INTO appointment_types(name,duration_minutes,colour) VALUES ('Consultation',30,'#0f766e'),('Vaccination',20,'#2563eb'),('Follow-up',20,'#7c3aed'),('Surgery',120,'#dc2626'),('Emergency',45,'#ea580c');
INSERT IGNORE INTO users(role_id,name,email,password_hash) VALUES ((SELECT id FROM roles WHERE name='Super User'),'System Administrator','admin@vetservepro.local','$2y$12$NPuXUseb8NREBKcpYgJQYOqk7oqJBahzp2KWfPE4zhNq3D5vLNqVe');
INSERT INTO products(sku,name,category,cost_price,selling_price,stock_qty,min_stock) VALUES
('SVC-CONSULT','Standard Consultation','Service',0,450,0,0),
('SVC-VACCINE','Vaccination Service','Service',0,180,0,0),
('SVC-FOLLOW','Follow-up Consultation','Service',0,300,0,0),
('SVC-EAR','Ear Examination & Cleaning','Service',0,330,0,0),
('SVC-INJ','Injection Administration','Service',0,150,0,0),
('MED-DEMO','Demo Medication','Medication',80,150,20,5)
ON DUPLICATE KEY UPDATE name=VALUES(name),selling_price=VALUES(selling_price);

CREATE TABLE IF NOT EXISTS diagnoses (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(150) NOT NULL UNIQUE,
 code VARCHAR(40) NULL,
 description TEXT NULL,
 category VARCHAR(100) NULL,
 follow_up_required TINYINT(1) NOT NULL DEFAULT 0,
 active TINYINT(1) DEFAULT 1
);
CREATE TABLE IF NOT EXISTS treatments (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(150) NOT NULL UNIQUE,
 description TEXT NULL,
 default_price DECIMAL(12,2) DEFAULT 0,
 active TINYINT(1) DEFAULT 1
);
CREATE TABLE IF NOT EXISTS consultation_diagnoses (
 consultation_id BIGINT UNSIGNED NOT NULL,
 diagnosis_id INT UNSIGNED NOT NULL,
 notes TEXT NULL,
 PRIMARY KEY(consultation_id,diagnosis_id),
 FOREIGN KEY(consultation_id) REFERENCES consultations(id) ON DELETE CASCADE,
 FOREIGN KEY(diagnosis_id) REFERENCES diagnoses(id) ON DELETE RESTRICT
);
CREATE TABLE IF NOT EXISTS consultation_treatments (
 consultation_id BIGINT UNSIGNED NOT NULL,
 treatment_id INT UNSIGNED NOT NULL,
 qty DECIMAL(12,3) DEFAULT 1,
 notes TEXT NULL,
 PRIMARY KEY(consultation_id,treatment_id),
 FOREIGN KEY(consultation_id) REFERENCES consultations(id) ON DELETE CASCADE,
 FOREIGN KEY(treatment_id) REFERENCES treatments(id) ON DELETE RESTRICT
);
CREATE TABLE IF NOT EXISTS protocols (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(150) NOT NULL UNIQUE,
 description TEXT NULL,
 active TINYINT(1) DEFAULT 1
);
CREATE TABLE IF NOT EXISTS protocol_items (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 protocol_id INT UNSIGNED NOT NULL,
 item_type ENUM('product','treatment','diagnosis') NOT NULL,
 item_id BIGINT UNSIGNED NOT NULL,
 qty DECIMAL(12,3) DEFAULT 1,
 FOREIGN KEY(protocol_id) REFERENCES protocols(id) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS vaccinations (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 patient_id BIGINT UNSIGNED NOT NULL,
 vaccine_name VARCHAR(150) NOT NULL,
 batch_no VARCHAR(80) NULL,
 administered_at DATE NOT NULL,
 next_due_date DATE NULL,
 administered_by INT UNSIGNED NULL,
 notes TEXT NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(patient_id) REFERENCES patients(id) ON DELETE CASCADE,
 FOREIGN KEY(administered_by) REFERENCES users(id) ON DELETE SET NULL
);
CREATE TABLE IF NOT EXISTS reminders (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 patient_id BIGINT UNSIGNED NOT NULL,
 client_id BIGINT UNSIGNED NOT NULL,
 reminder_type ENUM('vaccination','follow_up','payment','appointment','custom') NOT NULL,
 due_date DATE NOT NULL,
 title VARCHAR(180) NOT NULL,
 message TEXT NULL,
 status ENUM('pending','sent','completed','cancelled') DEFAULT 'pending',
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(patient_id) REFERENCES patients(id) ON DELETE CASCADE,
 FOREIGN KEY(client_id) REFERENCES clients(id) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS prescriptions (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 prescription_no VARCHAR(60) UNIQUE NOT NULL,
 consultation_id BIGINT UNSIGNED NOT NULL,
 patient_id BIGINT UNSIGNED NOT NULL,
 prescribed_by INT UNSIGNED NOT NULL,
 instructions TEXT NULL,
 signed_by INT UNSIGNED NULL,
 signed_at DATETIME NULL,
 issued_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 status ENUM('active','completed','cancelled') DEFAULT 'active',
 FOREIGN KEY(consultation_id) REFERENCES consultations(id) ON DELETE CASCADE,
 FOREIGN KEY(appointment_id) REFERENCES appointments(id) ON DELETE SET NULL,
 FOREIGN KEY(patient_id) REFERENCES patients(id) ON DELETE CASCADE,
 FOREIGN KEY(prescribed_by) REFERENCES users(id) ON DELETE RESTRICT
);
CREATE TABLE IF NOT EXISTS prescription_items (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 prescription_id BIGINT UNSIGNED NOT NULL,
 product_id BIGINT UNSIGNED NULL,
 medication_name VARCHAR(180) NOT NULL,
 dosage_form VARCHAR(80) NULL,
 strength VARCHAR(80) NULL,
 dosage VARCHAR(255) NULL,
 frequency VARCHAR(120) NULL,
 duration VARCHAR(120) NULL,
 quantity DECIMAL(12,3) NULL,
 instructions TEXT NULL,
 repeat_count INT NOT NULL DEFAULT 0,
 FOREIGN KEY(prescription_id) REFERENCES prescriptions(id) ON DELETE CASCADE,
 FOREIGN KEY(product_id) REFERENCES products(id) ON DELETE SET NULL
);
CREATE TABLE IF NOT EXISTS prescription_dispensings (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 prescription_id BIGINT UNSIGNED NOT NULL,
 patient_id BIGINT UNSIGNED NOT NULL,
 client_id BIGINT UNSIGNED NOT NULL,
 invoice_id BIGINT UNSIGNED NULL,
 dispensed_by INT UNSIGNED NULL,
 dispensed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 notes TEXT NULL,
 FOREIGN KEY(prescription_id) REFERENCES prescriptions(id) ON DELETE RESTRICT,
 FOREIGN KEY(patient_id) REFERENCES patients(id) ON DELETE RESTRICT,
 FOREIGN KEY(client_id) REFERENCES clients(id) ON DELETE RESTRICT,
 FOREIGN KEY(invoice_id) REFERENCES invoices(id) ON DELETE SET NULL,
 FOREIGN KEY(dispensed_by) REFERENCES users(id) ON DELETE SET NULL,
 INDEX idx_prescription_dispensings_prescription(prescription_id),
 INDEX idx_prescription_dispensings_patient(patient_id),
 INDEX idx_prescription_dispensings_invoice(invoice_id)
);
CREATE TABLE IF NOT EXISTS prescription_dispensing_items (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 dispensing_id BIGINT UNSIGNED NOT NULL,
 prescription_item_id BIGINT UNSIGNED NOT NULL,
 product_id BIGINT UNSIGNED NULL,
 quantity DECIMAL(12,3) NOT NULL,
 unit_price DECIMAL(12,2) NOT NULL DEFAULT 0,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(dispensing_id) REFERENCES prescription_dispensings(id) ON DELETE CASCADE,
 FOREIGN KEY(prescription_item_id) REFERENCES prescription_items(id) ON DELETE RESTRICT,
 FOREIGN KEY(product_id) REFERENCES products(id) ON DELETE SET NULL,
 INDEX idx_prescription_dispensing_items_dispensing(dispensing_id),
 INDEX idx_prescription_dispensing_items_prescription_item(prescription_item_id)
);
CREATE TABLE IF NOT EXISTS follow_up_cards (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 consultation_id BIGINT UNSIGNED NOT NULL UNIQUE,
 patient_id BIGINT UNSIGNED NOT NULL,
 client_id BIGINT UNSIGNED NOT NULL,
 follow_up_date DATE NOT NULL,
 follow_up_time TIME NULL,
 reason VARCHAR(255) NULL,
 appointment_id BIGINT UNSIGNED NULL,
 notes TEXT NULL,
 created_by INT UNSIGNED NULL,
 status ENUM('scheduled','completed','cancelled') DEFAULT 'scheduled',
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(consultation_id) REFERENCES consultations(id) ON DELETE CASCADE,
 FOREIGN KEY(appointment_id) REFERENCES appointments(id) ON DELETE SET NULL,
 FOREIGN KEY(patient_id) REFERENCES patients(id) ON DELETE CASCADE,
 FOREIGN KEY(client_id) REFERENCES clients(id) ON DELETE CASCADE,
 FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL
);
CREATE TABLE IF NOT EXISTS communications (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 client_id BIGINT UNSIGNED NULL,
 patient_id BIGINT UNSIGNED NULL,
 channel ENUM('whatsapp','email','sms','system') NOT NULL,
 direction ENUM('outbound','inbound') DEFAULT 'outbound',
 template_name VARCHAR(120) NULL,
 recipient VARCHAR(190) NULL,
 subject VARCHAR(255) NULL,
 message TEXT NOT NULL,
 status ENUM('queued','sent','delivered','failed') DEFAULT 'queued',
 provider_message_id VARCHAR(190) NULL,
 sent_by INT UNSIGNED NULL,
 sent_at DATETIME NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(client_id) REFERENCES clients(id) ON DELETE SET NULL,
 FOREIGN KEY(patient_id) REFERENCES patients(id) ON DELETE SET NULL,
 FOREIGN KEY(sent_by) REFERENCES users(id) ON DELETE SET NULL,
 INDEX idx_communications_client_date(client_id,created_at)
);
CREATE TABLE IF NOT EXISTS system_settings (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 setting_key VARCHAR(120) NOT NULL UNIQUE,
 setting_value TEXT NULL,
 updated_by INT UNSIGNED NULL,
 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 FOREIGN KEY(updated_by) REFERENCES users(id) ON DELETE SET NULL
);
CREATE TABLE IF NOT EXISTS suppliers (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(180) NOT NULL,
 contact_person VARCHAR(120),
 phone VARCHAR(40),
 email VARCHAR(190),
 account_no VARCHAR(80),
 notes TEXT,
 active TINYINT(1) DEFAULT 1,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS stock_batches (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 product_id BIGINT UNSIGNED NOT NULL,
 supplier_id BIGINT UNSIGNED NULL,
 batch_no VARCHAR(80),
 expiry_date DATE NULL,
 qty_received DECIMAL(12,3) DEFAULT 0,
 qty_remaining DECIMAL(12,3) DEFAULT 0,
 cost_price DECIMAL(12,2) DEFAULT 0,
 received_at DATE NOT NULL,
 FOREIGN KEY(product_id) REFERENCES products(id) ON DELETE CASCADE,
 FOREIGN KEY(supplier_id) REFERENCES suppliers(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS inventory_categories (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(100) NOT NULL UNIQUE,
 description VARCHAR(255) NULL,
 allow_prescriptions TINYINT(1) NOT NULL DEFAULT 1,
 allow_routine_meds TINYINT(1) NOT NULL DEFAULT 1,
 allow_otc TINYINT(1) NOT NULL DEFAULT 0,
 allow_vaccinations TINYINT(1) NOT NULL DEFAULT 0,
 allow_procedures TINYINT(1) NOT NULL DEFAULT 1,
 allow_hospital TINYINT(1) NOT NULL DEFAULT 1,
 allow_send_home TINYINT(1) NOT NULL DEFAULT 1,
 active TINYINT(1) NOT NULL DEFAULT 1,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS purchase_orders (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 supplier_id BIGINT UNSIGNED NOT NULL,
 po_no VARCHAR(60) UNIQUE NOT NULL,
 status ENUM('draft','ordered','part_received','received','cancelled') DEFAULT 'draft',
 order_date DATE NOT NULL,
 expected_date DATE NULL,
 notes TEXT NULL,
 created_by INT UNSIGNED NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(supplier_id) REFERENCES suppliers(id),
 FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL
);
CREATE TABLE IF NOT EXISTS purchase_order_items (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 purchase_order_id BIGINT UNSIGNED NOT NULL,
 product_id BIGINT UNSIGNED NOT NULL,
 qty DECIMAL(12,3) NOT NULL,
 unit_cost DECIMAL(12,2) NOT NULL,
 received_qty DECIMAL(12,3) DEFAULT 0,
 FOREIGN KEY(purchase_order_id) REFERENCES purchase_orders(id) ON DELETE CASCADE,
 FOREIGN KEY(product_id) REFERENCES products(id)
);
CREATE TABLE IF NOT EXISTS grvs (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 grv_no VARCHAR(60) UNIQUE NOT NULL,
 purchase_order_id BIGINT UNSIGNED NULL,
 supplier_id BIGINT UNSIGNED NOT NULL,
 supplier_invoice_no VARCHAR(100) NULL,
 invoice_date DATE NULL,
 invoice_file_path VARCHAR(500) NULL,
 received_date DATE NOT NULL,
 status ENUM('draft','posted','cancelled') NOT NULL DEFAULT 'draft',
 notes TEXT NULL,
 created_by INT UNSIGNED NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 posted_at DATETIME NULL,
 posted_by INT UNSIGNED NULL,
 FOREIGN KEY(purchase_order_id) REFERENCES purchase_orders(id) ON DELETE SET NULL,
 FOREIGN KEY(supplier_id) REFERENCES suppliers(id) ON DELETE RESTRICT,
 FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL,
 FOREIGN KEY(posted_by) REFERENCES users(id) ON DELETE SET NULL,
 INDEX idx_grv_supplier_date(supplier_id,received_date),
 INDEX idx_grv_po(purchase_order_id)
);
CREATE TABLE IF NOT EXISTS grv_items (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 grv_id BIGINT UNSIGNED NOT NULL,
 purchase_order_item_id BIGINT UNSIGNED NULL,
 product_id BIGINT UNSIGNED NOT NULL,
 qty_received DECIMAL(12,3) NOT NULL,
 unit_cost DECIMAL(12,2) NOT NULL DEFAULT 0,
 batch_no VARCHAR(80) NULL,
 expiry_date DATE NULL,
 FOREIGN KEY(grv_id) REFERENCES grvs(id) ON DELETE CASCADE,
 FOREIGN KEY(purchase_order_item_id) REFERENCES purchase_order_items(id) ON DELETE SET NULL,
 FOREIGN KEY(product_id) REFERENCES products(id) ON DELETE RESTRICT
);
CREATE TABLE IF NOT EXISTS supplier_credit_notes (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 credit_note_no VARCHAR(60) UNIQUE NOT NULL,
 supplier_id BIGINT UNSIGNED NOT NULL,
 grv_id BIGINT UNSIGNED NULL,
 supplier_credit_no VARCHAR(100) NULL,
 credit_date DATE NOT NULL,
 document_file_path VARCHAR(500) NULL,
 status ENUM('draft','posted','cancelled') NOT NULL DEFAULT 'draft',
 notes TEXT NULL,
 created_by INT UNSIGNED NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 posted_at DATETIME NULL,
 posted_by INT UNSIGNED NULL,
 FOREIGN KEY(supplier_id) REFERENCES suppliers(id) ON DELETE RESTRICT,
 FOREIGN KEY(grv_id) REFERENCES grvs(id) ON DELETE SET NULL,
 FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL,
 FOREIGN KEY(posted_by) REFERENCES users(id) ON DELETE SET NULL,
 INDEX idx_credit_supplier_date(supplier_id,credit_date)
);
CREATE TABLE IF NOT EXISTS supplier_credit_note_items (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 credit_note_id BIGINT UNSIGNED NOT NULL,
 product_id BIGINT UNSIGNED NOT NULL,
 qty DECIMAL(12,3) NOT NULL,
 unit_cost DECIMAL(12,2) NOT NULL DEFAULT 0,
 reason VARCHAR(255) NULL,
 FOREIGN KEY(credit_note_id) REFERENCES supplier_credit_notes(id) ON DELETE CASCADE,
 FOREIGN KEY(product_id) REFERENCES products(id) ON DELETE RESTRICT
);
CREATE TABLE IF NOT EXISTS hospital_cases (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 patient_id BIGINT UNSIGNED NOT NULL,
 vet_id INT UNSIGNED NULL,
 kennel VARCHAR(40),
 admitted_at DATETIME NOT NULL,
 expected_discharge DATE NULL,
 discharged_at DATETIME NULL,
 status ENUM('admitted','stable','critical','ready_discharge','discharged') DEFAULT 'admitted',
 notes TEXT,
 FOREIGN KEY(patient_id) REFERENCES patients(id),
 FOREIGN KEY(vet_id) REFERENCES users(id) ON DELETE SET NULL
);
CREATE TABLE IF NOT EXISTS hospital_tasks (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 hospital_case_id BIGINT UNSIGNED NOT NULL,
 task_type VARCHAR(100) NOT NULL,
 due_at DATETIME NOT NULL,
 completed_at DATETIME NULL,
 completed_by INT UNSIGNED NULL,
 notes TEXT,
 FOREIGN KEY(hospital_case_id) REFERENCES hospital_cases(id) ON DELETE CASCADE,
 FOREIGN KEY(completed_by) REFERENCES users(id) ON DELETE SET NULL
);

INSERT IGNORE INTO diagnoses(name,code,description) VALUES
('Otitis externa','DX-EAR','External ear inflammation'),('Dermatitis','DX-SKIN','Skin inflammation'),('Gastroenteritis','DX-GI','Gastrointestinal inflammation'),('Dental disease','DX-DENT','Dental/periodontal disease'),('Routine wellness','DX-WELL','Routine wellness examination');
INSERT IGNORE INTO treatments(name,default_price,description) VALUES
('General Consultation',450,'Standard veterinary consultation'),('Follow-up Consultation',300,'Follow-up visit'),('Wound Care',250,'Basic wound cleaning and dressing'),('Ear Cleaning',180,'Ear cleaning procedure'),('Dental Scale & Polish',1200,'Dental procedure');
INSERT IGNORE INTO protocols(name,description) VALUES
('Annual Wellness','Consultation, vaccination review and preventive care'),('Ear Infection','Clinical ear examination and treatment workflow'),('New Puppy','Initial examination, vaccination and deworming workflow');

ALTER TABLE stock_movements
  ADD CONSTRAINT fk_stock_movements_batch
  FOREIGN KEY (batch_id) REFERENCES stock_batches(id) ON DELETE SET NULL;

INSERT IGNORE INTO system_settings(setting_key,setting_value) VALUES
('practice_name','VET Serve Pro Demo Practice'),
('practice_registration_no',''),
('tax_rate','15.00'),
('vat_registered','0'),
('currency','ZAR'),
('timezone','Africa/Johannesburg'),
('practice_logo',''),
('statement_terms','Payment is due according to the practice account terms.'),
('bank_name',''),
('bank_account_name',''),
('bank_account_number',''),
('bank_branch_code',''),
('bank_account_type',''),
('bank_payment_reference','');



CREATE TABLE IF NOT EXISTS routine_procedures (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(180) NOT NULL UNIQUE,
 description TEXT NULL,
 base_fee DECIMAL(12,2) NOT NULL DEFAULT 0,
 active TINYINT(1) NOT NULL DEFAULT 1,
 created_by INT UNSIGNED NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS routine_procedure_medications (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 routine_procedure_id BIGINT UNSIGNED NOT NULL,
 product_id BIGINT UNSIGNED NOT NULL,
 included_qty DECIMAL(12,3) NOT NULL DEFAULT 1,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(routine_procedure_id) REFERENCES routine_procedures(id) ON DELETE CASCADE,
 FOREIGN KEY(product_id) REFERENCES products(id) ON DELETE RESTRICT,
 UNIQUE KEY uq_routine_procedure_product(routine_procedure_id,product_id),
 INDEX idx_rpm_procedure(routine_procedure_id),
 INDEX idx_rpm_product(product_id)
) ENGINE=InnoDB;
INSERT IGNORE INTO routine_procedures(name,description,base_fee,active)
SELECT name,description,default_price,active FROM treatments;
CREATE TABLE IF NOT EXISTS hospital_admissions (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 admission_no VARCHAR(50) NOT NULL UNIQUE,
 patient_id BIGINT UNSIGNED NOT NULL,
 client_id BIGINT UNSIGNED NOT NULL,
 consultation_id BIGINT UNSIGNED NULL,
 admission_type ENUM('hospital','routine_procedure') NOT NULL DEFAULT 'hospital',
 routine_procedure_id BIGINT UNSIGNED NULL,
 procedure_name VARCHAR(255) NULL,
 procedure_fee DECIMAL(12,2) NULL,
 invoice_id BIGINT UNSIGNED NULL,
 admitted_by INT UNSIGNED NULL,
 attending_vet_id INT UNSIGNED NULL,
 admitted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 expected_discharge_at DATETIME NULL,
 status ENUM('active','discharged') NOT NULL DEFAULT 'active',
 ward VARCHAR(100) NULL,
 kennel VARCHAR(100) NULL,
 reason TEXT NULL,
 diagnosis TEXT NULL,
 initial_assessment TEXT NULL,
 care_plan TEXT NULL,
 feeding_plan TEXT NULL,
 monitoring_plan TEXT NULL,
 allergies_alerts TEXT NULL,
 discharge_at DATETIME NULL,
 discharge_notes TEXT NULL,
 discharged_by INT UNSIGNED NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 FOREIGN KEY(patient_id) REFERENCES patients(id),
 FOREIGN KEY(client_id) REFERENCES clients(id),
 FOREIGN KEY(consultation_id) REFERENCES consultations(id) ON DELETE SET NULL,
 FOREIGN KEY(invoice_id) REFERENCES invoices(id) ON DELETE SET NULL,
 FOREIGN KEY(routine_procedure_id) REFERENCES routine_procedures(id) ON DELETE SET NULL,
 FOREIGN KEY(admitted_by) REFERENCES users(id) ON DELETE SET NULL,
 FOREIGN KEY(attending_vet_id) REFERENCES users(id) ON DELETE SET NULL,
 FOREIGN KEY(discharged_by) REFERENCES users(id) ON DELETE SET NULL,
 INDEX idx_hospital_status(status),
 INDEX idx_hospital_patient(patient_id),
 INDEX idx_hospital_admitted(admitted_at),
 INDEX idx_hospital_invoice(invoice_id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS hospital_admission_diagnoses (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 admission_id BIGINT UNSIGNED NOT NULL,
 diagnosis_id INT UNSIGNED NOT NULL,
 notes TEXT NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_hospital_diagnosis (admission_id,diagnosis_id),
 FOREIGN KEY (admission_id) REFERENCES hospital_admissions(id) ON DELETE CASCADE,
 FOREIGN KEY (diagnosis_id) REFERENCES diagnoses(id) ON DELETE RESTRICT
);

CREATE TABLE IF NOT EXISTS hospital_treatments (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 admission_id BIGINT UNSIGNED NOT NULL,
 treatment_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 treatment_type VARCHAR(100) NOT NULL,
 medication_name VARCHAR(150) NULL,
 dosage_form VARCHAR(80) NULL,
 strength VARCHAR(80) NULL,
 dose VARCHAR(100) NULL,
 route VARCHAR(80) NULL,
 frequency VARCHAR(100) NULL,
 instructions TEXT NULL,
 quantity DECIMAL(12,3) NULL,
 administered_by INT UNSIGNED NULL,
 status ENUM('planned','administered','held','cancelled') NOT NULL DEFAULT 'planned',
 notes TEXT NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(admission_id) REFERENCES hospital_admissions(id) ON DELETE CASCADE,
 FOREIGN KEY(administered_by) REFERENCES users(id) ON DELETE SET NULL,
 INDEX idx_hospital_treatment_admission(admission_id,treatment_at)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS hospital_tasks (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 admission_id BIGINT UNSIGNED NOT NULL,
 task VARCHAR(255) NOT NULL,
 due_at DATETIME NULL,
 assigned_to INT UNSIGNED NULL,
 priority ENUM('low','normal','high','urgent') NOT NULL DEFAULT 'normal',
 status ENUM('open','completed','cancelled') NOT NULL DEFAULT 'open',
 completed_at DATETIME NULL,
 completed_by INT UNSIGNED NULL,
 notes TEXT NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(admission_id) REFERENCES hospital_admissions(id) ON DELETE CASCADE,
 FOREIGN KEY(assigned_to) REFERENCES users(id) ON DELETE SET NULL,
 FOREIGN KEY(completed_by) REFERENCES users(id) ON DELETE SET NULL,
 INDEX idx_hospital_task_admission(admission_id,due_at,status)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS hospital_observations (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 admission_id BIGINT UNSIGNED NOT NULL,
 recorded_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 temperature DECIMAL(5,2) NULL,
 weight_kg DECIMAL(7,2) NULL,
 heart_rate INT NULL,
 respiratory_rate INT NULL,
 pain_score VARCHAR(30) NULL,
 hydration VARCHAR(100) NULL,
 notes TEXT NULL,
 recorded_by INT UNSIGNED NULL,
 FOREIGN KEY(admission_id) REFERENCES hospital_admissions(id) ON DELETE CASCADE,
 FOREIGN KEY(recorded_by) REFERENCES users(id) ON DELETE SET NULL,
 INDEX idx_hospital_observation_admission(admission_id,recorded_at)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS day_end_closures (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,business_date DATE NOT NULL UNIQUE,closed_by INT UNSIGNED NOT NULL,closed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,total_invoiced DECIMAL(12,2) NOT NULL DEFAULT 0,cash_total DECIMAL(12,2) NOT NULL DEFAULT 0,card_total DECIMAL(12,2) NOT NULL DEFAULT 0,eft_total DECIMAL(12,2) NOT NULL DEFAULT 0,account_total DECIMAL(12,2) NOT NULL DEFAULT 0,invoice_count INT NOT NULL DEFAULT 0,notes TEXT NULL,FOREIGN KEY(closed_by) REFERENCES users(id));

INSERT IGNORE INTO system_settings(setting_key,setting_value) VALUES ('smtp_host',''),('smtp_port','587'),('smtp_user',''),('smtp_password',''),('smtp_encryption','tls'),('smtp_from_email',''),('smtp_from_name',''),('pdf_binary','');

-- Import / Export migration framework
CREATE TABLE IF NOT EXISTS migration_batches (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 batch_token VARCHAR(64) NOT NULL UNIQUE,
 batch_name VARCHAR(180) NOT NULL,
 status ENUM('preview','running','completed','failed') NOT NULL DEFAULT 'preview',
 created_by INT UNSIGNED NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 completed_at DATETIME NULL,
 summary JSON NULL,
 FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL
);
CREATE TABLE IF NOT EXISTS migration_id_maps (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 batch_id BIGINT UNSIGNED NOT NULL,
 entity VARCHAR(80) NOT NULL,
 source_id VARCHAR(190) NOT NULL,
 target_id BIGINT UNSIGNED NOT NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_migration_map(batch_id,entity,source_id),
 INDEX idx_migration_map_entity(entity,source_id),
 FOREIGN KEY(batch_id) REFERENCES migration_batches(id) ON DELETE CASCADE
);
INSERT IGNORE INTO permissions(name) VALUES ('menu.admin.import_export');
INSERT IGNORE INTO permissions(name) VALUES ('menu.clinical.diagnoses');
INSERT IGNORE INTO permissions(name) VALUES ('menu.admin.idexx');
INSERT IGNORE INTO role_permissions(role_id,permission_id) SELECT r.id,p.id FROM roles r JOIN permissions p ON p.name='menu.clinical.diagnoses' WHERE r.name IN ('Super User','Practice Manager','Veterinarian');
INSERT IGNORE INTO role_permissions(role_id,permission_id) SELECT r.id,p.id FROM roles r JOIN permissions p ON p.name='menu.admin.idexx' WHERE r.name IN ('Super User','Practice Manager');
INSERT IGNORE INTO role_permissions(role_id,permission_id) SELECT r.id,p.id FROM roles r JOIN permissions p ON p.name='menu.admin.import_export' WHERE r.name='Super User';

INSERT IGNORE INTO system_settings(setting_key,setting_value) VALUES ('idexx_username',''),('idexx_password',''),('idexx_request_url','C:\\IDEXX Interlink\\Requests\\'),('idexx_result_url','C:\\IDEXX Interlink\\Results\\Reports\\');