SET NAMES utf8mb4;
START TRANSACTION;

INSERT INTO tenant_roles(role_key,display_name,built_in,active) VALUES
('almoner','Almoner',1,1),('province_almoner','Province Almoner',1,1)
ON DUPLICATE KEY UPDATE display_name=VALUES(display_name),active=1;
INSERT IGNORE INTO tenant_role_scopes(role_key,scope_type) VALUES('almoner','unit'),('province_almoner','province');

CREATE TABLE welfare_scope_settings (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, scope_key VARCHAR(80) NOT NULL, scope_type ENUM('unit','province') NOT NULL,
 province_id BIGINT UNSIGNED NOT NULL, unit_id BIGINT UNSIGNED NULL, enabled TINYINT(1) NOT NULL DEFAULT 0,
 legal_basis VARCHAR(255) NOT NULL, special_category_condition VARCHAR(255) NULL, health_information_enabled TINYINT(1) NOT NULL DEFAULT 0,
 retention_months SMALLINT UNSIGNED NOT NULL DEFAULT 24, approved_by BIGINT UNSIGNED NOT NULL, approved_at DATETIME NOT NULL,
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 UNIQUE KEY uq_welfare_scope(scope_key), KEY idx_welfare_mandate(province_id,scope_type,enabled),
 FOREIGN KEY(province_id) REFERENCES provinces(id), FOREIGN KEY(unit_id) REFERENCES units(id), FOREIGN KEY(approved_by) REFERENCES users(id),
 CHECK(retention_months BETWEEN 1 AND 120)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE welfare_cases (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, scope_key VARCHAR(80) NOT NULL, scope_type ENUM('unit','province') NOT NULL,
 province_id BIGINT UNSIGNED NOT NULL, unit_id BIGINT UNSIGNED NULL, person_id BIGINT UNSIGNED NOT NULL,
 assigned_user_id BIGINT UNSIGNED NOT NULL, status ENUM('open','review','closed','withdrawn','retired') NOT NULL DEFAULT 'open',
 personal_summary_cipher LONGTEXT NULL, working_notes_cipher LONGTEXT NULL, contains_health_information TINYINT(1) NOT NULL DEFAULT 0,
 legal_basis_snapshot VARCHAR(255) NOT NULL, special_condition_snapshot VARCHAR(255) NULL,
 opened_on DATE NOT NULL, next_review_on DATE NULL, retention_review_due DATE NOT NULL,
 withdrawn_at DATETIME NULL, retired_at DATETIME NULL, created_by BIGINT UNSIGNED NOT NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 KEY idx_welfare_case_queue(scope_key,assigned_user_id,status,next_review_on), KEY idx_welfare_case_person(person_id,scope_key),
 FOREIGN KEY(province_id) REFERENCES provinces(id), FOREIGN KEY(unit_id) REFERENCES units(id), FOREIGN KEY(person_id) REFERENCES people(id),
 FOREIGN KEY(assigned_user_id) REFERENCES users(id), FOREIGN KEY(created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE welfare_case_assignments (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, case_id BIGINT UNSIGNED NOT NULL, assigned_user_id BIGINT UNSIGNED NOT NULL,
 assigned_by BIGINT UNSIGNED NOT NULL, assigned_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, ended_at DATETIME NULL,
 KEY idx_welfare_assignment(case_id,ended_at), FOREIGN KEY(case_id) REFERENCES welfare_cases(id),
 FOREIGN KEY(assigned_user_id) REFERENCES users(id), FOREIGN KEY(assigned_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE welfare_contacts (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, case_id BIGINT UNSIGNED NOT NULL,
 contact_type ENUM('phone','email','letter','visit','message','other') NOT NULL, occurred_at DATETIME NOT NULL,
 private_note_cipher LONGTEXT NULL, agreed_follow_up_cipher LONGTEXT NULL, follow_up_due DATETIME NULL,
 completed_at DATETIME NULL, created_by BIGINT UNSIGNED NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 KEY idx_welfare_followup(case_id,follow_up_due,completed_at), FOREIGN KEY(case_id) REFERENCES welfare_cases(id), FOREIGN KEY(created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE welfare_life_events (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, case_id BIGINT UNSIGNED NOT NULL,
 event_type ENUM('bereavement','illness','hospital','recovery','death','care','other') NOT NULL, event_date DATE NOT NULL,
 private_detail_cipher LONGTEXT NULL, created_by BIGINT UNSIGNED NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 KEY idx_welfare_life_event(case_id,event_date), FOREIGN KEY(case_id) REFERENCES welfare_cases(id), FOREIGN KEY(created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE welfare_related_contacts (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, scope_key VARCHAR(80) NOT NULL, member_person_id BIGINT UNSIGNED NOT NULL,
 related_person_id BIGINT UNSIGNED NULL, relationship_type ENUM('spouse','partner','surviving_spouse','surviving_partner','other') NOT NULL,
 display_name_cipher LONGTEXT NOT NULL, email_cipher LONGTEXT NULL, phone_cipher LONGTEXT NULL, address_cipher LONGTEXT NULL,
 preferred_channel ENUM('email','phone','post','none') NOT NULL DEFAULT 'none', contact_allowed TINYINT(1) NOT NULL DEFAULT 0,
 preference_source VARCHAR(255) NOT NULL, preference_recorded_at DATETIME NOT NULL, withdrawn_at DATETIME NULL,
 created_by BIGINT UNSIGNED NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 KEY idx_welfare_relation(scope_key,member_person_id,withdrawn_at), FOREIGN KEY(member_person_id) REFERENCES people(id),
 FOREIGN KEY(related_person_id) REFERENCES people(id) ON DELETE SET NULL, FOREIGN KEY(created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE welfare_gestures (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, case_id BIGINT UNSIGNED NOT NULL,
 gesture_type ENUM('card','flowers','visit','cheque','support','reimbursement') NOT NULL, planned_for DATE NOT NULL,
 status ENUM('planned','approved','sent','completed','cancelled','rejected','paid') NOT NULL DEFAULT 'planned',
 amount DECIMAL(12,2) NULL, accounting_category VARCHAR(80) NULL, private_reason_cipher LONGTEXT NULL,
 finance_entry_id BIGINT UNSIGNED NULL, created_by BIGINT UNSIGNED NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 KEY idx_welfare_gesture(case_id,status,planned_for), KEY idx_welfare_finance(status,accounting_category,planned_for),
 FOREIGN KEY(case_id) REFERENCES welfare_cases(id), FOREIGN KEY(finance_entry_id) REFERENCES finance_ledger_entries(id), FOREIGN KEY(created_by) REFERENCES users(id),
 CHECK((gesture_type IN('cheque','support','reimbursement') AND amount IS NOT NULL AND amount>0 AND accounting_category IS NOT NULL) OR (gesture_type NOT IN('cheque','support','reimbursement') AND amount IS NULL))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE welfare_exceptional_access (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, scope_key VARCHAR(80) NOT NULL, user_id BIGINT UNSIGNED NOT NULL,
 granted_by BIGINT UNSIGNED NOT NULL, reason_cipher LONGTEXT NOT NULL, granted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 expires_at DATETIME NOT NULL, revoked_at DATETIME NULL, last_used_at DATETIME NULL,
 KEY idx_welfare_exception(scope_key,user_id,expires_at,revoked_at), FOREIGN KEY(user_id) REFERENCES users(id), FOREIGN KEY(granted_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE welfare_case_events (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, case_id BIGINT UNSIGNED NULL, scope_key VARCHAR(80) NOT NULL,
 actor_id BIGINT UNSIGNED NOT NULL, event_key VARCHAR(80) NOT NULL, occurred_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 KEY idx_welfare_event(scope_key,occurred_at), FOREIGN KEY(case_id) REFERENCES welfare_cases(id) ON DELETE SET NULL, FOREIGN KEY(actor_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE tenant_retention_schedules MODIFY data_class ENUM('memberships','candidates','guest_directory','bookings','guests','messages','documents','welfare','finance','dc_private_notes') NOT NULL;
INSERT INTO migration_history(version,name,checksum,execution_ms) VALUES('202609280041','Scoped Almoner welfare cases and restricted finance sharing',SHA2('202609280041_almoner_welfare_v1',256),0);
COMMIT;