-- =============================================================================
-- Mental Health Change (MHC) — MASTER DATABASE SCHEMA DEFINITION
-- Generated & Harmonized for MariaDB / MySQL 5.7+ / 8.0+
-- Collation: utf8mb4_unicode_ci
-- Total Tables: 32
-- =============================================================================

SET NAMES utf8mb4;
SET time_zone = '+00:00';
SET foreign_key_checks = 0;
SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';

-- -----------------------------------------------------------------------------
-- 1. ROLES & PERMISSIONS
-- -----------------------------------------------------------------------------
DROP TABLE IF EXISTS `roles`;
CREATE TABLE `roles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `roles` (`id`, `name`, `description`) VALUES
(1, 'admin', 'Super Administrator with full system control'),
(2, 'company', 'Corporate Client HR Manager'),
(3, 'provider', 'Accredited Therapist / Clinical Coach'),
(4, 'client', 'Corporate Client Employee')
ON DUPLICATE KEY UPDATE `name`=VALUES(`name`), `description`=VALUES(`description`);


DROP TABLE IF EXISTS `subroles`;
CREATE TABLE `subroles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `role_id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `slug` varchar(100) NOT NULL,
  `description` text DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `slug` (`slug`),
  KEY `idx_subroles_role` (`role_id`),
  CONSTRAINT `subroles_ibfk_1` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


DROP TABLE IF EXISTS `subrole_permissions`;
CREATE TABLE `subrole_permissions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `subrole_id` int(11) NOT NULL,
  `permission` varchar(150) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_subrole_perm` (`subrole_id`,`permission`),
  KEY `idx_subrole_permissions_subrole` (`subrole_id`),
  CONSTRAINT `subrole_permissions_ibfk_1` FOREIGN KEY (`subrole_id`) REFERENCES `subroles` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- -----------------------------------------------------------------------------
-- 2. USERS & AUDIT LOGS
-- -----------------------------------------------------------------------------
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `email` varchar(255) NOT NULL,
  `password_hash` varchar(255) NOT NULL,
  `login_token` varchar(100) DEFAULT NULL,
  `role_id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `lang_pref` enum('es','en') DEFAULT 'en',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `subrole_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`),
  UNIQUE KEY `login_token` (`login_token`),
  KEY `role_id` (`role_id`),
  KEY `idx_users_subrole` (`subrole_id`),
  CONSTRAINT `users_ibfk_1` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`),
  CONSTRAINT `fk_users_subrole` FOREIGN KEY (`subrole_id`) REFERENCES `subroles` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


DROP TABLE IF EXISTS `consent_log`;
CREATE TABLE `consent_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `consent_type` enum('privacy_policy','data_processing','cookies') NOT NULL,
  `accepted` tinyint(1) NOT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `consent_log_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- -----------------------------------------------------------------------------
-- 3. COMPANIES & CORPORATE HUB
-- -----------------------------------------------------------------------------
DROP TABLE IF EXISTS `companies`;
CREATE TABLE `companies` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `code` varchar(50) DEFAULT NULL,
  `allowed_domains` varchar(500) DEFAULT NULL COMMENT 'Dominios separados por coma: @empresa.com, @corp.ie',
  `contact_name` varchar(255) DEFAULT NULL,
  `contact_email` varchar(255) DEFAULT NULL,
  `account_manager_name` varchar(150) DEFAULT 'David Doyle',
  `account_manager_email` varchar(255) DEFAULT 'david@mentalhealthchange.ie',
  `account_manager_user_id` int(11) DEFAULT NULL,
  `phone` varchar(50) DEFAULT NULL,
  `industry` varchar(100) DEFAULT NULL,
  `country` varchar(100) DEFAULT 'Ireland',
  `sessions_per_member` int(11) DEFAULT 8,
  `total_members_enrolled` int(11) DEFAULT 0,
  `contract_start` date DEFAULT NULL,
  `contract_end` date DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_id` (`user_id`),
  UNIQUE KEY `code` (`code`),
  KEY `idx_companies_code` (`code`),
  KEY `idx_companies_acct_mgr` (`account_manager_user_id`),
  CONSTRAINT `companies_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `companies_ibfk_account_mgr` FOREIGN KEY (`account_manager_user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


DROP TABLE IF EXISTS `company_admins`;
CREATE TABLE `company_admins` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `company_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `admin_level` enum('owner','hr_admin','manager') NOT NULL DEFAULT 'hr_admin',
  `department_access` varchar(255) DEFAULT NULL,
  `can_view_reports` tinyint(1) NOT NULL DEFAULT 1,
  `can_manage_billing` tinyint(1) NOT NULL DEFAULT 0,
  `can_manage_team` tinyint(1) NOT NULL DEFAULT 1,
  `can_access_resources` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_comp_user` (`company_id`,`user_id`),
  KEY `idx_ca_company` (`company_id`),
  KEY `idx_ca_user` (`user_id`),
  CONSTRAINT `company_admins_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `company_admins_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


DROP TABLE IF EXISTS `company_admin_requests`;
CREATE TABLE `company_admin_requests` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `company_id` int(11) NOT NULL,
  `requester_email` varchar(255) NOT NULL,
  `requester_name` varchar(255) NOT NULL,
  `status` enum('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  `requested_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `approved_by` int(11) DEFAULT NULL,
  `approved_at` timestamp NULL DEFAULT NULL,
  `notes` text DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_req_user` (`user_id`),
  KEY `idx_req_company` (`company_id`),
  KEY `idx_req_status` (`status`),
  KEY `approved_by` (`approved_by`),
  CONSTRAINT `company_admin_requests_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `company_admin_requests_ibfk_2` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `company_admin_requests_ibfk_3` FOREIGN KEY (`approved_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


DROP TABLE IF EXISTS `company_benefits_config`;
CREATE TABLE `company_benefits_config` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `company_id` int(11) NOT NULL,
  `eap_provider_name` varchar(150) DEFAULT 'Mental Health Change Global EAP',
  `insurance_partner` varchar(150) DEFAULT 'Irish Life Health / VHI / Laya Healthcare',
  `policy_number` varchar(100) DEFAULT NULL,
  `custom_instructions` text DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_ben_company` (`company_id`),
  CONSTRAINT `company_benefits_config_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


DROP TABLE IF EXISTS `company_departments`;
CREATE TABLE `company_departments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `company_id` int(11) NOT NULL,
  `name` varchar(150) NOT NULL,
  `code` varchar(50) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_dept_company` (`company_id`),
  CONSTRAINT `company_departments_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


DROP TABLE IF EXISTS `company_invitations`;
CREATE TABLE `company_invitations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `company_id` int(11) NOT NULL,
  `token` varchar(64) NOT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `created_by` int(11) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `token` (`token`),
  KEY `company_id` (`company_id`),
  KEY `created_by` (`created_by`),
  KEY `idx_comp_inv_token` (`token`,`is_active`),
  CONSTRAINT `company_invitations_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `company_invitations_ibfk_2` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- -----------------------------------------------------------------------------
-- 4. UNIVERSAL PRE-AUTHORIZED EMAILS (ALLOWLIST ENGINE)
-- -----------------------------------------------------------------------------
DROP TABLE IF EXISTS `preauthorized_emails`;
CREATE TABLE `preauthorized_emails` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `email` varchar(255) NOT NULL,
  `role_id` int(11) NOT NULL,
  `company_id` int(11) DEFAULT NULL,
  `provider_classification` enum('counsellor','coach','unassigned') DEFAULT 'unassigned',
  `token` varchar(64) NOT NULL,
  `status` enum('pending','registered','revoked','expired') DEFAULT 'pending',
  `sessions_quota` int(11) DEFAULT 8,
  `notes` text DEFAULT NULL,
  `created_by_user_id` int(11) DEFAULT NULL,
  `registered_user_id` int(11) DEFAULT NULL,
  `registered_at` datetime DEFAULT NULL,
  `expires_at` datetime DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `token` (`token`),
  KEY `idx_preauth_email_role` (`email`,`role_id`,`status`),
  KEY `idx_preauth_token` (`token`),
  KEY `idx_preauth_company` (`company_id`),
  KEY `created_by_user_id` (`created_by_user_id`),
  KEY `registered_user_id` (`registered_user_id`),
  CONSTRAINT `preauth_emails_ibfk_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE SET NULL,
  CONSTRAINT `preauth_emails_ibfk_creator` FOREIGN KEY (`created_by_user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `preauth_emails_ibfk_reg_user` FOREIGN KEY (`registered_user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- -----------------------------------------------------------------------------
-- 5. PROVIDERS (SPECIALISTS) & CREDENTIALING GOVERNANCE
-- -----------------------------------------------------------------------------
DROP TABLE IF EXISTS `providers`;
CREATE TABLE `providers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `first_name` varchar(100) DEFAULT NULL,
  `last_name` varchar(100) DEFAULT NULL,
  `date_of_birth` date DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `classification` enum('counsellor','coach') NOT NULL DEFAULT 'counsellor',
  `bio` text DEFAULT NULL,
  `bio_status` enum('pending_approval','approved','changes_requested') NOT NULL DEFAULT 'pending_approval',
  `bio_locked` tinyint(1) NOT NULL DEFAULT 1,
  `specialties` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`specialties`)),
  `languages` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`languages`)),
  `topics_of_focus` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`topics_of_focus`)),
  `gender` varchar(50) DEFAULT NULL,
  `session_duration` int(11) DEFAULT 50,
  `buffer_time` int(11) DEFAULT 30,
  `max_clients` int(11) DEFAULT 10,
  `current_clients` int(11) DEFAULT 0,
  `timezone` varchar(100) DEFAULT 'Europe/Dublin',
  `bank_iban` varchar(60) DEFAULT NULL,
  `bank_bic_swift` varchar(30) DEFAULT NULL,
  `bank_name` varchar(150) DEFAULT NULL,
  `bank_address` text DEFAULT NULL,
  `tax_reference_number` varchar(60) DEFAULT NULL,
  `vat_status` varchar(30) DEFAULT 'exempt',
  `vat_rate_pct` decimal(5,2) DEFAULT 0.00,
  `video_provider` enum('zoom','meet','both') DEFAULT 'zoom',
  `zoom_link` varchar(255) DEFAULT NULL,
  `meet_link` varchar(255) DEFAULT NULL,
  `onboarding_step` enum('account','forms','documents','under_review','approved') NOT NULL DEFAULT 'account',
  `approval_status` enum('draft','submitted','in_review','changes_requested','approved','rejected') NOT NULL DEFAULT 'draft',
  `is_match_ready` tinyint(1) NOT NULL DEFAULT 0,
  `clinical_notes` text DEFAULT NULL,
  `approved_by` int(11) DEFAULT NULL,
  `approved_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_id` (`user_id`),
  KEY `idx_providers_status_match` (`approval_status`,`is_match_ready`,`classification`),
  KEY `approved_by` (`approved_by`),
  CONSTRAINT `providers_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `providers_ibfk_2` FOREIGN KEY (`approved_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


DROP TABLE IF EXISTS `provider_availability`;
CREATE TABLE `provider_availability` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `provider_id` int(11) NOT NULL,
  `day_of_week` tinyint(4) NOT NULL,
  `day_name` varchar(20) DEFAULT NULL,
  `start_time` time NOT NULL DEFAULT '09:00:00',
  `end_time` time NOT NULL DEFAULT '17:00:00',
  `active` tinyint(1) DEFAULT 1,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_provider_day` (`provider_id`,`day_of_week`),
  KEY `idx_availability_provider` (`provider_id`),
  CONSTRAINT `provider_availability_ibfk_1` FOREIGN KEY (`provider_id`) REFERENCES `providers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


DROP TABLE IF EXISTS `provider_documents`;
CREATE TABLE `provider_documents` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `provider_id` int(11) NOT NULL,
  `document_type` enum('photo','insurance','qualifications','membership','id_proof','address_proof','cpd_evidence','garda_vetting','experience_proof','references','tax_vat_cert') NOT NULL,
  `file_path` varchar(255) NOT NULL,
  `file_name` varchar(255) NOT NULL,
  `file_size` int(11) DEFAULT 0,
  `mime_type` varchar(100) DEFAULT 'application/pdf',
  `verification_status` enum('pending','verified','rejected') DEFAULT 'pending',
  `verified_by` int(11) DEFAULT NULL,
  `verified_at` datetime DEFAULT NULL,
  `rejection_reason` text DEFAULT NULL,
  `uploaded_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_prov_doc_type` (`provider_id`,`document_type`),
  KEY `verified_by` (`verified_by`),
  CONSTRAINT `provider_docs_ibfk_provider` FOREIGN KEY (`provider_id`) REFERENCES `providers` (`id`) ON DELETE CASCADE,
  CONSTRAINT `provider_docs_ibfk_verifier` FOREIGN KEY (`verified_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


DROP TABLE IF EXISTS `provider_references`;
CREATE TABLE `provider_references` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `provider_id` int(11) NOT NULL,
  `name` varchar(150) NOT NULL,
  `relationship` varchar(100) NOT NULL,
  `email` varchar(255) NOT NULL,
  `phone` varchar(50) DEFAULT NULL,
  `organization` varchar(150) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_prov_refs_prov` (`provider_id`),
  CONSTRAINT `provider_refs_ibfk_provider` FOREIGN KEY (`provider_id`) REFERENCES `providers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


DROP TABLE IF EXISTS `provider_bio_change_requests`;
CREATE TABLE `provider_bio_change_requests` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `provider_id` int(11) NOT NULL,
  `proposed_bio` text NOT NULL,
  `reason` text NOT NULL,
  `status` enum('pending','approved','rejected') DEFAULT 'pending',
  `requested_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `reviewed_by` int(11) DEFAULT NULL,
  `reviewed_at` datetime DEFAULT NULL,
  `admin_response` text DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_bio_req_provider` (`provider_id`),
  KEY `idx_bio_req_reviewer` (`reviewed_by`),
  CONSTRAINT `provider_bio_req_ibfk_provider` FOREIGN KEY (`provider_id`) REFERENCES `providers` (`id`) ON DELETE CASCADE,
  CONSTRAINT `provider_bio_req_ibfk_reviewer` FOREIGN KEY (`reviewed_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- -----------------------------------------------------------------------------
-- 6. CLIENTS, TOPICS & CLINICAL RISK
-- -----------------------------------------------------------------------------
DROP TABLE IF EXISTS `clients`;
CREATE TABLE `clients` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `provider_id` int(11) DEFAULT NULL,
  `company_id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `first_name` varchar(100) DEFAULT NULL,
  `last_name` varchar(100) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `employer_email` varchar(255) DEFAULT NULL,
  `contact_email` varchar(255) DEFAULT NULL,
  `email_changed` tinyint(1) DEFAULT 0,
  `type` enum('corporate') NOT NULL DEFAULT 'corporate',
  `service_preference` enum('coaching','counselling','both') DEFAULT NULL,
  `primary_topic` varchar(100) DEFAULT NULL,
  `secondary_topics` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`secondary_topics`)),
  `triage_level` enum('wellbeing','clinical','crisis_risk') DEFAULT 'wellbeing',
  `crisis_risk_flag` tinyint(1) DEFAULT 0,
  `status` enum('new_match','active','engaged','inactive') DEFAULT 'new_match',
  `phone` varchar(50) DEFAULT NULL,
  `city` varchar(100) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `country` varchar(100) DEFAULT 'Ireland',
  `total_sessions_allowed` int(11) DEFAULT 8,
  `sessions_used` int(11) DEFAULT 0,
  `start_date` date DEFAULT NULL,
  `renewal_date` date DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `next_of_kin_name` varchar(255) DEFAULT NULL,
  `next_of_kin_phone` varchar(50) DEFAULT NULL,
  `gp_name` varchar(255) DEFAULT NULL,
  `gp_phone` varchar(50) DEFAULT NULL,
  `intake_completed` tinyint(1) DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_id` (`user_id`),
  KEY `idx_clients_provider` (`provider_id`),
  KEY `idx_clients_company` (`company_id`),
  KEY `idx_clients_status` (`status`),
  KEY `idx_clients_employer_email` (`employer_email`),
  KEY `idx_clients_crisis_flag` (`crisis_risk_flag`),
  CONSTRAINT `clients_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `clients_ibfk_2` FOREIGN KEY (`provider_id`) REFERENCES `providers` (`id`) ON DELETE SET NULL,
  CONSTRAINT `clients_ibfk_3` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


DROP TABLE IF EXISTS `client_interests`;
CREATE TABLE `client_interests` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `client_id` int(11) NOT NULL,
  `topic` varchar(100) NOT NULL,
  `weight` int(11) DEFAULT 100,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `client_id` (`client_id`),
  CONSTRAINT `client_interests_ibfk_1` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


DROP TABLE IF EXISTS `client_selected_topics`;
CREATE TABLE `client_selected_topics` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `client_id` int(11) NOT NULL,
  `category` enum('Emotional','Professional','Relationships','Physical','Financial') NOT NULL,
  `topic_name` varchar(100) NOT NULL,
  `is_primary` tinyint(1) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `client_id` (`client_id`),
  CONSTRAINT `client_selected_topics_ibfk_1` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


DROP TABLE IF EXISTS `alerts`;
CREATE TABLE `alerts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `client_id` int(11) NOT NULL,
  `type` enum('crisis_risk','screen_wellbeing') NOT NULL,
  `severity` enum('critical','warning','info') NOT NULL DEFAULT 'warning',
  `status` enum('active','resolved') NOT NULL DEFAULT 'active',
  `trigger_reason` varchar(255) NOT NULL,
  `notes` text DEFAULT NULL,
  `resolved_by` int(11) DEFAULT NULL,
  `resolved_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `resolved_by` (`resolved_by`),
  KEY `idx_alerts_client` (`client_id`,`status`),
  KEY `idx_alerts_status` (`status`,`severity`),
  CONSTRAINT `alerts_ibfk_1` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE,
  CONSTRAINT `alerts_ibfk_2` FOREIGN KEY (`resolved_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


DROP TABLE IF EXISTS `crisis_assessments`;
CREATE TABLE `crisis_assessments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `client_id` int(11) NOT NULL,
  `q1_intent` tinyint(1) NOT NULL,
  `q2_safety` tinyint(1) NOT NULL,
  `q3_support` tinyint(1) NOT NULL,
  `result` enum('safe','high_risk') NOT NULL,
  `notes` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `client_id` (`client_id`),
  CONSTRAINT `crisis_assessments_ibfk_1` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


DROP TABLE IF EXISTS `questionnaires_results`;
CREATE TABLE `questionnaires_results` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `client_id` int(11) NOT NULL,
  `client_name` varchar(255) DEFAULT NULL,
  `type` enum('PHQ9','GAD7','WHO5') NOT NULL,
  `score` int(11) NOT NULL,
  `risk_level` enum('minimal','mild','moderate','moderately_severe','severe') DEFAULT NULL,
  `recommendation` enum('coaching','counselling','both') DEFAULT NULL,
  `answers_json` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`answers_json`)),
  `administered_by` int(11) DEFAULT NULL,
  `administered_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `administered_by` (`administered_by`),
  KEY `idx_questionnaires_client` (`client_id`,`type`),
  CONSTRAINT `questionnaires_results_ibfk_1` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE,
  CONSTRAINT `questionnaires_results_ibfk_2` FOREIGN KEY (`administered_by`) REFERENCES `providers` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- -----------------------------------------------------------------------------
-- 7. APPOINTMENTS, SAFETY CARE PLANS & INVOICING
-- -----------------------------------------------------------------------------
DROP TABLE IF EXISTS `appointments`;
CREATE TABLE `appointments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `client_id` int(11) NOT NULL,
  `provider_id` int(11) NOT NULL,
  `client_name` varchar(255) DEFAULT NULL,
  `scheduled_at` datetime NOT NULL,
  `duration_min` int(11) DEFAULT 50,
  `type` enum('therapy','coaching','intake') DEFAULT 'therapy',
  `video_provider` enum('zoom','meet') DEFAULT 'zoom',
  `video_link` varchar(255) DEFAULT NULL,
  `status` enum('scheduled','completed','cancelled','late_cancel') DEFAULT 'scheduled',
  `notes` text DEFAULT NULL,
  `change_request_type` enum('none','reschedule','cancel') DEFAULT 'none',
  `change_request_reason` text DEFAULT NULL,
  `change_request_notice` enum('more_24h','less_24h') DEFAULT NULL,
  `change_request_at` datetime DEFAULT NULL,
  `invoice_id` int(11) DEFAULT NULL,
  `attendance` tinyint(1) DEFAULT NULL COMMENT '1 = attended, 0 = did not attend (NULL = not recorded)',
  `rating_quality` tinyint(1) DEFAULT NULL COMMENT 'Provider rating 1-5: quality of the session',
  `rating_connection` tinyint(1) DEFAULT NULL COMMENT 'Provider rating 1-5: connection with the client',
  `rating_progress` tinyint(1) DEFAULT NULL COMMENT 'Provider rating 1-5: perceived client progress',
  `rating_client_prep` tinyint(1) DEFAULT NULL COMMENT 'Provider rating 1-5: client preparedness',
  `rating_overall` tinyint(1) DEFAULT NULL COMMENT 'Provider rating 1-5: overall session satisfaction',
  PRIMARY KEY (`id`),
  KEY `idx_appointments_provider` (`provider_id`),
  KEY `idx_appointments_client` (`client_id`),
  KEY `idx_appointments_status` (`status`),
  KEY `idx_appointments_scheduled` (`scheduled_at`),
  CONSTRAINT `appointments_ibfk_1` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE,
  CONSTRAINT `appointments_ibfk_2` FOREIGN KEY (`provider_id`) REFERENCES `providers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


DROP TABLE IF EXISTS `safety_care_plans`;
CREATE TABLE `safety_care_plans` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `client_id` int(11) NOT NULL,
  `provider_id` int(11) NOT NULL,
  `session_id` int(11) DEFAULT NULL,
  `coping_strategies` text DEFAULT NULL,
  `support_contacts` text DEFAULT NULL,
  `emergency_contacts` text DEFAULT NULL,
  `environment_safety` text DEFAULT NULL,
  `is_confirmed` tinyint(1) DEFAULT 0,
  `confirmed_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `client_id` (`client_id`),
  KEY `provider_id` (`provider_id`),
  KEY `session_id` (`session_id`),
  CONSTRAINT `safety_care_plans_ibfk_1` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE,
  CONSTRAINT `safety_care_plans_ibfk_2` FOREIGN KEY (`provider_id`) REFERENCES `providers` (`id`) ON DELETE CASCADE,
  CONSTRAINT `safety_care_plans_ibfk_3` FOREIGN KEY (`session_id`) REFERENCES `appointments` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


DROP TABLE IF EXISTS `invoices`;
CREATE TABLE `invoices` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `session_id` int(11) DEFAULT NULL,
  `provider_id` int(11) DEFAULT NULL,
  `client_id` int(11) DEFAULT NULL,
  `company_id` int(11) DEFAULT NULL,
  `client_name` varchar(255) DEFAULT NULL,
  `amount` decimal(10,2) NOT NULL,
  `currency` varchar(10) DEFAULT 'EUR',
  `status` enum('pending','submitted','paid') DEFAULT 'pending',
  `pdf_path` varchar(255) DEFAULT NULL,
  `clinical_notes` text DEFAULT NULL,
  `rating_score` tinyint(4) DEFAULT NULL,
  `rating_feedback` text DEFAULT NULL,
  `submitted_at` datetime DEFAULT NULL,
  `paid_at` datetime DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `session_id` (`session_id`),
  KEY `client_id` (`client_id`),
  KEY `company_id` (`company_id`),
  KEY `idx_invoices_provider` (`provider_id`),
  KEY `idx_invoices_status` (`status`),
  CONSTRAINT `invoices_ibfk_1` FOREIGN KEY (`session_id`) REFERENCES `appointments` (`id`) ON DELETE SET NULL,
  CONSTRAINT `invoices_ibfk_2` FOREIGN KEY (`provider_id`) REFERENCES `providers` (`id`) ON DELETE SET NULL,
  CONSTRAINT `invoices_ibfk_3` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE SET NULL,
  CONSTRAINT `invoices_ibfk_4` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


DROP TABLE IF EXISTS `payments`;
CREATE TABLE `payments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `invoice_id` int(11) NOT NULL,
  `method` enum('transfer','manual') NOT NULL DEFAULT 'transfer',
  `transaction_id` varchar(255) DEFAULT NULL,
  `receipt_path` varchar(255) DEFAULT NULL,
  `registered_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `invoice_id` (`invoice_id`),
  CONSTRAINT `payments_ibfk_1` FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- -----------------------------------------------------------------------------
-- 8. COMMUNICATIONS, NOTIFICATIONS & CONTENT
-- -----------------------------------------------------------------------------
DROP TABLE IF EXISTS `messages`;
CREATE TABLE `messages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sender_id` int(11) NOT NULL,
  `sender_name` varchar(255) DEFAULT NULL,
  `receiver_id` int(11) NOT NULL,
  `receiver_name` varchar(255) DEFAULT NULL,
  `content` text NOT NULL,
  `is_read` tinyint(1) DEFAULT 0,
  `read_at` datetime DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `deleted_by_sender` tinyint(1) NOT NULL DEFAULT 0,
  `deleted_by_receiver` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_messages_receiver` (`receiver_id`,`is_read`),
  KEY `idx_messages_sender` (`sender_id`),
  CONSTRAINT `messages_ibfk_1` FOREIGN KEY (`sender_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `messages_ibfk_2` FOREIGN KEY (`receiver_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


DROP TABLE IF EXISTS `email_queue`;
CREATE TABLE `email_queue` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `to_email` varchar(255) NOT NULL,
  `to_name` varchar(255) DEFAULT NULL,
  `subject` varchar(500) NOT NULL,
  `body_html` longtext NOT NULL,
  `body_text` text DEFAULT NULL,
  `status` enum('pending','sent','failed') DEFAULT 'pending',
  `attempts` tinyint(4) DEFAULT 0,
  `error_msg` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `sent_at` timestamp NULL DEFAULT NULL,
  `metadata` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'Optional JSON: {type, ref_id, ...}' CHECK (json_valid(`metadata`)),
  PRIMARY KEY (`id`),
  KEY `idx_email_queue_status` (`status`,`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


DROP TABLE IF EXISTS `faq_categories`;
CREATE TABLE `faq_categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `slug` varchar(50) NOT NULL,
  `label` varchar(100) NOT NULL,
  `icon` varchar(20) DEFAULT '❓',
  `display_order` int(11) DEFAULT 0,
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


DROP TABLE IF EXISTS `faqs`;
CREATE TABLE `faqs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `category_id` int(11) NOT NULL,
  `target_role` enum('all','client','provider','company','admin') NOT NULL DEFAULT 'all',
  `question` varchar(500) NOT NULL,
  `answer` text NOT NULL,
  `keywords` varchar(255) DEFAULT NULL,
  `display_order` int(11) DEFAULT 0,
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `category_id` (`category_id`),
  KEY `idx_faqs_role_cat` (`target_role`,`category_id`,`is_active`,`display_order`),
  CONSTRAINT `faqs_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `faq_categories` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


DROP TABLE IF EXISTS `hub_resources`;
CREATE TABLE `hub_resources` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `slug` varchar(255) NOT NULL,
  `category` enum('crisis_stress','professional_health','deib','templates','leadership') NOT NULL,
  `target_audience` enum('admins','managers','employees','all') NOT NULL DEFAULT 'all',
  `description` text NOT NULL,
  `author_title` varchar(150) DEFAULT 'MHC Clinical Operations Team',
  `read_time_min` int(11) DEFAULT 5,
  `file_type` varchar(20) DEFAULT 'PDF',
  `file_url` varchar(255) DEFAULT NULL,
  `tags` varchar(255) DEFAULT NULL,
  `download_count` int(11) DEFAULT 0,
  `is_published` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `slug` (`slug`),
  KEY `idx_res_category` (`category`),
  KEY `idx_res_audience` (`target_audience`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET foreign_key_checks = 1;