-- Step 6: additive clinical/IPD upgrade. Run after Step 5 billing migration.
-- No legacy table or column is removed or renamed. Historical rows may retain NULL links.
CREATE TABLE IF NOT EXISTS patient_encounter (
 id INT NOT NULL AUTO_INCREMENT, encounter_number VARCHAR(32) NOT NULL, patient_id INT NOT NULL, appointment_id INT NULL,
 doctor_id INT NULL, department_id INT NULL, encounter_type VARCHAR(16) NOT NULL DEFAULT 'OPD', visit_type VARCHAR(16) NOT NULL DEFAULT 'WALK_IN',
 checkin_time DATETIME NULL, consultation_start_time DATETIME NULL, consultation_end_time DATETIME NULL, status VARCHAR(24) NOT NULL DEFAULT 'WAITING',
 created_by INT NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, PRIMARY KEY(id), UNIQUE KEY uq_encounter_number(encounter_number),
 UNIQUE KEY uq_encounter_appointment(appointment_id), KEY ix_encounter_patient(patient_id), KEY ix_encounter_status(status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS clinical_consultation (
 id INT NOT NULL AUTO_INCREMENT, patient_id INT NOT NULL, encounter_id INT NULL, doctor_id INT NULL, vital_temperature DECIMAL(5,2) NULL,
 systolic_bp SMALLINT NULL, diastolic_bp SMALLINT NULL, pulse SMALLINT NULL, respiratory_rate SMALLINT NULL, spo2 DECIMAL(5,2) NULL,
 height DECIMAL(6,2) NULL, weight DECIMAL(6,2) NULL, bmi DECIMAL(6,2) NULL, chief_complaint TEXT, present_illness TEXT, past_history TEXT,
 allergies TEXT, examination TEXT, provisional_diagnosis TEXT, final_diagnosis TEXT, clinical_notes TEXT, followup_date DATE NULL,
 created_by INT NULL, created_at DATETIME NOT NULL, PRIMARY KEY(id), KEY ix_consult_patient(patient_id), KEY ix_consult_encounter(encounter_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS clinical_diagnosis (id INT NOT NULL AUTO_INCREMENT, consultation_id INT NOT NULL, diagnosis_type VARCHAR(20) NOT NULL, diagnosis_text VARCHAR(255) NOT NULL, created_at DATETIME NOT NULL, PRIMARY KEY(id), KEY ix_diagnosis_consultation(consultation_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS admissions (
 id INT NOT NULL AUTO_INCREMENT, admission_number VARCHAR(32) NOT NULL, patient_id INT NOT NULL, encounter_id INT NULL, doctor_id INT NULL, department_id INT NULL,
 admission_type VARCHAR(16) NOT NULL DEFAULT 'PLANNED', admission_reason TEXT, admission_diagnosis TEXT, admission_datetime DATETIME NOT NULL,
 expected_discharge_datetime DATETIME NULL, actual_discharge_datetime DATETIME NULL, status VARCHAR(28) NOT NULL DEFAULT 'REQUESTED', created_by INT NULL,
 created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, PRIMARY KEY(id), UNIQUE KEY uq_admission_number(admission_number), KEY ix_admission_patient(patient_id), KEY ix_admission_status(status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS bed_lifecycle (id INT NOT NULL AUTO_INCREMENT, bed_id INT NOT NULL, admission_id INT NULL, status VARCHAR(24) NOT NULL DEFAULT 'AVAILABLE', changed_by INT NULL, changed_at DATETIME NOT NULL, remarks VARCHAR(255) NULL, PRIMARY KEY(id), KEY ix_bed_lifecycle_bed(bed_id), KEY ix_bed_lifecycle_admission(admission_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS nursing_vitals (id INT NOT NULL AUTO_INCREMENT, patient_id INT NOT NULL, admission_id INT NULL, encounter_id INT NULL, temperature DECIMAL(5,2) NULL, systolic_bp SMALLINT NULL, diastolic_bp SMALLINT NULL, pulse SMALLINT NULL, respiratory_rate SMALLINT NULL, spo2 DECIMAL(5,2) NULL, height DECIMAL(6,2) NULL, weight DECIMAL(6,2) NULL, recorded_by INT NULL, recorded_at DATETIME NOT NULL, PRIMARY KEY(id), KEY ix_vitals_patient(patient_id), KEY ix_vitals_admission(admission_id), KEY ix_vitals_encounter(encounter_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS nursing_notes (id INT NOT NULL AUTO_INCREMENT, patient_id INT NOT NULL, admission_id INT NULL, note_type VARCHAR(32) NOT NULL, note_text TEXT NOT NULL, intake_output VARCHAR(64) NULL, recorded_by INT NULL, recorded_at DATETIME NOT NULL, PRIMARY KEY(id), KEY ix_notes_admission(admission_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS medication_administration (id INT NOT NULL AUTO_INCREMENT, prescription_id INT NULL, medicine_id INT NULL, patient_id INT NOT NULL, admission_id INT NULL, scheduled_datetime DATETIME NOT NULL, administered_datetime DATETIME NULL, administered_by INT NULL, dose VARCHAR(64) NULL, route VARCHAR(64) NULL, status VARCHAR(16) NOT NULL DEFAULT 'PENDING', remarks TEXT NULL, created_at DATETIME NOT NULL, PRIMARY KEY(id), KEY ix_mar_patient(patient_id), KEY ix_mar_admission(admission_id), KEY ix_mar_status(status)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS doctor_rounds (id INT NOT NULL AUTO_INCREMENT, admission_id INT NOT NULL, doctor_id INT NOT NULL, round_datetime DATETIME NOT NULL, patient_condition TEXT, progress_notes TEXT, diagnosis_update TEXT, treatment_changes TEXT, new_orders TEXT, medicine_changes TEXT, diet_advice TEXT, followup_instruction TEXT, created_at DATETIME NOT NULL, PRIMARY KEY(id), KEY ix_rounds_admission(admission_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS emergency_triage (id INT NOT NULL AUTO_INCREMENT, patient_id INT NOT NULL, encounter_id INT NULL, arrival_datetime DATETIME NOT NULL, triage_datetime DATETIME NULL, triage_nurse INT NULL, emergency_doctor INT NULL, chief_complaint TEXT, injury_or_condition TEXT, priority VARCHAR(10) NOT NULL DEFAULT 'GREEN', emergency_status VARCHAR(24) NOT NULL DEFAULT 'ARRIVED', created_at DATETIME NOT NULL, PRIMARY KEY(id), KEY ix_triage_patient(patient_id), KEY ix_triage_priority(priority)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS clinical_service_performed (id INT NOT NULL AUTO_INCREMENT, patient_id INT NOT NULL, encounter_id INT NULL, admission_id INT NULL, performer_id INT NULL, service_type VARCHAR(48) NOT NULL, service_id INT NULL, performed_at DATETIME NOT NULL, quantity INT NOT NULL DEFAULT 1, charge_source_type VARCHAR(48) NULL, charge_source_id INT NULL, status VARCHAR(16) NOT NULL DEFAULT 'COMPLETED', created_at DATETIME NOT NULL, PRIMARY KEY(id), UNIQUE KEY uq_service_charge(charge_source_type,charge_source_id), KEY ix_service_patient(patient_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS discharge_summary (id INT NOT NULL AUTO_INCREMENT, admission_id INT NOT NULL, status VARCHAR(32) NOT NULL DEFAULT 'DRAFT', final_diagnosis TEXT, treatment_given TEXT, procedures TEXT, investigations TEXT, hospital_course TEXT, condition_at_discharge TEXT, discharge_medicines TEXT, diet_advice TEXT, activity_advice TEXT, followup TEXT, warning_signs TEXT, doctor_id INT NULL, finalized_at DATETIME NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, PRIMARY KEY(id), UNIQUE KEY uq_discharge_admission(admission_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS patient_consent (id INT NOT NULL AUTO_INCREMENT, patient_id INT NOT NULL, admission_id INT NULL, consent_type VARCHAR(32) NOT NULL, procedure_name VARCHAR(255) NULL, consent_version VARCHAR(32) NOT NULL DEFAULT '1.0', status VARCHAR(16) NOT NULL DEFAULT 'PENDING', signed_datetime DATETIME NULL, witness VARCHAR(255) NULL, document_path VARCHAR(255) NULL, created_by INT NULL, created_at DATETIME NOT NULL, PRIMARY KEY(id), KEY ix_consent_patient(patient_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS clinical_audit_log (id INT NOT NULL AUTO_INCREMENT, user_id INT NULL, user_role VARCHAR(32) NULL, module_name VARCHAR(64) NOT NULL, action_name VARCHAR(64) NOT NULL, record_id INT NULL, old_data TEXT NULL, new_data TEXT NULL, request_identifier VARCHAR(64) NULL, created_at DATETIME NOT NULL, PRIMARY KEY(id), KEY ix_clinical_audit_module(module_name,record_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- Rollback plan: retain clinical history. If rollback is mandatory, disable new routes first and archive these new tables only after a verified export.