-- Step 8: Refund approval and cashier shifts. Additive, idempotent and non-destructive.
SET @db := DATABASE();
CREATE TABLE IF NOT EXISTS refund_requests (
 id BIGINT NOT NULL AUTO_INCREMENT, patient_id INT NOT NULL, original_payment_id INT NOT NULL,
 invoice_id INT NULL, amount DECIMAL(12,2) NOT NULL, method VARCHAR(40) NOT NULL,
 reason VARCHAR(500) NOT NULL, status VARCHAR(20) NOT NULL DEFAULT 'REQUESTED',
 request_token VARCHAR(64) NOT NULL, requested_by INT NULL, requested_role VARCHAR(30) NULL,
 requested_at DATETIME NOT NULL, approved_by INT NULL, approved_at DATETIME NULL,
 rejection_reason VARCHAR(500) NULL, refund_payment_id INT NULL, PRIMARY KEY(id),
 UNIQUE KEY uq_refund_request_token(request_token), KEY ix_refund_original_status(original_payment_id,status),
 KEY ix_refund_patient_status(patient_id,status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/* Existing HMSC dumps use cash_shifts; extend that table rather than creating a duplicate. */
CREATE TABLE IF NOT EXISTS cash_shifts (
 id BIGINT NOT NULL AUTO_INCREMENT, user_id INT NOT NULL, opening_cash DECIMAL(12,2) NOT NULL DEFAULT 0,
 expected_closing DECIMAL(12,2) NOT NULL DEFAULT 0, actual_closing DECIMAL(12,2) NULL,
 difference_amount DECIMAL(12,2) NULL, remarks TEXT NULL, status VARCHAR(15) NOT NULL DEFAULT 'OPEN',
 opened_at DATETIME NOT NULL, closed_at DATETIME NULL, PRIMARY KEY(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
SET @q := IF((SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema=@db AND table_name='payment' AND index_name='payment_timestamp_collection_idx')=0,
 'ALTER TABLE payment ADD KEY payment_timestamp_collection_idx (timestamp,collection_type,method)', 'SELECT 1'); PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s;
SET @q := IF((SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=@db AND table_name='refund_requests' AND column_name='request_token')=0,
 'ALTER TABLE refund_requests ADD COLUMN request_token VARCHAR(64) NULL AFTER status', 'SELECT 1'); PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s;
SET @q := IF((SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=@db AND table_name='refund_requests' AND column_name='requested_role')=0,
 'ALTER TABLE refund_requests ADD COLUMN requested_role VARCHAR(30) NULL AFTER requested_by', 'SELECT 1'); PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s;
SET @q := IF((SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=@db AND table_name='refund_requests' AND column_name='requested_at')=0,
 'ALTER TABLE refund_requests ADD COLUMN requested_at DATETIME NULL AFTER created_at', 'SELECT 1'); PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s;
SET @q := IF((SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=@db AND table_name='refund_requests' AND column_name='approved_at')=0,
 'ALTER TABLE refund_requests ADD COLUMN approved_at DATETIME NULL AFTER approved_by', 'SELECT 1'); PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s;
SET @q := IF((SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=@db AND table_name='refund_requests' AND column_name='rejection_reason')=0,
 'ALTER TABLE refund_requests ADD COLUMN rejection_reason VARCHAR(500) NULL', 'SELECT 1'); PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s;
SET @q := IF((SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=@db AND table_name='refund_requests' AND column_name='refund_payment_id')=0,
 'ALTER TABLE refund_requests ADD COLUMN refund_payment_id INT NULL', 'SELECT 1'); PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s;
SET @q := IF((SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=@db AND table_name='cash_shifts' AND column_name='cashier_role')=0,
 'ALTER TABLE cash_shifts ADD COLUMN cashier_role VARCHAR(30) NULL AFTER user_id', 'SELECT 1'); PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s;
SET @q := IF((SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=@db AND table_name='payment' AND column_name='cashier_shift_id')=0,
 'ALTER TABLE payment ADD COLUMN cashier_shift_id BIGINT NULL AFTER patient_id', 'SELECT 1'); PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s;
/* Some legacy HMSC databases already have cashier_shift_id as TEXT. A full index on that
   legacy type exceeds old MyISAM's 1000-byte limit, so use a normal numeric index when the
   column is numeric and a bounded 20-character prefix only for legacy text columns. */
SET @cashier_shift_type := (SELECT DATA_TYPE FROM information_schema.columns WHERE table_schema=@db AND table_name='payment' AND column_name='cashier_shift_id');
SET @q := IF((SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema=@db AND table_name='payment' AND index_name='payment_cashier_shift_idx')>0,
 'SELECT 1', IF(@cashier_shift_type IN ('tinyint','smallint','mediumint','int','bigint'),
 'ALTER TABLE payment ADD KEY payment_cashier_shift_idx (cashier_shift_id)',
 'ALTER TABLE payment ADD KEY payment_cashier_shift_idx (cashier_shift_id(20))'));
PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s;