-- NexoIT Platform Database Schema
-- Active schema aligned with the current web application
-- Excludes legacy structures from `inspiratie`
-- Charset: utf8mb4

CREATE DATABASE IF NOT EXISTS `nexoIT_platform`
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

USE `nexoIT_platform`;

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS `support_attachments`;
DROP TABLE IF EXISTS `support_thread_reads`;
DROP TABLE IF EXISTS `company_profile_change_requests`;
DROP TABLE IF EXISTS `support_messages`;
DROP TABLE IF EXISTS `support_threads`;
DROP TABLE IF EXISTS `platform_notification_reads`;
DROP TABLE IF EXISTS `platform_notifications`;
DROP TABLE IF EXISTS `password_reset_tokens`;
DROP TABLE IF EXISTS `promo_code_requests`;
DROP TABLE IF EXISTS `company_reviews`;
DROP TABLE IF EXISTS `company_reminder_templates`;
DROP TABLE IF EXISTS `whatsapp_jobs`;
DROP TABLE IF EXISTS `whatsapp_settings`;
DROP TABLE IF EXISTS `whatsapp_reminder_logs`;
DROP TABLE IF EXISTS `transport_trip_vehicle_list`;
DROP TABLE IF EXISTS `transport_passenger_records`;
DROP TABLE IF EXISTS `transport_records`;
DROP TABLE IF EXISTS `logistic_vehicle_documents`;
DROP TABLE IF EXISTS `logistic_records`;
DROP TABLE IF EXISTS `regim_room_slots`;
DROP TABLE IF EXISTS `regim_records`;
DROP TABLE IF EXISTS `fitness_records`;
DROP TABLE IF EXISTS `cafe_records`;
DROP TABLE IF EXISTS `beauty_records`;
DROP TABLE IF EXISTS `medical_records`;
DROP TABLE IF EXISTS `auto_service_records`;
DROP TABLE IF EXISTS `itp_records`;
DROP TABLE IF EXISTS `appointments`;
DROP TABLE IF EXISTS `subscription_payments`;
DROP TABLE IF EXISTS `subscriptions`;
DROP TABLE IF EXISTS `trial_periods`;
DROP TABLE IF EXISTS `offer_requests`;
DROP TABLE IF EXISTS `company_module_access`;
DROP TABLE IF EXISTS `service_modules`;
DROP TABLE IF EXISTS `users`;
DROP TABLE IF EXISTS `companies`;
DROP TABLE IF EXISTS `custom_services`;
DROP TABLE IF EXISTS `subscription_plans`;
DROP TABLE IF EXISTS `company_categories`;
DROP TABLE IF EXISTS `roles`;

CREATE TABLE `roles` (
    `id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `code` VARCHAR(32) NOT NULL,
    `name` VARCHAR(64) NOT NULL,
    `description` VARCHAR(255) DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_roles_code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `company_categories` (
    `id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `slug` VARCHAR(64) NOT NULL,
    `name` VARCHAR(120) NOT NULL,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_company_categories_slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `subscription_plans` (
    `id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `plan_key` VARCHAR(32) NOT NULL,
    `name` VARCHAR(64) NOT NULL,
    `duration_months` SMALLINT UNSIGNED NOT NULL,
    `price_eur` DECIMAL(10,2) NOT NULL,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_subscription_plans_key` (`plan_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `custom_services` (
    `id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `service_key` VARCHAR(64) NOT NULL,
    `service_name` VARCHAR(160) NOT NULL,
    `service_type` ENUM('custom_offer','custom_offer_general') NOT NULL DEFAULT 'custom_offer',
    `starting_price_eur` DECIMAL(10,2) DEFAULT NULL,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_custom_services_key` (`service_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `companies` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `category_id` SMALLINT UNSIGNED DEFAULT NULL,
    `company_name` VARCHAR(180) NOT NULL,
    `founder_name` VARCHAR(160) NOT NULL,
    `contact_email` VARCHAR(190) NOT NULL,
    `contact_phone` VARCHAR(32) DEFAULT NULL,
    `transport_type` ENUM('transport_persoane','transport_marfa') DEFAULT NULL,
    `account_type` ENUM('trial','client') NOT NULL DEFAULT 'trial',
    `status` ENUM('trial','active','expired','inactive','suspended') NOT NULL DEFAULT 'trial',
    `trial_start_at` DATETIME DEFAULT NULL,
    `trial_end_at` DATETIME DEFAULT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_companies_contact_email` (`contact_email`),
    KEY `idx_companies_category` (`category_id`),
    KEY `idx_companies_status` (`status`),
    CONSTRAINT `fk_companies_category`
        FOREIGN KEY (`category_id`) REFERENCES `company_categories` (`id`)
        ON UPDATE CASCADE
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `users` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `company_id` BIGINT UNSIGNED DEFAULT NULL,
    `role_id` TINYINT UNSIGNED NOT NULL,
    `full_name` VARCHAR(160) NOT NULL,
    `email` VARCHAR(190) NOT NULL,
    `phone` VARCHAR(32) DEFAULT NULL,
    `password_hash` VARCHAR(255) NOT NULL,
    `email_verified_at` DATETIME DEFAULT NULL,
    `remember_token` VARCHAR(100) DEFAULT NULL,
    `account_status` ENUM('pending','active','expired','inactive','suspended') NOT NULL DEFAULT 'pending',
    `trial_starts_at` DATETIME DEFAULT NULL,
    `trial_expires_at` DATETIME DEFAULT NULL,
    `last_login_at` DATETIME DEFAULT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_users_email` (`email`),
    KEY `idx_users_company` (`company_id`),
    KEY `idx_users_role` (`role_id`),
    KEY `idx_users_account_status` (`account_status`),
    CONSTRAINT `fk_users_company`
        FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`)
        ON UPDATE CASCADE
        ON DELETE SET NULL,
    CONSTRAINT `fk_users_role`
        FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `password_reset_tokens` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` BIGINT UNSIGNED NOT NULL,
    `email` VARCHAR(190) NOT NULL,
    `token_hash` CHAR(64) NOT NULL,
    `expires_at` DATETIME NOT NULL,
    `used_at` DATETIME DEFAULT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_password_reset_tokens_hash` (`token_hash`),
    KEY `idx_password_reset_tokens_user` (`user_id`),
    KEY `idx_password_reset_tokens_email` (`email`),
    KEY `idx_password_reset_tokens_expires` (`expires_at`),
    KEY `idx_password_reset_tokens_used` (`used_at`),
    CONSTRAINT `fk_password_reset_tokens_user`
        FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `service_modules` (
    `id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `module_key` VARCHAR(64) NOT NULL,
    `module_name` VARCHAR(160) NOT NULL,
    `module_group` ENUM('subscription','custom_offer') NOT NULL DEFAULT 'subscription',
    `description` VARCHAR(255) DEFAULT NULL,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_service_modules_key` (`module_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `company_module_access` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `company_id` BIGINT UNSIGNED NOT NULL,
    `module_id` SMALLINT UNSIGNED NOT NULL,
    `is_enabled` TINYINT(1) NOT NULL DEFAULT 1,
    `enabled_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `disabled_at` DATETIME DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_company_module_access` (`company_id`, `module_id`),
    KEY `idx_company_module_access_module` (`module_id`),
    CONSTRAINT `fk_company_module_access_company`
        FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT `fk_company_module_access_module`
        FOREIGN KEY (`module_id`) REFERENCES `service_modules` (`id`)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `offer_requests` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `assigned_admin_user_id` BIGINT UNSIGNED DEFAULT NULL,
    `service_id` SMALLINT UNSIGNED DEFAULT NULL,
    `service_key` VARCHAR(64) NOT NULL,
    `service_name` VARCHAR(160) NOT NULL,
    `founder_name` VARCHAR(160) NOT NULL,
    `company_name` VARCHAR(180) NOT NULL,
    `email` VARCHAR(190) NOT NULL,
    `phone` VARCHAR(32) DEFAULT NULL,
    `project_details` TEXT NOT NULL,
    `status` ENUM('new','in_review','offer_sent','won','lost','closed') NOT NULL DEFAULT 'new',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_offer_requests_admin` (`assigned_admin_user_id`),
    KEY `idx_offer_requests_service` (`service_id`),
    KEY `idx_offer_requests_status` (`status`),
    CONSTRAINT `fk_offer_requests_admin`
        FOREIGN KEY (`assigned_admin_user_id`) REFERENCES `users` (`id`)
        ON UPDATE CASCADE
        ON DELETE SET NULL,
    CONSTRAINT `fk_offer_requests_service`
        FOREIGN KEY (`service_id`) REFERENCES `custom_services` (`id`)
        ON UPDATE CASCADE
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `trial_periods` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `company_id` BIGINT UNSIGNED NOT NULL,
    `user_id` BIGINT UNSIGNED NOT NULL,
    `started_at` DATETIME NOT NULL,
    `expires_at` DATETIME NOT NULL,
    `status` ENUM('active','expired','converted','cancelled') NOT NULL DEFAULT 'active',
    `notes` VARCHAR(255) DEFAULT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_trial_periods_company` (`company_id`),
    KEY `idx_trial_periods_user` (`user_id`),
    KEY `idx_trial_periods_status` (`status`),
    CONSTRAINT `fk_trial_periods_company`
        FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT `fk_trial_periods_user`
        FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `subscriptions` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `company_id` BIGINT UNSIGNED NOT NULL,
    `plan_id` SMALLINT UNSIGNED NOT NULL,
    `requested_by_user_id` BIGINT UNSIGNED DEFAULT NULL,
    `status` ENUM('pending','active','expired','cancelled') NOT NULL DEFAULT 'pending',
    `amount_eur` DECIMAL(10,2) NOT NULL,
    `currency` CHAR(3) NOT NULL DEFAULT 'EUR',
    `starts_at` DATETIME DEFAULT NULL,
    `ends_at` DATETIME DEFAULT NULL,
    `activated_at` DATETIME DEFAULT NULL,
    `cancelled_at` DATETIME DEFAULT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_subscriptions_company` (`company_id`),
    KEY `idx_subscriptions_plan` (`plan_id`),
    KEY `idx_subscriptions_request_user` (`requested_by_user_id`),
    KEY `idx_subscriptions_status` (`status`),
    CONSTRAINT `fk_subscriptions_company`
        FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT `fk_subscriptions_plan`
        FOREIGN KEY (`plan_id`) REFERENCES `subscription_plans` (`id`)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
    CONSTRAINT `fk_subscriptions_requested_by_user`
        FOREIGN KEY (`requested_by_user_id`) REFERENCES `users` (`id`)
        ON UPDATE CASCADE
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `subscription_payments` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `subscription_id` BIGINT UNSIGNED NOT NULL,
    `company_id` BIGINT UNSIGNED NOT NULL,
    `user_id` BIGINT UNSIGNED DEFAULT NULL,
    `amount_eur` DECIMAL(10,2) NOT NULL,
    `currency` CHAR(3) NOT NULL DEFAULT 'EUR',
    `payment_method` VARCHAR(64) DEFAULT NULL,
    `payment_status` ENUM('pending','paid','failed','refunded') NOT NULL DEFAULT 'pending',
    `transaction_reference` VARCHAR(120) DEFAULT NULL,
    `paid_at` DATETIME DEFAULT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_subscription_payments_reference` (`transaction_reference`),
    KEY `idx_subscription_payments_subscription` (`subscription_id`),
    KEY `idx_subscription_payments_company` (`company_id`),
    KEY `idx_subscription_payments_user` (`user_id`),
    CONSTRAINT `fk_subscription_payments_subscription`
        FOREIGN KEY (`subscription_id`) REFERENCES `subscriptions` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT `fk_subscription_payments_company`
        FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT `fk_subscription_payments_user`
        FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
        ON UPDATE CASCADE
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `appointments` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `company_id` BIGINT UNSIGNED NOT NULL,
    `appointment_scope` ENUM('itp','service_auto','medical','regim_hotelier','beauty','fitness','logistica','transport','cafe') NOT NULL DEFAULT 'itp',
    `created_by_user_id` BIGINT UNSIGNED DEFAULT NULL,
    `client_name` VARCHAR(160) NOT NULL,
    `client_phone` VARCHAR(32) DEFAULT NULL,
    `client_email` VARCHAR(190) DEFAULT NULL,
    `guest_count` SMALLINT UNSIGNED DEFAULT NULL,
    `table_label` VARCHAR(120) DEFAULT NULL,
    `client_pickup_time` TIME DEFAULT NULL,
    `client_city` VARCHAR(120) DEFAULT NULL,
    `client_county` VARCHAR(120) DEFAULT NULL,
    `client_street` VARCHAR(160) DEFAULT NULL,
    `client_street_number` VARCHAR(32) DEFAULT NULL,
    `client_building` VARCHAR(32) DEFAULT NULL,
    `client_staircase` VARCHAR(32) DEFAULT NULL,
    `service_name` VARCHAR(190) NOT NULL,
    `client_reference` VARCHAR(120) DEFAULT NULL,
    `transport_vehicle_id` BIGINT UNSIGNED DEFAULT NULL,
    `transport_vehicle_label` VARCHAR(160) DEFAULT NULL,
    `notes` TEXT DEFAULT NULL,
    `scheduled_date` DATE NOT NULL,
    `start_time` TIME NOT NULL,
    `end_time` TIME NOT NULL,
    `duration_minutes` SMALLINT UNSIGNED NOT NULL DEFAULT 30,
    `status` ENUM('scheduled','confirmed','completed','cancelled','no_show','deleted') NOT NULL DEFAULT 'scheduled',
    `notification_channel` ENUM('none','whatsapp','email','sms') NOT NULL DEFAULT 'whatsapp',
    `notification_status` ENUM('pending','queued','sent','failed','not_required') NOT NULL DEFAULT 'pending',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_appointments_company_slot` (`company_id`, `appointment_scope`, `scheduled_date`, `start_time`),
    KEY `idx_appointments_company` (`company_id`),
    KEY `idx_appointments_user` (`created_by_user_id`),
    KEY `idx_appointments_date` (`scheduled_date`),
    KEY `idx_appointments_status` (`status`),
    KEY `idx_appointments_scope` (`appointment_scope`),
    CONSTRAINT `fk_appointments_company`
        FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT `fk_appointments_created_by_user`
        FOREIGN KEY (`created_by_user_id`) REFERENCES `users` (`id`)
        ON UPDATE CASCADE
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `itp_records` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `company_id` BIGINT UNSIGNED NOT NULL,
    `created_by_user_id` BIGINT UNSIGNED DEFAULT NULL,
    `client_name` VARCHAR(160) NOT NULL,
    `phone` VARCHAR(32) DEFAULT NULL,
    `vehicle_vin` VARCHAR(32) DEFAULT NULL,
    `vehicle_brand` VARCHAR(120) DEFAULT NULL,
    `vehicle_model` VARCHAR(120) DEFAULT NULL,
    `vehicle_year` SMALLINT UNSIGNED DEFAULT NULL,
    `vehicle_engine` VARCHAR(120) DEFAULT NULL,
    `license_plate` VARCHAR(32) NOT NULL,
    `last_itp_date` DATETIME DEFAULT NULL,
    `itp_periodicity_key` VARCHAR(64) DEFAULT NULL,
    `itp_expiry_date` DATE DEFAULT NULL,
    `rca_expiry_date` DATE DEFAULT NULL,
    `status` ENUM('active','expiring_soon','expired','blocked','return') NOT NULL DEFAULT 'active',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_itp_records_company` (`company_id`),
    KEY `idx_itp_records_status` (`status`),
    KEY `idx_itp_records_license_plate` (`license_plate`),
    CONSTRAINT `fk_itp_records_company`
        FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT `fk_itp_records_created_by_user`
        FOREIGN KEY (`created_by_user_id`) REFERENCES `users` (`id`)
        ON UPDATE CASCADE
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `auto_service_records` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `company_id` BIGINT UNSIGNED NOT NULL,
    `created_by_user_id` BIGINT UNSIGNED DEFAULT NULL,
    `client_name` VARCHAR(160) NOT NULL,
    `phone` VARCHAR(32) DEFAULT NULL,
    `vehicle_vin` VARCHAR(32) DEFAULT NULL,
    `vehicle_brand` VARCHAR(120) DEFAULT NULL,
    `vehicle_model` VARCHAR(120) DEFAULT NULL,
    `vehicle_year` SMALLINT UNSIGNED DEFAULT NULL,
    `vehicle_engine` VARCHAR(120) DEFAULT NULL,
    `license_plate` VARCHAR(32) NOT NULL,
    `last_itp_date` DATETIME DEFAULT NULL,
    `itp_periodicity_key` VARCHAR(64) DEFAULT NULL,
    `itp_expiry_date` DATE DEFAULT NULL,
    `rca_expiry_date` DATE DEFAULT NULL,
    `status` ENUM('active','expiring_soon','expired','blocked','return') NOT NULL DEFAULT 'active',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_auto_service_records_company` (`company_id`),
    KEY `idx_auto_service_records_status` (`status`),
    KEY `idx_auto_service_records_license_plate` (`license_plate`),
    CONSTRAINT `fk_auto_service_records_company`
        FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT `fk_auto_service_records_created_by_user`
        FOREIGN KEY (`created_by_user_id`) REFERENCES `users` (`id`)
        ON UPDATE CASCADE
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `medical_records` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `company_id` BIGINT UNSIGNED NOT NULL,
    `created_by_user_id` BIGINT UNSIGNED DEFAULT NULL,
    `patient_name` VARCHAR(160) NOT NULL,
    `phone` VARCHAR(32) DEFAULT NULL,
    `email` VARCHAR(190) DEFAULT NULL,
    `service_name` VARCHAR(190) NOT NULL,
    `last_visit_at` DATETIME DEFAULT NULL,
    `next_visit_date` DATE DEFAULT NULL,
    `notes` TEXT DEFAULT NULL,
    `status` ENUM('active','follow_up','archived') NOT NULL DEFAULT 'active',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_medical_records_company` (`company_id`),
    KEY `idx_medical_records_status` (`status`),
    KEY `idx_medical_records_patient` (`patient_name`),
    CONSTRAINT `fk_medical_records_company`
        FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT `fk_medical_records_created_by_user`
        FOREIGN KEY (`created_by_user_id`) REFERENCES `users` (`id`)
        ON UPDATE CASCADE
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `beauty_records` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `company_id` BIGINT UNSIGNED NOT NULL,
    `created_by_user_id` BIGINT UNSIGNED DEFAULT NULL,
    `client_name` VARCHAR(160) NOT NULL,
    `phone` VARCHAR(32) DEFAULT NULL,
    `email` VARCHAR(190) DEFAULT NULL,
    `service_name` VARCHAR(190) NOT NULL,
    `last_visit_at` DATETIME DEFAULT NULL,
    `create_subscription` TINYINT(1) NOT NULL DEFAULT 0,
    `subscription_period` VARCHAR(32) DEFAULT NULL,
    `subscription_sessions` SMALLINT UNSIGNED DEFAULT NULL,
    `subscription_price` DECIMAL(10,2) DEFAULT NULL,
    `next_visit_date` DATE DEFAULT NULL,
    `completed_sessions` SMALLINT UNSIGNED DEFAULT NULL,
    `notes` TEXT DEFAULT NULL,
    `status` ENUM('active','follow_up','archived') NOT NULL DEFAULT 'active',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_beauty_records_company` (`company_id`),
    KEY `idx_beauty_records_status` (`status`),
    KEY `idx_beauty_records_client` (`client_name`),
    CONSTRAINT `fk_beauty_records_company`
        FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT `fk_beauty_records_created_by_user`
        FOREIGN KEY (`created_by_user_id`) REFERENCES `users` (`id`)
        ON UPDATE CASCADE
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `cafe_records` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `company_id` BIGINT UNSIGNED NOT NULL,
    `created_by_user_id` BIGINT UNSIGNED DEFAULT NULL,
    `client_name` VARCHAR(160) NOT NULL,
    `phone` VARCHAR(32) DEFAULT NULL,
    `email` VARCHAR(190) DEFAULT NULL,
    `preferred_area` VARCHAR(120) DEFAULT NULL,
    `preferred_table` VARCHAR(120) DEFAULT NULL,
    `average_party_size` SMALLINT UNSIGNED DEFAULT NULL,
    `favorite_items` VARCHAR(255) DEFAULT NULL,
    `last_visit_at` DATETIME DEFAULT NULL,
    `notes` TEXT DEFAULT NULL,
    `status` ENUM('new','vip','inactive') NOT NULL DEFAULT 'new',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_cafe_records_company` (`company_id`),
    KEY `idx_cafe_records_status` (`status`),
    KEY `idx_cafe_records_client` (`client_name`),
    CONSTRAINT `fk_cafe_records_company`
        FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT `fk_cafe_records_created_by_user`
        FOREIGN KEY (`created_by_user_id`) REFERENCES `users` (`id`)
        ON UPDATE CASCADE
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `fitness_records` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `company_id` BIGINT UNSIGNED NOT NULL,
    `created_by_user_id` BIGINT UNSIGNED DEFAULT NULL,
    `member_name` VARCHAR(160) NOT NULL,
    `phone` VARCHAR(32) DEFAULT NULL,
    `email` VARCHAR(190) DEFAULT NULL,
    `subscription_type` VARCHAR(120) NOT NULL,
    `subscription_start_date` DATE DEFAULT NULL,
    `subscription_duration` VARCHAR(32) DEFAULT NULL,
    `subscription_end_date` DATE DEFAULT NULL,
    `monthly_fee` DECIMAL(10,2) DEFAULT NULL,
    `notes` TEXT DEFAULT NULL,
    `status` ENUM('active','expiring_soon','expired','suspended') NOT NULL DEFAULT 'active',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_fitness_records_company` (`company_id`),
    KEY `idx_fitness_records_status` (`status`),
    KEY `idx_fitness_records_member` (`member_name`),
    CONSTRAINT `fk_fitness_records_company`
        FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT `fk_fitness_records_created_by_user`
        FOREIGN KEY (`created_by_user_id`) REFERENCES `users` (`id`)
        ON UPDATE CASCADE
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `logistic_records` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `company_id` BIGINT UNSIGNED NOT NULL,
    `created_by_user_id` BIGINT UNSIGNED DEFAULT NULL,
    `vehicle_label` VARCHAR(160) NOT NULL,
    `license_plate` VARCHAR(32) DEFAULT NULL,
    `driver_name` VARCHAR(160) NOT NULL,
    `phone` VARCHAR(32) DEFAULT NULL,
    `email` VARCHAR(190) DEFAULT NULL,
    `client_name` VARCHAR(160) NOT NULL,
    `route_from` VARCHAR(190) DEFAULT NULL,
    `route_to` VARCHAR(190) DEFAULT NULL,
    `transport_type` VARCHAR(120) NOT NULL,
    `departure_date` DATE DEFAULT NULL,
    `return_date` DATE DEFAULT NULL,
    `itp_last_date` DATE DEFAULT NULL,
    `itp_expiry_date` DATE DEFAULT NULL,
    `rca_provider` VARCHAR(160) DEFAULT NULL,
    `rca_policy_number` VARCHAR(120) DEFAULT NULL,
    `rca_last_date` DATE DEFAULT NULL,
    `rca_expiry_date` DATE DEFAULT NULL,
    `notes` TEXT DEFAULT NULL,
    `status` ENUM('active','in_transit','delivered','delayed','archived') NOT NULL DEFAULT 'active',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_logistic_records_company` (`company_id`),
    KEY `idx_logistic_records_status` (`status`),
    KEY `idx_logistic_records_vehicle` (`vehicle_label`),
    KEY `idx_logistic_records_driver` (`driver_name`),
    CONSTRAINT `fk_logistic_records_company`
        FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT `fk_logistic_records_created_by_user`
        FOREIGN KEY (`created_by_user_id`) REFERENCES `users` (`id`)
        ON UPDATE CASCADE
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `logistic_vehicle_documents` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `company_id` BIGINT UNSIGNED NOT NULL,
    `created_by_user_id` BIGINT UNSIGNED DEFAULT NULL,
    `license_plate` VARCHAR(32) NOT NULL,
    `vehicle_label` VARCHAR(160) DEFAULT NULL,
    `itp_last_date` DATE DEFAULT NULL,
    `itp_expiry_date` DATE DEFAULT NULL,
    `rca_last_date` DATE DEFAULT NULL,
    `rca_expiry_date` DATE DEFAULT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_logistic_vehicle_documents_company_plate` (`company_id`, `license_plate`),
    KEY `idx_logistic_vehicle_documents_company` (`company_id`),
    CONSTRAINT `fk_logistic_vehicle_documents_company`
        FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT `fk_logistic_vehicle_documents_created_by_user`
        FOREIGN KEY (`created_by_user_id`) REFERENCES `users` (`id`)
        ON UPDATE CASCADE
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `transport_records` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `company_id` BIGINT UNSIGNED NOT NULL,
    `created_by_user_id` BIGINT UNSIGNED DEFAULT NULL,
    `vehicle_label` VARCHAR(160) NOT NULL,
    `license_plate` VARCHAR(32) DEFAULT NULL,
    `driver_name` VARCHAR(160) NOT NULL,
    `phone` VARCHAR(32) DEFAULT NULL,
    `client_name` VARCHAR(160) NOT NULL,
    `pickup_location` VARCHAR(190) DEFAULT NULL,
    `delivery_location` VARCHAR(190) DEFAULT NULL,
    `transport_type` VARCHAR(120) NOT NULL,
    `cargo_details` VARCHAR(190) DEFAULT NULL,
    `cargo_weight` DECIMAL(10,2) DEFAULT NULL,
    `reference_code` VARCHAR(80) DEFAULT NULL,
    `departure_date` DATE DEFAULT NULL,
    `estimated_arrival_date` DATE DEFAULT NULL,
    `notes` TEXT DEFAULT NULL,
    `status` ENUM('active','loaded','in_transit','delivered','delayed','archived') NOT NULL DEFAULT 'active',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_transport_records_company` (`company_id`),
    KEY `idx_transport_records_status` (`status`),
    KEY `idx_transport_records_vehicle` (`vehicle_label`),
    KEY `idx_transport_records_driver` (`driver_name`),
    CONSTRAINT `fk_transport_records_company`
        FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT `fk_transport_records_created_by_user`
        FOREIGN KEY (`created_by_user_id`) REFERENCES `users` (`id`)
        ON UPDATE CASCADE
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `transport_passenger_records` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `company_id` BIGINT UNSIGNED NOT NULL,
    `created_by_user_id` BIGINT UNSIGNED DEFAULT NULL,
    `vehicle_label` VARCHAR(160) NOT NULL,
    `license_plate` VARCHAR(32) DEFAULT NULL,
    `driver_name` VARCHAR(160) NOT NULL,
    `phone` VARCHAR(32) DEFAULT NULL,
    `route_name` VARCHAR(160) NOT NULL,
    `departure_location` VARCHAR(190) NOT NULL,
    `arrival_location` VARCHAR(190) NOT NULL,
    `passenger_name` VARCHAR(160) DEFAULT NULL,
    `passenger_phone` VARCHAR(32) DEFAULT NULL,
    `travel_date` DATE DEFAULT NULL,
    `travel_time` TIME DEFAULT NULL,
    `seat_count` SMALLINT UNSIGNED DEFAULT NULL,
    `notes` TEXT DEFAULT NULL,
    `status` ENUM('scheduled','confirmed','in_trip','completed','cancelled','archived') NOT NULL DEFAULT 'scheduled',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_transport_passenger_records_company` (`company_id`),
    KEY `idx_transport_passenger_records_status` (`status`),
    KEY `idx_transport_passenger_records_vehicle` (`vehicle_label`),
    CONSTRAINT `fk_transport_passenger_records_company`
        FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT `fk_transport_passenger_records_created_by_user`
        FOREIGN KEY (`created_by_user_id`) REFERENCES `users` (`id`)
        ON UPDATE CASCADE
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `transport_trip_vehicle_list` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `company_id` BIGINT UNSIGNED NOT NULL,
    `created_by_user_id` BIGINT UNSIGNED DEFAULT NULL,
    `vehicle_label` VARCHAR(160) NOT NULL,
    `seat_count` SMALLINT UNSIGNED DEFAULT NULL,
    `license_plate` VARCHAR(32) DEFAULT NULL,
    `driver_name` VARCHAR(160) DEFAULT NULL,
    `route_direction` ENUM('tur','retur','tur_retur') NOT NULL DEFAULT 'tur',
    `departure_location` VARCHAR(160) DEFAULT NULL,
    `arrival_location` VARCHAR(160) DEFAULT NULL,
    `trip_duration_hours` DECIMAL(5,2) NOT NULL DEFAULT 1.00,
    `trip_date` DATE DEFAULT NULL,
    `status` ENUM('planificat','activ','rezerva','anulat') NOT NULL DEFAULT 'planificat',
    `notes` TEXT DEFAULT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_transport_trip_vehicle_list_company` (`company_id`),
    KEY `idx_transport_trip_vehicle_list_direction` (`route_direction`),
    KEY `idx_transport_trip_vehicle_list_status` (`status`),
    CONSTRAINT `fk_transport_trip_vehicle_list_company`
        FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT `fk_transport_trip_vehicle_list_created_by_user`
        FOREIGN KEY (`created_by_user_id`) REFERENCES `users` (`id`)
        ON UPDATE CASCADE
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `regim_records` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `company_id` BIGINT UNSIGNED NOT NULL,
    `created_by_user_id` BIGINT UNSIGNED DEFAULT NULL,
    `guest_name` VARCHAR(160) NOT NULL,
    `cnp` VARCHAR(13) DEFAULT NULL,
    `phone` VARCHAR(32) DEFAULT NULL,
    `email` VARCHAR(190) DEFAULT NULL,
    `floor_label` VARCHAR(32) DEFAULT NULL,
    `accommodation_type` VARCHAR(32) DEFAULT NULL,
    `room_label` VARCHAR(64) DEFAULT NULL,
    `services` TEXT DEFAULT NULL,
    `relaxation_services` TEXT DEFAULT NULL,
    `check_in_date` DATE DEFAULT NULL,
    `check_out_date` DATE DEFAULT NULL,
    `room_price` DECIMAL(10,2) DEFAULT NULL,
    `notes` TEXT DEFAULT NULL,
    `status` ENUM('active','checked_out','cancelled') NOT NULL DEFAULT 'active',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_regim_records_company` (`company_id`),
    KEY `idx_regim_records_status` (`status`),
    KEY `idx_regim_records_guest` (`guest_name`),
    CONSTRAINT `fk_regim_records_company`
        FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT `fk_regim_records_created_by_user`
        FOREIGN KEY (`created_by_user_id`) REFERENCES `users` (`id`)
        ON UPDATE CASCADE
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `regim_room_slots` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `company_id` BIGINT UNSIGNED NOT NULL,
    `floor_label` VARCHAR(32) NOT NULL,
    `accommodation_type` VARCHAR(32) NOT NULL,
    `room_label` VARCHAR(64) NOT NULL,
    `room_price` DECIMAL(10,2) NOT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_regim_room_slots_company` (`company_id`),
    KEY `idx_regim_room_slots_room` (`floor_label`, `room_label`),
    CONSTRAINT `fk_regim_room_slots_company`
        FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `whatsapp_reminder_logs` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `company_id` BIGINT UNSIGNED NOT NULL,
    `reminder_type` ENUM('appointment','itp_expiry','rca_expiry','subscription_expiry') NOT NULL,
    `source_id` BIGINT UNSIGNED NOT NULL,
    `delivery_status` ENUM('queued','sent','failed') NOT NULL DEFAULT 'queued',
    `error_message` VARCHAR(255) DEFAULT NULL,
    `attempted_at` DATETIME DEFAULT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_whatsapp_reminder_logs_company` (`company_id`),
    KEY `idx_whatsapp_reminder_logs_source` (`reminder_type`, `source_id`),
    KEY `idx_whatsapp_reminder_logs_status` (`delivery_status`),
    KEY `idx_whatsapp_reminder_logs_attempted` (`attempted_at`),
    CONSTRAINT `fk_whatsapp_reminder_logs_company`
        FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `whatsapp_settings` (
    `company_id` BIGINT UNSIGNED NOT NULL,
    `is_enabled` TINYINT(1) NOT NULL DEFAULT 0,
    `provider` ENUM('nexo_server','custom_webhook','ultramsg','meta_cloud','twilio') NOT NULL DEFAULT 'custom_webhook',
    `api_base_url` VARCHAR(255) DEFAULT NULL,
    `api_token` VARCHAR(255) DEFAULT NULL,
    `instance_id` VARCHAR(120) DEFAULT NULL,
    `sender_phone` VARCHAR(32) DEFAULT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`company_id`),
    CONSTRAINT `fk_whatsapp_settings_company`
        FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `whatsapp_jobs` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `company_id` BIGINT UNSIGNED NOT NULL,
    `reminder_type` ENUM('appointment','itp_expiry','rca_expiry','subscription_expiry') NOT NULL,
    `source_id` BIGINT UNSIGNED NOT NULL,
    `scheduled_for_date` DATE DEFAULT NULL,
    `to_phone` VARCHAR(32) NOT NULL,
    `message_text` TEXT NOT NULL,
    `status` ENUM('pending','processing','sent','failed','cancelled') NOT NULL DEFAULT 'pending',
    `attempts` SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    `last_error` VARCHAR(255) DEFAULT NULL,
    `provider_message_id` VARCHAR(190) DEFAULT NULL,
    `scheduled_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `locked_by` VARCHAR(64) DEFAULT NULL,
    `lock_expires_at` DATETIME DEFAULT NULL,
    `locked_at` DATETIME DEFAULT NULL,
    `processed_at` DATETIME DEFAULT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_whatsapp_jobs_source_date` (`company_id`, `reminder_type`, `source_id`, `scheduled_for_date`),
    KEY `idx_whatsapp_jobs_company` (`company_id`),
    KEY `idx_whatsapp_jobs_status` (`status`),
    KEY `idx_whatsapp_jobs_scheduled` (`scheduled_at`),
    KEY `idx_whatsapp_jobs_lock` (`lock_expires_at`),
    CONSTRAINT `fk_whatsapp_jobs_company`
        FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `platform_notifications` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `company_id` BIGINT UNSIGNED DEFAULT NULL,
    `created_by_user_id` BIGINT UNSIGNED DEFAULT NULL,
    `audience_type` ENUM('all_companies','single_company') NOT NULL DEFAULT 'all_companies',
    `title` VARCHAR(190) NOT NULL,
    `message_body` TEXT NOT NULL,
    `status` ENUM('active','archived') NOT NULL DEFAULT 'active',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_platform_notifications_company` (`company_id`),
    KEY `idx_platform_notifications_created_by` (`created_by_user_id`),
    KEY `idx_platform_notifications_audience` (`audience_type`),
    KEY `idx_platform_notifications_status` (`status`),
    CONSTRAINT `fk_platform_notifications_company`
        FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`)
        ON UPDATE CASCADE
        ON DELETE SET NULL,
    CONSTRAINT `fk_platform_notifications_created_by_user`
        FOREIGN KEY (`created_by_user_id`) REFERENCES `users` (`id`)
        ON UPDATE CASCADE
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `platform_notification_reads` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `notification_id` BIGINT UNSIGNED NOT NULL,
    `user_id` BIGINT UNSIGNED NOT NULL,
    `read_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_platform_notification_reads_notification_user` (`notification_id`, `user_id`),
    KEY `idx_platform_notification_reads_user` (`user_id`),
    CONSTRAINT `fk_platform_notification_reads_notification`
        FOREIGN KEY (`notification_id`) REFERENCES `platform_notifications` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT `fk_platform_notification_reads_user`
        FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `support_threads` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `company_id` BIGINT UNSIGNED NOT NULL,
    `created_by_user_id` BIGINT UNSIGNED DEFAULT NULL,
    `assigned_admin_user_id` BIGINT UNSIGNED DEFAULT NULL,
    `subject` VARCHAR(190) NOT NULL,
    `status` ENUM('open','in_progress','resolved') NOT NULL DEFAULT 'open',
    `last_message_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_support_threads_company` (`company_id`),
    KEY `idx_support_threads_created_by` (`created_by_user_id`),
    KEY `idx_support_threads_admin` (`assigned_admin_user_id`),
    KEY `idx_support_threads_status` (`status`),
    KEY `idx_support_threads_last_message` (`last_message_at`),
    CONSTRAINT `fk_support_threads_company`
        FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT `fk_support_threads_created_by_user`
        FOREIGN KEY (`created_by_user_id`) REFERENCES `users` (`id`)
        ON UPDATE CASCADE
        ON DELETE SET NULL,
    CONSTRAINT `fk_support_threads_admin_user`
        FOREIGN KEY (`assigned_admin_user_id`) REFERENCES `users` (`id`)
        ON UPDATE CASCADE
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `support_messages` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `thread_id` BIGINT UNSIGNED NOT NULL,
    `company_id` BIGINT UNSIGNED NOT NULL,
    `sender_user_id` BIGINT UNSIGNED DEFAULT NULL,
    `sender_role` ENUM('admin','client','trial') NOT NULL DEFAULT 'client',
    `message_body` TEXT NOT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_support_messages_thread` (`thread_id`),
    KEY `idx_support_messages_company` (`company_id`),
    KEY `idx_support_messages_sender` (`sender_user_id`),
    CONSTRAINT `fk_support_messages_thread`
        FOREIGN KEY (`thread_id`) REFERENCES `support_threads` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT `fk_support_messages_company`
        FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT `fk_support_messages_sender_user`
        FOREIGN KEY (`sender_user_id`) REFERENCES `users` (`id`)
        ON UPDATE CASCADE
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `support_attachments` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `thread_id` BIGINT UNSIGNED NOT NULL,
    `message_id` BIGINT UNSIGNED NOT NULL,
    `company_id` BIGINT UNSIGNED NOT NULL,
    `uploaded_by_user_id` BIGINT UNSIGNED DEFAULT NULL,
    `storage_name` VARCHAR(190) NOT NULL,
    `original_name` VARCHAR(190) NOT NULL,
    `mime_type` VARCHAR(120) NOT NULL,
    `file_size` INT UNSIGNED NOT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_support_attachments_thread` (`thread_id`),
    KEY `idx_support_attachments_message` (`message_id`),
    KEY `idx_support_attachments_company` (`company_id`),
    KEY `idx_support_attachments_user` (`uploaded_by_user_id`),
    CONSTRAINT `fk_support_attachments_thread`
        FOREIGN KEY (`thread_id`) REFERENCES `support_threads` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT `fk_support_attachments_message`
        FOREIGN KEY (`message_id`) REFERENCES `support_messages` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT `fk_support_attachments_company`
        FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT `fk_support_attachments_uploaded_by_user`
        FOREIGN KEY (`uploaded_by_user_id`) REFERENCES `users` (`id`)
        ON UPDATE CASCADE
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `support_thread_reads` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `thread_id` BIGINT UNSIGNED NOT NULL,
    `user_id` BIGINT UNSIGNED NOT NULL,
    `last_read_message_id` BIGINT UNSIGNED NOT NULL DEFAULT 0,
    `read_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_support_thread_reads_thread_user` (`thread_id`, `user_id`),
    KEY `idx_support_thread_reads_user` (`user_id`),
    CONSTRAINT `fk_support_thread_reads_thread`
        FOREIGN KEY (`thread_id`) REFERENCES `support_threads` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT `fk_support_thread_reads_user`
        FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `company_profile_change_requests` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `company_id` BIGINT UNSIGNED NOT NULL,
    `thread_id` BIGINT UNSIGNED NOT NULL,
    `requested_by_user_id` BIGINT UNSIGNED NOT NULL,
    `reviewed_by_user_id` BIGINT UNSIGNED DEFAULT NULL,
    `current_company_name` VARCHAR(180) NOT NULL,
    `current_founder_name` VARCHAR(160) NOT NULL,
    `current_contact_phone` VARCHAR(32) DEFAULT NULL,
    `requested_company_name` VARCHAR(180) NOT NULL,
    `requested_founder_name` VARCHAR(160) NOT NULL,
    `requested_contact_phone` VARCHAR(32) DEFAULT NULL,
    `status` ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
    `review_notes` TEXT DEFAULT NULL,
    `reviewed_at` DATETIME DEFAULT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_company_profile_change_requests_thread` (`thread_id`),
    KEY `idx_company_profile_change_requests_company` (`company_id`),
    KEY `idx_company_profile_change_requests_requested_by` (`requested_by_user_id`),
    KEY `idx_company_profile_change_requests_reviewed_by` (`reviewed_by_user_id`),
    KEY `idx_company_profile_change_requests_status` (`status`),
    CONSTRAINT `fk_company_profile_change_requests_company`
        FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT `fk_company_profile_change_requests_thread`
        FOREIGN KEY (`thread_id`) REFERENCES `support_threads` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT `fk_company_profile_change_requests_requested_by_user`
        FOREIGN KEY (`requested_by_user_id`) REFERENCES `users` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT `fk_company_profile_change_requests_reviewed_by_user`
        FOREIGN KEY (`reviewed_by_user_id`) REFERENCES `users` (`id`)
        ON UPDATE CASCADE
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `promo_code_requests` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `company_id` BIGINT UNSIGNED NOT NULL,
    `created_by_user_id` BIGINT UNSIGNED NOT NULL,
    `promo_code` VARCHAR(80) NOT NULL,
    `notes` TEXT DEFAULT NULL,
    `status` ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
    `admin_notes` TEXT DEFAULT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_promo_code_requests_company` (`company_id`),
    KEY `idx_promo_code_requests_user` (`created_by_user_id`),
    KEY `idx_promo_code_requests_status` (`status`),
    KEY `idx_promo_code_requests_created` (`created_at`),
    CONSTRAINT `fk_promo_code_requests_company`
        FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT `fk_promo_code_requests_user`
        FOREIGN KEY (`created_by_user_id`) REFERENCES `users` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `company_reviews` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `company_id` BIGINT UNSIGNED NOT NULL,
    `rating` TINYINT UNSIGNED NOT NULL,
    `reviewer_name` VARCHAR(160) DEFAULT NULL,
    `review_message` TEXT DEFAULT NULL,
    `client_phone` VARCHAR(32) DEFAULT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_company_reviews_company` (`company_id`),
    KEY `idx_company_reviews_rating` (`rating`),
    KEY `idx_company_reviews_created` (`created_at`),
    CONSTRAINT `fk_company_reviews_company`
        FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `company_reminder_templates` (
    `company_id` BIGINT UNSIGNED NOT NULL,
    `category_name` VARCHAR(120) NOT NULL,
    `reminder_type` ENUM('appointment','itp_expiry','rca_expiry','subscription_expiry') NOT NULL,
    `message_template` TEXT NOT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`company_id`, `reminder_type`),
    KEY `idx_company_reminder_templates_category` (`category_name`),
    CONSTRAINT `fk_company_reminder_templates_company`
        FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;

INSERT INTO `roles` (`code`, `name`, `description`) VALUES
    ('admin', 'Administrator', 'Administratorul platformei NexoIT'),
    ('trial', 'Trial', 'Utilizator inregistrat in perioada gratuita de 7 zile'),
    ('client', 'Client', 'Utilizator cu abonament activ in platforma');

INSERT INTO `company_categories` (`slug`, `name`, `is_active`) VALUES
    ('statie-itp', 'Statie ITP', 1),
    ('service-auto', 'Service Auto', 1),
    ('medical', 'Medical', 1),
    ('regim-hotelier', 'Regim hotelier', 1),
    ('beauty', 'Beauty', 1),
    ('fitness-sport', 'Fitness & Sport', 1),
    ('logistica', 'Logistica', 1),
    ('transport', 'Transport', 1),
    ('saloane-barber', 'Saloane & Barber', 1),
    ('restaurante-cafenele', 'Restaurante & Cafenele', 1);

INSERT INTO `subscription_plans` (`plan_key`, `name`, `duration_months`, `price_eur`, `is_active`) VALUES
    ('lunar', 'Lunar', 1, 149.00, 1),
    ('3-luni', '3 luni', 3, 399.00, 1),
    ('6-luni', '6 luni', 6, 749.00, 1),
    ('1-an', '1 an', 12, 1299.00, 1);

INSERT INTO `custom_services` (`service_key`, `service_name`, `service_type`, `starting_price_eur`, `is_active`) VALUES
    ('general', 'Oferta generala', 'custom_offer_general', NULL, 1),
    ('web-design-marketing', 'Web Design si Marketing', 'custom_offer', 349.00, 1),
    ('software-mobile-api', 'Software Mobile, API si SaaS', 'custom_offer', 799.00, 1),
    ('dezvoltare-software', 'Dezvoltare Software', 'custom_offer', 999.00, 1),
    ('api-integrari', 'API, Integrari si SaaS', 'custom_offer', 549.00, 1),
    ('saas', 'Platforme SaaS', 'custom_offer', NULL, 1);

INSERT INTO `service_modules` (`module_key`, `module_name`, `module_group`, `description`, `is_active`) VALUES
    ('online-bookings', 'Programari Online', 'subscription', 'Gestionare programari pe categorii de companii', 1),
    ('whatsapp-automation', 'Automatizari WhatsApp', 'subscription', 'Notificari si remindere automate prin WhatsApp', 1),
    ('crm-management', 'CRM Management Clienti', 'subscription', 'Istoric clienti, interactiuni si evidenta operatiunilor', 1),
    ('web-design-marketing', 'Web Design si Marketing', 'custom_offer', 'Website-uri premium si campanii de marketing', 1),
    ('mobile-apps', 'Aplicatii Mobile', 'custom_offer', 'Aplicatii mobile pentru clienti si administratori', 1),
    ('custom-software', 'Dezvoltare Software', 'custom_offer', 'Sisteme software personalizate pentru afaceri', 1),
    ('api-integrations', 'API si Integrari', 'custom_offer', 'Conectare intre platforme si servicii externe', 1),
    ('saas-platforms', 'Servicii SaaS', 'custom_offer', 'Platforme SaaS scalabile pentru vanzare sau operare', 1);

INSERT INTO `companies` (
    `category_id`,
    `company_name`,
    `founder_name`,
    `contact_email`,
    `contact_phone`,
    `transport_type`,
    `account_type`,
    `status`,
    `trial_start_at`,
    `trial_end_at`
) VALUES (
    NULL,
    'Antotoni Vision SRL',
    'Administrator NexoIT',
    'sin.marian955@gmail.com',
    NULL,
    NULL,
    'client',
    'active',
    NULL,
    NULL
);

INSERT INTO `users` (
    `company_id`,
    `role_id`,
    `full_name`,
    `email`,
    `phone`,
    `password_hash`,
    `email_verified_at`,
    `account_status`
) VALUES (
    (SELECT `id` FROM `companies` WHERE `contact_email` = 'sin.marian955@gmail.com' LIMIT 1),
    (SELECT `id` FROM `roles` WHERE `code` = 'admin' LIMIT 1),
    'Administrator NexoIT',
    'sin.marian955@gmail.com',
    NULL,
    '$2y$10$mNuxklWOjEW2GVezLFsJdeWjRUnKz6WOvKa9RDDfwtzBBYaKg9v5C',
    NOW(),
    'active'
);

DELIMITER $$

CREATE TRIGGER `trg_trial_periods_after_insert`
AFTER INSERT ON `trial_periods`
FOR EACH ROW
BEGIN
    DECLARE v_trial_role_id TINYINT UNSIGNED;

    SELECT `id`
    INTO v_trial_role_id
    FROM `roles`
    WHERE `code` = 'trial'
    LIMIT 1;

    UPDATE `companies`
    SET
        `account_type` = 'trial',
        `status` = 'trial',
        `trial_start_at` = NEW.`started_at`,
        `trial_end_at` = NEW.`expires_at`
    WHERE `id` = NEW.`company_id`;

    UPDATE `users`
    SET
        `role_id` = v_trial_role_id,
        `account_status` = 'active',
        `trial_starts_at` = NEW.`started_at`,
        `trial_expires_at` = NEW.`expires_at`
    WHERE `id` = NEW.`user_id`;
END$$

CREATE TRIGGER `trg_subscriptions_after_insert`
AFTER INSERT ON `subscriptions`
FOR EACH ROW
BEGIN
    DECLARE v_client_role_id TINYINT UNSIGNED;

    IF NEW.`status` = 'active' THEN
        SELECT `id`
        INTO v_client_role_id
        FROM `roles`
        WHERE `code` = 'client'
        LIMIT 1;

        UPDATE `companies`
        SET
            `account_type` = 'client',
            `status` = 'active'
        WHERE `id` = NEW.`company_id`;

        UPDATE `users`
        SET
            `role_id` = v_client_role_id,
            `account_status` = 'active'
        WHERE `company_id` = NEW.`company_id`;

        UPDATE `trial_periods`
        SET `status` = 'converted'
        WHERE `company_id` = NEW.`company_id`
          AND `status` = 'active';
    END IF;
END$$

CREATE TRIGGER `trg_subscriptions_after_update`
AFTER UPDATE ON `subscriptions`
FOR EACH ROW
BEGIN
    DECLARE v_client_role_id TINYINT UNSIGNED;

    IF NEW.`status` = 'active' AND OLD.`status` <> 'active' THEN
        SELECT `id`
        INTO v_client_role_id
        FROM `roles`
        WHERE `code` = 'client'
        LIMIT 1;

        UPDATE `companies`
        SET
            `account_type` = 'client',
            `status` = 'active'
        WHERE `id` = NEW.`company_id`;

        UPDATE `users`
        SET
            `role_id` = v_client_role_id,
            `account_status` = 'active'
        WHERE `company_id` = NEW.`company_id`;

        UPDATE `trial_periods`
        SET `status` = 'converted'
        WHERE `company_id` = NEW.`company_id`
          AND `status` = 'active';
    END IF;

    IF NEW.`status` IN ('expired', 'cancelled') THEN
        UPDATE `companies`
        SET `status` = 'expired'
        WHERE `id` = NEW.`company_id`
          AND NOT EXISTS (
              SELECT 1
              FROM `subscriptions` s
              WHERE s.`company_id` = NEW.`company_id`
                AND s.`status` = 'active'
          );
    END IF;
END$$

CREATE EVENT `ev_mark_expired_trials`
ON SCHEDULE EVERY 1 HOUR
DO
BEGIN
    UPDATE `trial_periods`
    SET `status` = 'expired'
    WHERE `status` = 'active'
      AND `expires_at` < NOW();

    UPDATE `companies`
    SET `status` = 'expired'
    WHERE `account_type` = 'trial'
      AND `trial_end_at` IS NOT NULL
      AND `trial_end_at` < NOW()
      AND NOT EXISTS (
          SELECT 1
          FROM `subscriptions` s
          WHERE s.`company_id` = `companies`.`id`
            AND s.`status` = 'active'
      );

    UPDATE `users` u
    INNER JOIN `roles` r ON r.`id` = u.`role_id`
    SET u.`account_status` = 'expired'
    WHERE r.`code` = 'trial'
      AND u.`trial_expires_at` IS NOT NULL
      AND u.`trial_expires_at` < NOW()
      AND NOT EXISTS (
          SELECT 1
          FROM `subscriptions` s
          WHERE s.`company_id` = u.`company_id`
            AND s.`status` = 'active'
      );
END$$

DELIMITER ;

-- Pentru rularea event-ului de expirare trial:
-- SET GLOBAL event_scheduler = ON;