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

SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS audit_logs, compliance_logs, treatment_consents, compliance_evidence, compliance_standards, attendance, lab_cases, inventory_items, suppliers, invoice_payments, invoices, prescription_items, prescription_options, prescriptions, medicines, odontogram, treatment_plans, patient_xrays, appointments, patients, settings, users, roles;
SET FOREIGN_KEY_CHECKS=1;

CREATE TABLE roles (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL UNIQUE,
  description VARCHAR(255) NULL
) ENGINE=InnoDB;

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  role_id INT NOT NULL,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(160) NOT NULL UNIQUE,
  phone VARCHAR(30) NULL,
  qualification VARCHAR(120) NULL,
  registration_number VARCHAR(80) NULL,
  signature_path VARCHAR(255) NULL,
  prescription_pin_hash VARCHAR(255) NULL,
  password_hash VARCHAR(255) NOT NULL,
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL,
  INDEX idx_users_role (role_id),
  CONSTRAINT fk_users_role FOREIGN KEY (role_id) REFERENCES roles(id)
) ENGINE=InnoDB;

CREATE TABLE patients (
  id INT AUTO_INCREMENT PRIMARY KEY,
  patient_code VARCHAR(30) NOT NULL UNIQUE,
  first_name VARCHAR(80) NOT NULL,
  last_name VARCHAR(80) NOT NULL,
  gender ENUM('Female','Male','Other') NOT NULL DEFAULT 'Female',
  date_of_birth DATE NULL,
  age INT NULL,
  blood_group VARCHAR(10) NULL,
  government_id_type VARCHAR(40) NULL,
  government_id_number VARCHAR(80) NULL,
  government_id_card_path VARCHAR(255) NULL,
  phone VARCHAR(30) NOT NULL,
  email VARCHAR(160) NULL,
  address TEXT NULL,
  medical_history TEXT NULL,
  dental_history TEXT NULL,
  allergies TEXT NULL,
  emergency_contact VARCHAR(160) NULL,
  created_at DATETIME NOT NULL,
  INDEX idx_patients_name (first_name,last_name),
  INDEX idx_patients_phone (phone)
) ENGINE=InnoDB;

CREATE TABLE appointments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  patient_id INT NOT NULL,
  dentist_id INT NOT NULL,
  appointment_date DATE NOT NULL,
  start_time TIME NOT NULL,
  end_time TIME NOT NULL,
  reason TEXT NULL,
  status ENUM('scheduled','confirmed','completed','cancelled','no_show') NOT NULL DEFAULT 'scheduled',
  notification_channel ENUM('none','sms','whatsapp') NOT NULL DEFAULT 'none',
  notes TEXT NULL,
  created_at DATETIME NOT NULL,
  INDEX idx_appointments_date (appointment_date,start_time),
  INDEX idx_appointments_patient (patient_id),
  CONSTRAINT fk_appointments_patient FOREIGN KEY (patient_id) REFERENCES patients(id) ON DELETE CASCADE,
  CONSTRAINT fk_appointments_dentist FOREIGN KEY (dentist_id) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE patient_xrays (
  id INT AUTO_INCREMENT PRIMARY KEY,
  patient_id INT NOT NULL,
  title VARCHAR(160) NULL,
  file_path VARCHAR(255) NOT NULL,
  uploaded_by INT NOT NULL,
  created_at DATETIME NOT NULL,
  INDEX idx_patient_xrays_patient (patient_id),
  CONSTRAINT fk_patient_xrays_patient FOREIGN KEY (patient_id) REFERENCES patients(id) ON DELETE CASCADE,
  CONSTRAINT fk_patient_xrays_user FOREIGN KEY (uploaded_by) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE treatment_plans (
  id INT AUTO_INCREMENT PRIMARY KEY,
  patient_id INT NOT NULL,
  dentist_id INT NOT NULL,
  diagnosis TEXT NOT NULL,
  plan_details TEXT NOT NULL,
  estimated_cost DECIMAL(10,2) NOT NULL DEFAULT 0,
  status ENUM('planned','in_progress','completed','cancelled') NOT NULL DEFAULT 'planned',
  clinical_notes TEXT NULL,
  xray_path VARCHAR(255) NULL,
  created_at DATETIME NOT NULL,
  INDEX idx_treatment_patient (patient_id),
  CONSTRAINT fk_treatment_patient FOREIGN KEY (patient_id) REFERENCES patients(id) ON DELETE CASCADE,
  CONSTRAINT fk_treatment_dentist FOREIGN KEY (dentist_id) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE odontogram (
  id INT AUTO_INCREMENT PRIMARY KEY,
  patient_id INT NOT NULL,
  tooth_number TINYINT NOT NULL,
  condition_status ENUM('healthy','carious','filled','extracted','root_canal') NOT NULL DEFAULT 'healthy',
  notes TEXT NULL,
  updated_by INT NOT NULL,
  updated_at DATETIME NOT NULL,
  UNIQUE KEY uq_patient_tooth (patient_id,tooth_number),
  CONSTRAINT fk_odontogram_patient FOREIGN KEY (patient_id) REFERENCES patients(id) ON DELETE CASCADE,
  CONSTRAINT fk_odontogram_user FOREIGN KEY (updated_by) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE prescriptions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  prescription_no VARCHAR(30) NOT NULL UNIQUE,
  verification_token VARCHAR(64) NOT NULL UNIQUE,
  patient_id INT NOT NULL,
  dentist_id INT NOT NULL,
  prescription_date DATE NOT NULL,
  chief_complaint TEXT NULL,
  clinical_findings TEXT NULL,
  diagnosis TEXT NULL,
  advice TEXT NULL,
  follow_up_date DATE NULL,
  status ENUM('draft','signed') NOT NULL DEFAULT 'draft',
  signed_at DATETIME NULL,
  created_at DATETIME NOT NULL,
  INDEX idx_prescriptions_patient (patient_id),
  CONSTRAINT fk_prescriptions_patient FOREIGN KEY (patient_id) REFERENCES patients(id) ON DELETE CASCADE,
  CONSTRAINT fk_prescriptions_dentist FOREIGN KEY (dentist_id) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE medicines (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(160) NOT NULL,
  strength VARCHAR(80) NULL,
  form VARCHAR(60) NULL,
  default_dosage VARCHAR(120) NULL,
  instructions TEXT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  UNIQUE KEY uq_medicine (name,strength,form)
) ENGINE=InnoDB;

CREATE TABLE prescription_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  prescription_id INT NOT NULL,
  medicine_name VARCHAR(160) NOT NULL,
  strength VARCHAR(80) NULL,
  dosage VARCHAR(120) NOT NULL,
  frequency VARCHAR(120) NOT NULL,
  duration VARCHAR(120) NOT NULL,
  instructions TEXT NULL,
  INDEX idx_rx_items_prescription (prescription_id),
  CONSTRAINT fk_rx_items_prescription FOREIGN KEY (prescription_id) REFERENCES prescriptions(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE prescription_options (
  id INT AUTO_INCREMENT PRIMARY KEY,
  option_type ENUM('frequency','duration','instruction') NOT NULL,
  option_value VARCHAR(160) NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  sort_order INT NOT NULL DEFAULT 100,
  created_at DATETIME NOT NULL,
  UNIQUE KEY uq_prescription_option (option_type,option_value),
  INDEX idx_prescription_options_type (option_type,is_active,sort_order)
) ENGINE=InnoDB;

CREATE TABLE invoices (
  id INT AUTO_INCREMENT PRIMARY KEY,
  invoice_no VARCHAR(30) NOT NULL UNIQUE,
  patient_id INT NOT NULL,
  invoice_date DATE NOT NULL,
  subtotal DECIMAL(10,2) NOT NULL DEFAULT 0,
  discount DECIMAL(10,2) NOT NULL DEFAULT 0,
  tax DECIMAL(10,2) NOT NULL DEFAULT 0,
  total_amount DECIMAL(10,2) NOT NULL DEFAULT 0,
  paid_amount DECIMAL(10,2) NOT NULL DEFAULT 0,
  payment_method ENUM('cash','card','upi','bank','cheque','wallet','insurance') NOT NULL DEFAULT 'cash',
  status ENUM('unpaid','partial','paid') NOT NULL DEFAULT 'unpaid',
  notes TEXT NULL,
  created_at DATETIME NOT NULL,
  INDEX idx_invoices_patient (patient_id),
  INDEX idx_invoices_date (invoice_date),
  CONSTRAINT fk_invoices_patient FOREIGN KEY (patient_id) REFERENCES patients(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE invoice_payments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  invoice_id INT NOT NULL,
  payment_method ENUM('cash','card','upi','bank','cheque','wallet','insurance') NOT NULL DEFAULT 'cash',
  amount DECIMAL(10,2) NOT NULL DEFAULT 0,
  reference_no VARCHAR(120) NULL,
  payment_date DATE NOT NULL,
  created_by INT NOT NULL,
  created_at DATETIME NOT NULL,
  INDEX idx_invoice_payments_invoice (invoice_id),
  CONSTRAINT fk_invoice_payments_invoice FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE,
  CONSTRAINT fk_invoice_payments_user FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE suppliers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(160) NOT NULL,
  contact_person VARCHAR(120) NULL,
  phone VARCHAR(30) NULL,
  email VARCHAR(160) NULL,
  address TEXT NULL,
  created_at DATETIME NOT NULL
) ENGINE=InnoDB;

CREATE TABLE inventory_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  supplier_id INT NULL,
  name VARCHAR(160) NOT NULL,
  category VARCHAR(100) NULL,
  quantity INT NOT NULL DEFAULT 0,
  reorder_level INT NOT NULL DEFAULT 0,
  unit_cost DECIMAL(10,2) NOT NULL DEFAULT 0,
  expiry_date DATE NULL,
  created_at DATETIME NOT NULL,
  INDEX idx_inventory_supplier (supplier_id),
  CONSTRAINT fk_inventory_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE lab_cases (
  id INT AUTO_INCREMENT PRIMARY KEY,
  case_no VARCHAR(30) NOT NULL UNIQUE,
  patient_id INT NOT NULL,
  lab_name VARCHAR(160) NOT NULL,
  work_type VARCHAR(160) NOT NULL,
  sent_date DATE NULL,
  due_date DATE NULL,
  status ENUM('sent','received','delivered','cancelled') NOT NULL DEFAULT 'sent',
  cost DECIMAL(10,2) NOT NULL DEFAULT 0,
  notes TEXT NULL,
  created_at DATETIME NOT NULL,
  INDEX idx_lab_patient (patient_id),
  CONSTRAINT fk_lab_patient FOREIGN KEY (patient_id) REFERENCES patients(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE attendance (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  attendance_date DATE NOT NULL,
  check_in TIME NULL,
  check_out TIME NULL,
  status ENUM('present','absent','leave') NOT NULL DEFAULT 'present',
  UNIQUE KEY uq_attendance_user_date (user_id,attendance_date),
  CONSTRAINT fk_attendance_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE settings (
  id INT AUTO_INCREMENT PRIMARY KEY,
  setting_key VARCHAR(100) NOT NULL UNIQUE,
  setting_value TEXT NULL
) ENGINE=InnoDB;

CREATE TABLE compliance_standards (
  id INT AUTO_INCREMENT PRIMARY KEY,
  chapter VARCHAR(120) NOT NULL,
  standard_code VARCHAR(40) NOT NULL UNIQUE,
  title VARCHAR(180) NOT NULL,
  requirement TEXT NOT NULL,
  owner_role VARCHAR(80) NOT NULL,
  frequency VARCHAR(80) NOT NULL,
  INDEX idx_compliance_chapter (chapter)
) ENGINE=InnoDB;

CREATE TABLE compliance_evidence (
  id INT AUTO_INCREMENT PRIMARY KEY,
  standard_id INT NOT NULL,
  evidence_date DATE NOT NULL,
  status ENUM('pending','partial','compliant','not_applicable') NOT NULL DEFAULT 'pending',
  notes TEXT NULL,
  file_path VARCHAR(255) NULL,
  created_by INT NOT NULL,
  created_at DATETIME NOT NULL,
  INDEX idx_evidence_standard (standard_id),
  CONSTRAINT fk_evidence_standard FOREIGN KEY (standard_id) REFERENCES compliance_standards(id) ON DELETE CASCADE,
  CONSTRAINT fk_evidence_user FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE treatment_consents (
  id INT AUTO_INCREMENT PRIMARY KEY,
  patient_id INT NOT NULL,
  dentist_id INT NOT NULL,
  procedure_name VARCHAR(180) NOT NULL,
  risks_explained TEXT NOT NULL,
  alternatives_explained TEXT NOT NULL,
  patient_signature_name VARCHAR(160) NOT NULL,
  witness_name VARCHAR(160) NULL,
  consent_date DATE NOT NULL,
  status ENUM('draft','signed','revoked') NOT NULL DEFAULT 'signed',
  video_path VARCHAR(255) NULL,
  created_by INT NOT NULL,
  created_at DATETIME NOT NULL,
  INDEX idx_consents_patient (patient_id),
  CONSTRAINT fk_consents_patient FOREIGN KEY (patient_id) REFERENCES patients(id) ON DELETE CASCADE,
  CONSTRAINT fk_consents_dentist FOREIGN KEY (dentist_id) REFERENCES users(id),
  CONSTRAINT fk_consents_user FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE compliance_logs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  log_type ENUM('sterilization','biomedical_waste','incident','equipment','training','fire_safety','infection_control') NOT NULL,
  log_date DATE NOT NULL,
  title VARCHAR(180) NOT NULL,
  description TEXT NOT NULL,
  status ENUM('open','under_review','completed','closed') NOT NULL DEFAULT 'completed',
  next_due_date DATE NULL,
  file_path VARCHAR(255) NULL,
  created_by INT NOT NULL,
  created_at DATETIME NOT NULL,
  INDEX idx_logs_type_date (log_type,log_date),
  CONSTRAINT fk_logs_user FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE audit_logs (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NULL,
  action VARCHAR(80) NOT NULL,
  entity VARCHAR(100) NOT NULL,
  entity_id INT NULL,
  ip_address VARCHAR(45) NULL,
  created_at DATETIME NOT NULL,
  INDEX idx_audit_user (user_id),
  INDEX idx_audit_entity (entity,entity_id),
  CONSTRAINT fk_audit_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

INSERT INTO roles(name,description) VALUES ('Admin','Full system access'),('Dentist','Clinical workflows'),('Receptionist','Front desk operations'),('Accountant','Billing and reports');
INSERT INTO users(role_id,name,email,phone,qualification,registration_number,prescription_pin_hash,password_hash,status,created_at) VALUES
(1,'System Administrator','admin@dentacare.test','9000000001','MDS','DCI-ADM-001','$2y$10$AZpCSBJayzF3fdGAkg8TN.K1xXtqXbT4RA6xEaznDIKDRbOCX8Oai','$2y$10$AZpCSBJayzF3fdGAkg8TN.K1xXtqXbT4RA6xEaznDIKDRbOCX8Oai','active',NOW()),
(2,'Dr. Asha Mehta','asha@dentacare.test','9000000002','BDS, MDS','DCI-MH-12345','$2y$10$AZpCSBJayzF3fdGAkg8TN.K1xXtqXbT4RA6xEaznDIKDRbOCX8Oai','$2y$10$AZpCSBJayzF3fdGAkg8TN.K1xXtqXbT4RA6xEaznDIKDRbOCX8Oai','active',NOW()),
(3,'Riya Shah','riya@dentacare.test','9000000003',NULL,NULL,NULL,'$2y$10$AZpCSBJayzF3fdGAkg8TN.K1xXtqXbT4RA6xEaznDIKDRbOCX8Oai','active',NOW());
INSERT INTO patients(patient_code,first_name,last_name,gender,date_of_birth,age,blood_group,government_id_type,government_id_number,government_id_card_path,phone,email,address,medical_history,dental_history,allergies,emergency_contact,created_at) VALUES
('P260623101','Neha','Kapoor','Female','1992-04-10',34,'B+','Aadhaar','XXXX-XXXX-2101',NULL,'9876500001','neha@example.com','Mumbai','No chronic illness','Sensitivity in molars','None','Raj Kapoor 9876500002',NOW()),
('P260623102','Arjun','Rao','Male','1987-09-21',38,'O+','PAN','ABCDE1234F',NULL,'9876500003','arjun@example.com','Pune','Diabetes controlled','Root canal in 2023','Penicillin','Mira Rao 9876500004',NOW());
INSERT INTO appointments(patient_id,dentist_id,appointment_date,start_time,end_time,reason,status,notification_channel,notes,created_at) VALUES
(1,2,CURDATE(),'10:00','10:30','Consultation','confirmed','whatsapp','First visit',NOW()),
(2,2,DATE_ADD(CURDATE(), INTERVAL 1 DAY),'11:00','11:45','Crown fitting','scheduled','sms','Bring previous x-ray',NOW());
INSERT INTO treatment_plans(patient_id,dentist_id,diagnosis,plan_details,estimated_cost,status,clinical_notes,created_at) VALUES
(1,2,'Class II caries on 36','Composite restoration and fluoride care',3500,'planned','Discussed oral hygiene plan',NOW()),
(2,2,'Post RCT crown required','Zirconia crown with lab impression',12000,'in_progress','Impression sent',NOW());
INSERT INTO medicines(name,strength,form,default_dosage,instructions) VALUES
('Amoxicillin','500 mg','Capsule','1 capsule','After food'),
('Ibuprofen','400 mg','Tablet','1 tablet','After food if pain persists'),
('Chlorhexidine','0.2%','Mouthwash','10 ml rinse','Do not swallow'),
('Desensitizing Toothpaste','','Paste','Twice daily','Apply with soft brush');
INSERT INTO prescriptions(prescription_no,verification_token,patient_id,dentist_id,prescription_date,chief_complaint,clinical_findings,diagnosis,advice,follow_up_date,status,signed_at,created_at) VALUES
('RX2606231001','sampletokenrx1001',1,2,CURDATE(),'Sensitivity to cold','Mild cervical abrasion','Dentin hypersensitivity','Avoid very cold drinks and use soft brush',DATE_ADD(CURDATE(), INTERVAL 14 DAY),'signed',NOW(),NOW());
INSERT INTO prescription_items(prescription_id,medicine_name,strength,dosage,frequency,duration,instructions) VALUES
(1,'Desensitizing Toothpaste','','Pea-sized amount','Twice daily','14 days','Do not rinse immediately after brushing');
INSERT INTO prescription_options(option_type,option_value,is_active,sort_order,created_at) VALUES
('frequency','Once daily',1,10,NOW()),
('frequency','Twice daily',1,20,NOW()),
('frequency','Thrice daily',1,30,NOW()),
('frequency','Every 6 hours',1,40,NOW()),
('frequency','Every 8 hours',1,50,NOW()),
('frequency','Every 12 hours',1,60,NOW()),
('frequency','At bedtime',1,70,NOW()),
('frequency','SOS',1,80,NOW()),
('duration','1 day',1,10,NOW()),
('duration','3 days',1,20,NOW()),
('duration','5 days',1,30,NOW()),
('duration','7 days',1,40,NOW()),
('duration','10 days',1,50,NOW()),
('duration','14 days',1,60,NOW()),
('duration','1 month',1,70,NOW()),
('duration','Until next visit',1,80,NOW()),
('instruction','After food',1,10,NOW()),
('instruction','Before food',1,20,NOW()),
('instruction','Before breakfast',1,30,NOW()),
('instruction','After dinner',1,40,NOW()),
('instruction','Before dinner',1,50,NOW()),
('instruction','Do not chew',1,60,NOW()),
('instruction','Do not swallow',1,70,NOW()),
('instruction','Apply locally',1,80,NOW()),
('instruction','Rinse and spit',1,90,NOW()),
('instruction','SOS if pain persists',1,100,NOW());
INSERT INTO invoices(invoice_no,patient_id,invoice_date,subtotal,discount,tax,total_amount,paid_amount,payment_method,status,notes,created_at) VALUES
('INV260623101',1,CURDATE(),3500,0,0,3500,1500,'upi','partial','Advance payment',NOW());
INSERT INTO invoice_payments(invoice_id,payment_method,amount,reference_no,payment_date,created_by,created_at) VALUES
(1,'upi',1000,'UPI-SAMPLE-001',CURDATE(),1,NOW()),
(1,'cash',500,'',CURDATE(),1,NOW());
INSERT INTO suppliers(name,contact_person,phone,email,address,created_at) VALUES ('Prime Dental Supplies','Karan Jain','9000011111','sales@prime.example','Delhi',NOW());
INSERT INTO inventory_items(supplier_id,name,category,quantity,reorder_level,unit_cost,expiry_date,created_at) VALUES
(1,'Composite Resin A2','Restorative',8,10,950,'2027-12-31',NOW()),
(1,'Latex Gloves Box','Consumable',40,20,450,'2028-01-31',NOW());
INSERT INTO lab_cases(case_no,patient_id,lab_name,work_type,sent_date,due_date,status,cost,notes,created_at) VALUES
('LAB260623101',2,'Bright Smile Lab','Zirconia Crown',CURDATE(),DATE_ADD(CURDATE(), INTERVAL 5 DAY),'sent',4500,'Shade A2',NOW());
INSERT INTO attendance(user_id,attendance_date,check_in,status) VALUES (2,CURDATE(),'09:30','present');
INSERT INTO settings(setting_key,setting_value) VALUES
('app_url',''),('clinic_name','Dentacare Clinic'),('clinic_tagline','Modern Dental Care'),('clinic_phone','+91 90000 00000'),('clinic_alt_phone',''),('clinic_email','hello@dentacare.test'),('clinic_website','https://dentacare.test'),('clinic_address','2nd Floor, Smile Avenue'),('clinic_city','Mumbai'),('clinic_state','Maharashtra'),('clinic_pincode',''),('clinic_gst',''),('clinic_license',''),('clinic_registration',''),('clinic_logo',''),('clinic_footer_note','This prescription is digitally generated and can be verified using the QR code.'),('sms_gateway_url',''),('whatsapp_gateway_url','');

INSERT INTO compliance_standards(chapter,standard_code,title,requirement,owner_role,frequency) VALUES
('Access, Assessment and Continuity of Care','AAC-01','Patient registration and identification','Every patient record must carry unique ID, demographics, history, and visit continuity details.','Receptionist','Every visit'),
('Care of Patients','COP-01','Clinical assessment and treatment plan','Chief complaint, findings, diagnosis, odontogram, treatment plan, and follow-up are documented.','Dentist','Every consultation'),
('Management of Medication','MOM-01','Safe prescription practice','Prescriptions include doctor identity, registration number, medicines, dose, frequency, duration, allergy review, and signature.','Dentist','Every prescription'),
('Patient Rights and Education','PRE-01','Informed consent','Procedure risks, alternatives, patient/guardian acknowledgement, and witness details are recorded.','Dentist','Before procedure'),
('Hospital Infection Control','HIC-01','Sterilization monitoring','Autoclave/sterilization cycles and infection-control checks are logged with evidence.','Clinic Manager','Daily'),
('Patient Safety and Quality Improvement','PSQ-01','Incident and adverse event reporting','Clinical incidents, corrective action, and closure status are documented.','Clinic Manager','As needed'),
('Responsibilities of Management','ROM-01','Policy and SOP control','Clinic SOPs, review dates, and responsible owners are tracked.','Admin','Quarterly'),
('Facility Management and Safety','FMS-01','Equipment maintenance','Dental chair, compressor, x-ray, autoclave, and safety equipment maintenance is recorded.','Clinic Manager','Monthly'),
('Human Resource Management','HRM-01','Staff credentialing and training','Staff roles, attendance, training, registration, and competency evidence are maintained.','Admin','Monthly'),
('Information Management System','IMS-01','Data privacy, audit and backup','Role access, audit logs, backup, restore, and patient record confidentiality are monitored.','Admin','Weekly');

INSERT INTO compliance_evidence(standard_id,evidence_date,status,notes,created_by,created_at) VALUES
(1,CURDATE(),'compliant','Patient registration module active with unique patient codes.',1,NOW()),
(3,CURDATE(),'partial','E-prescription pad enabled; upload live doctor signature before go-live.',1,NOW()),
(10,CURDATE(),'compliant','Role-based login, audit logs, and backup tool enabled.',1,NOW());
INSERT INTO treatment_consents(patient_id,dentist_id,procedure_name,risks_explained,alternatives_explained,patient_signature_name,witness_name,consent_date,status,created_by,created_at) VALUES
(2,2,'Zirconia crown preparation','Sensitivity, temporary discomfort, and crown adjustment explained.','Metal ceramic crown and delayed treatment explained.','Arjun Rao','Riya Shah',CURDATE(),'signed',1,NOW());
INSERT INTO compliance_logs(log_type,log_date,title,description,status,next_due_date,created_by,created_at) VALUES
('sterilization',CURDATE(),'Autoclave cycle completed','Daily sterilization cycle completed for operative instruments.','completed',DATE_ADD(CURDATE(), INTERVAL 1 DAY),1,NOW()),
('biomedical_waste',CURDATE(),'Biomedical waste handed over','Segregated clinical waste handed to authorized vendor.','completed',DATE_ADD(CURDATE(), INTERVAL 1 DAY),1,NOW()),
('equipment',CURDATE(),'Dental chair preventive check','Chair movement, suction, light, and waterline checked.','completed',DATE_ADD(CURDATE(), INTERVAL 30 DAY),1,NOW()),
('training',CURDATE(),'Infection control briefing','Staff trained on PPE, hand hygiene, and surface disinfection workflow.','completed',DATE_ADD(CURDATE(), INTERVAL 90 DAY),1,NOW());