USE dental_clinic;

ALTER TABLE treatment_consents ADD COLUMN IF NOT EXISTS video_path VARCHAR(255) NULL AFTER status;
ALTER TABLE patients ADD COLUMN IF NOT EXISTS government_id_type VARCHAR(40) NULL AFTER blood_group;
ALTER TABLE patients ADD COLUMN IF NOT EXISTS government_id_number VARCHAR(80) NULL AFTER government_id_type;
ALTER TABLE patients ADD COLUMN IF NOT EXISTS government_id_card_path VARCHAR(255) NULL AFTER government_id_number;
ALTER TABLE invoices MODIFY payment_method ENUM('cash','card','upi','bank','cheque','wallet','insurance') NOT NULL DEFAULT 'cash';

CREATE TABLE IF NOT EXISTS 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 IF NOT EXISTS 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 IF NOT EXISTS 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 IF NOT EXISTS 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;

INSERT IGNORE INTO medicines(name,strength,form,default_dosage,instructions,is_active) VALUES
('Amoxicillin','500 mg','Capsule','1 capsule','After food',1),
('Ibuprofen','400 mg','Tablet','1 tablet','After food if pain persists',1),
('Chlorhexidine','0.2%','Mouthwash','10 ml rinse','Do not swallow',1),
('Desensitizing Toothpaste','','Paste','Twice daily','Apply with soft brush',1);

INSERT IGNORE 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 IGNORE INTO settings(setting_key,setting_value) VALUES
('app_url',''),
('clinic_tagline',''),
('clinic_alt_phone',''),
('clinic_website',''),
('clinic_city',''),
('clinic_state',''),
('clinic_pincode',''),
('clinic_license',''),
('clinic_registration',''),
('clinic_logo',''),
('clinic_footer_note','This prescription is digitally generated and can be verified using the QR code.');