CREATE DATABASE IF NOT EXISTS protezdis CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE protezdis;
CREATE TABLE organizations (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 type ENUM('clinic','laboratory') NOT NULL,
 name VARCHAR(190) NOT NULL,
 tax_number VARCHAR(32) NULL,
 city VARCHAR(100) NULL,
 district VARCHAR(100) NULL,
 phone VARCHAR(40) NULL,
 email VARCHAR(190) NULL,
 address TEXT NULL,
 status ENUM('pending','active','suspended') NOT NULL DEFAULT 'pending',
 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
 INDEX idx_org_type_status(type,status)
) ENGINE=InnoDB;
CREATE TABLE users (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 organization_id BIGINT UNSIGNED NULL,
 full_name VARCHAR(150) NOT NULL,
 email VARCHAR(190) NOT NULL UNIQUE,
 password_hash VARCHAR(255) NOT NULL,
 role ENUM('admin','dentist','lab_admin','lab_staff') NOT NULL,
 status ENUM('pending','active','suspended') NOT NULL DEFAULT 'pending',
 last_login_at DATETIME NULL,
 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 CONSTRAINT fk_users_org FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE SET NULL,
 INDEX idx_users_org_role(organization_id,role)
) ENGINE=InnoDB;
CREATE TABLE cases (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 case_code VARCHAR(32) NOT NULL UNIQUE,
 title VARCHAR(190) NOT NULL,
 created_by BIGINT UNSIGNED NOT NULL,
 clinic_organization_id BIGINT UNSIGNED NULL,
 assigned_organization_id BIGINT UNSIGNED NULL,
 tooth_numbers VARCHAR(100) NULL,
 work_type VARCHAR(120) NOT NULL,
 material VARCHAR(120) NULL,
 shade VARCHAR(80) NULL,
 due_date DATE NULL,
 notes TEXT NULL,
 status ENUM('draft','submitted','quoted','accepted','design','production','try_in','revision','ready','shipped','completed','cancelled') NOT NULL DEFAULT 'draft',
 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
 FOREIGN KEY (created_by) REFERENCES users(id),
 FOREIGN KEY (clinic_organization_id) REFERENCES organizations(id) ON DELETE SET NULL,
 FOREIGN KEY (assigned_organization_id) REFERENCES organizations(id) ON DELETE SET NULL,
 INDEX idx_cases_status_due(status,due_date),
 INDEX idx_cases_clinic(clinic_organization_id),
 INDEX idx_cases_lab(assigned_organization_id)
) ENGINE=InnoDB;
CREATE TABLE case_files (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 case_id BIGINT UNSIGNED NOT NULL,
 uploaded_by BIGINT UNSIGNED NOT NULL,
 original_name VARCHAR(255) NOT NULL,
 stored_name VARCHAR(255) NOT NULL,
 mime_type VARCHAR(120) NOT NULL,
 file_size BIGINT UNSIGNED NOT NULL,
 file_category ENUM('photo','scan','document','other') NOT NULL DEFAULT 'other',
 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY (case_id) REFERENCES cases(id) ON DELETE CASCADE,
 FOREIGN KEY (uploaded_by) REFERENCES users(id),
 INDEX idx_casefiles_case(case_id)
) ENGINE=InnoDB;
CREATE TABLE quotations (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 case_id BIGINT UNSIGNED NOT NULL,
 laboratory_organization_id BIGINT UNSIGNED NOT NULL,
 amount DECIMAL(12,2) NOT NULL,
 currency CHAR(3) NOT NULL DEFAULT 'TRY',
 estimated_days SMALLINT UNSIGNED NULL,
 note TEXT NULL,
 status ENUM('pending','accepted','rejected','withdrawn') NOT NULL DEFAULT 'pending',
 valid_until DATETIME NULL,
 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY (case_id) REFERENCES cases(id) ON DELETE CASCADE,
 FOREIGN KEY (laboratory_organization_id) REFERENCES organizations(id),
 INDEX idx_quotes_case_status(case_id,status)
) ENGINE=InnoDB;
CREATE TABLE case_messages (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 case_id BIGINT UNSIGNED NOT NULL,
 sender_user_id BIGINT UNSIGNED NOT NULL,
 message TEXT NOT NULL,
 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY (case_id) REFERENCES cases(id) ON DELETE CASCADE,
 FOREIGN KEY (sender_user_id) REFERENCES users(id),
 INDEX idx_messages_case_date(case_id,created_at)
) ENGINE=InnoDB;
CREATE TABLE notifications (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL,
 title VARCHAR(190) NOT NULL,
 body TEXT NULL,
 target_url VARCHAR(500) NULL,
 read_at DATETIME NULL,
 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
 INDEX idx_notifications_user_read(user_id,read_at)
) ENGINE=InnoDB;
CREATE TABLE audit_logs (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NULL,
 action VARCHAR(100) NOT NULL,
 entity_type VARCHAR(80) NOT NULL,
 entity_id BIGINT UNSIGNED NULL,
 ip_address VARCHAR(45) NULL,
 user_agent VARCHAR(255) NULL,
 details JSON NULL,
 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
 INDEX idx_audit_entity(entity_type,entity_id),
 INDEX idx_audit_created(created_at)
) ENGINE=InnoDB;