-- BioCare Dent ERP v1.0 - MySQL 8.0+, UTF8MB4
-- Prototype schema; compliance, retention and medical record requirements require review before production.
CREATE DATABASE IF NOT EXISTS biocare_dent CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE biocare_dent;

CREATE TABLE clinics (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(160) NOT NULL, timezone VARCHAR(64) NOT NULL DEFAULT 'Europe/Belgrade',
 phone VARCHAR(40), email VARCHAR(190), address VARCHAR(255),
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;
CREATE TABLE roles (
 id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 code VARCHAR(40) NOT NULL UNIQUE, label VARCHAR(100) NOT NULL
) ENGINE=InnoDB;
CREATE TABLE permissions (
 id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 code VARCHAR(100) NOT NULL UNIQUE, description VARCHAR(255) NOT NULL
) ENGINE=InnoDB;
CREATE TABLE role_permissions (
 role_id SMALLINT UNSIGNED NOT NULL, permission_id SMALLINT UNSIGNED NOT NULL,
 PRIMARY KEY(role_id, permission_id),
 FOREIGN KEY(role_id) REFERENCES roles(id), FOREIGN KEY(permission_id) REFERENCES permissions(id)
) ENGINE=InnoDB;
CREATE TABLE users (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, clinic_id BIGINT UNSIGNED NOT NULL,
 role_id SMALLINT UNSIGNED NOT NULL, full_name VARCHAR(160) NOT NULL,
 email VARCHAR(190) NOT NULL UNIQUE, password_hash VARCHAR(255) NOT NULL,
 phone VARCHAR(40), is_active BOOLEAN NOT NULL DEFAULT TRUE,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(clinic_id) REFERENCES clinics(id), FOREIGN KEY(role_id) REFERENCES roles(id)
) ENGINE=InnoDB;
CREATE TABLE chairs (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, clinic_id BIGINT UNSIGNED NOT NULL,
 name VARCHAR(100) NOT NULL, is_active BOOLEAN NOT NULL DEFAULT TRUE,
 UNIQUE KEY uq_chair_clinic (id, clinic_id), FOREIGN KEY(clinic_id) REFERENCES clinics(id)
) ENGINE=InnoDB;
CREATE TABLE patients (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, clinic_id BIGINT UNSIGNED NOT NULL,
 patient_number VARCHAR(30) NOT NULL, first_name VARCHAR(100) NOT NULL,
 last_name VARCHAR(100) NOT NULL, date_of_birth DATE NULL,
 phone VARCHAR(40), email VARCHAR(190), address VARCHAR(255),
 contact_notes TEXT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 UNIQUE KEY uq_patient_number (clinic_id, patient_number),
 INDEX idx_patient_name (clinic_id, last_name, first_name),
 FOREIGN KEY(clinic_id) REFERENCES clinics(id)
) ENGINE=InnoDB;
CREATE TABLE services (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, clinic_id BIGINT UNSIGNED NOT NULL,
 name VARCHAR(160) NOT NULL, duration_minutes SMALLINT UNSIGNED NOT NULL,
 buffer_minutes SMALLINT UNSIGNED NOT NULL DEFAULT 0,
 price_decimal DECIMAL(12,2) NULL, public_bookable BOOLEAN NOT NULL DEFAULT FALSE,
 is_active BOOLEAN NOT NULL DEFAULT TRUE,
 CHECK(duration_minutes > 0), FOREIGN KEY(clinic_id) REFERENCES clinics(id)
) ENGINE=InnoDB;
CREATE TABLE working_hours (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, clinic_id BIGINT UNSIGNED NOT NULL,
 user_id BIGINT UNSIGNED NULL, weekday TINYINT UNSIGNED NOT NULL,
 opens_at TIME NOT NULL, closes_at TIME NOT NULL,
 CHECK(weekday BETWEEN 1 AND 7), CHECK(opens_at < closes_at),
 FOREIGN KEY(clinic_id) REFERENCES clinics(id), FOREIGN KEY(user_id) REFERENCES users(id)
) ENGINE=InnoDB;
CREATE TABLE schedule_blocks (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, clinic_id BIGINT UNSIGNED NOT NULL,
 chair_id BIGINT UNSIGNED NOT NULL, user_id BIGINT UNSIGNED NULL,
 start_at DATETIME NOT NULL, end_at DATETIME NOT NULL,
 reason VARCHAR(160) NOT NULL, CHECK(start_at < end_at),
 INDEX idx_block_chair_time (chair_id, start_at, end_at),
 FOREIGN KEY(clinic_id) REFERENCES clinics(id), FOREIGN KEY(chair_id) REFERENCES chairs(id),
 FOREIGN KEY(user_id) REFERENCES users(id)
) ENGINE=InnoDB;
CREATE TABLE appointments (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, clinic_id BIGINT UNSIGNED NOT NULL,
 chair_id BIGINT UNSIGNED NOT NULL, patient_id BIGINT UNSIGNED NULL,
 dentist_user_id BIGINT UNSIGNED NULL, service_id BIGINT UNSIGNED NOT NULL,
 starts_at DATETIME NOT NULL, ends_at DATETIME NOT NULL,
 status ENUM('pending','confirmed','arrived','in_progress','completed','cancelled','no_show','expired') NOT NULL DEFAULT 'pending',
 source ENUM('admin','web','phone') NOT NULL DEFAULT 'admin',
 hold_expires_at DATETIME NULL, patient_public_note VARCHAR(500) NULL,
 created_by BIGINT UNSIGNED NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 CHECK(starts_at < ends_at),
 INDEX idx_appointment_chair_interval (chair_id, starts_at, ends_at, status),
 INDEX idx_appointment_patient (patient_id, starts_at),
 INDEX idx_appointment_dentist (dentist_user_id, starts_at),
 FOREIGN KEY(clinic_id) REFERENCES clinics(id), FOREIGN KEY(chair_id) REFERENCES chairs(id),
 FOREIGN KEY(patient_id) REFERENCES patients(id), FOREIGN KEY(dentist_user_id) REFERENCES users(id),
 FOREIGN KEY(service_id) REFERENCES services(id), FOREIGN KEY(created_by) REFERENCES users(id)
) ENGINE=InnoDB;
CREATE TABLE booking_requests (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, clinic_id BIGINT UNSIGNED NOT NULL,
 appointment_id BIGINT UNSIGNED NULL,
 contact_name VARCHAR(160) NOT NULL, contact_phone VARCHAR(40) NOT NULL,
 contact_email VARCHAR(190), request_token_hash CHAR(64) NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(clinic_id) REFERENCES clinics(id),
 FOREIGN KEY(appointment_id) REFERENCES appointments(id)
) ENGINE=InnoDB;
CREATE TABLE patient_medical_profiles (
 patient_id BIGINT UNSIGNED PRIMARY KEY,
 allergies TEXT NULL, medications TEXT NULL, conditions_notes TEXT NULL,
 updated_by BIGINT UNSIGNED NULL, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 FOREIGN KEY(patient_id) REFERENCES patients(id), FOREIGN KEY(updated_by) REFERENCES users(id)
) ENGINE=InnoDB;
CREATE TABLE clinical_encounters (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, clinic_id BIGINT UNSIGNED NOT NULL,
 patient_id BIGINT UNSIGNED NOT NULL, appointment_id BIGINT UNSIGNED NULL,
 dentist_user_id BIGINT UNSIGNED NOT NULL, occurred_at DATETIME NOT NULL,
 clinical_note TEXT NOT NULL, diagnosis_note TEXT NULL, treatment_note TEXT NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 INDEX idx_encounter_patient (patient_id, occurred_at),
 FOREIGN KEY(clinic_id) REFERENCES clinics(id), FOREIGN KEY(patient_id) REFERENCES patients(id),
 FOREIGN KEY(appointment_id) REFERENCES appointments(id),
 FOREIGN KEY(dentist_user_id) REFERENCES users(id)
) ENGINE=InnoDB;
CREATE TABLE procedures (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, encounter_id BIGINT UNSIGNED NOT NULL,
 service_id BIGINT UNSIGNED NULL, tooth_number TINYINT UNSIGNED NULL,
 description TEXT NOT NULL, charge_amount DECIMAL(12,2) NULL,
 FOREIGN KEY(encounter_id) REFERENCES clinical_encounters(id),
 FOREIGN KEY(service_id) REFERENCES services(id)
) ENGINE=InnoDB;
CREATE TABLE patient_documents (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, clinic_id BIGINT UNSIGNED NOT NULL,
 patient_id BIGINT UNSIGNED NOT NULL, uploaded_by BIGINT UNSIGNED NOT NULL,
 storage_key VARCHAR(255) NOT NULL, original_name VARCHAR(255) NOT NULL,
 mime_type VARCHAR(120) NOT NULL, file_size_bytes BIGINT UNSIGNED NOT NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(clinic_id) REFERENCES clinics(id), FOREIGN KEY(patient_id) REFERENCES patients(id),
 FOREIGN KEY(uploaded_by) REFERENCES users(id)
) ENGINE=InnoDB;
CREATE TABLE audit_logs (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, clinic_id BIGINT UNSIGNED NOT NULL,
 actor_user_id BIGINT UNSIGNED NULL, action VARCHAR(80) NOT NULL,
 entity_type VARCHAR(80) NOT NULL, entity_id BIGINT UNSIGNED NULL,
 happened_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 ip_address VARBINARY(16) NULL, metadata_json JSON NULL,
 INDEX idx_audit_actor (actor_user_id, happened_at),
 INDEX idx_audit_entity (entity_type, entity_id, happened_at),
 FOREIGN KEY(clinic_id) REFERENCES clinics(id), FOREIGN KEY(actor_user_id) REFERENCES users(id)
) ENGINE=InnoDB;

INSERT INTO roles (code, label) VALUES
 ('owner','Vlasnik / administrator'),('dentist','Stomatolog'),
 ('reception','Recepcija'),('patient','Pacijent');
INSERT INTO clinics (name, timezone) VALUES ('BioCare Dent','Europe/Belgrade');
INSERT INTO chairs (clinic_id,name) VALUES (1,'Stolica 1');

-- IMPORTANT: MySQL unique indices DO NOT prevent overlapping time ranges.
-- Booking transaction MUST lock the relevant chair row (SELECT ... FOR UPDATE),
-- check interval collision against active, non-expired appointments and schedule_blocks,
-- then insert/confirm within the SAME transaction; rollback on conflict.
-- Never allow website users direct database access; use authenticated, validated API.
-- All production medical data requires detailed rights review, data retention policy,
-- encryption, audit trails and integration/legal assessment before go-live.