\
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS email_logs;
DROP TABLE IF EXISTS email_job_attachments;
DROP TABLE IF EXISTS email_jobs;
DROP TABLE IF EXISTS email_template_attachments;
DROP TABLE IF EXISTS email_templates;
DROP TABLE IF EXISTS reminders;
DROP TABLE IF EXISTS tasks;
DROP TABLE IF EXISTS interactions;
DROP TABLE IF EXISTS client_tag_map;
DROP TABLE IF EXISTS client_tags;
DROP TABLE IF EXISTS client_notes;
DROP TABLE IF EXISTS vehicle_alerts;
DROP TABLE IF EXISTS vehicles;
DROP TABLE IF EXISTS client_interests;
DROP TABLE IF EXISTS client_type_map;
DROP TABLE IF EXISTS contact_book;
DROP TABLE IF EXISTS clients;
DROP TABLE IF EXISTS user_mail_settings;
DROP TABLE IF EXISTS password_resets;
DROP TABLE IF EXISTS activity_logs;
DROP TABLE IF EXISTS app_settings;
DROP TABLE IF EXISTS users;

CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(120) NOT NULL,
    email VARCHAR(190) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('admin','user') NOT NULL DEFAULT 'user',
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    last_login_at DATETIME NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    UNIQUE KEY uq_users_email (email),
    KEY idx_users_role (role),
    KEY idx_users_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE user_mail_settings (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    smtp_host VARCHAR(190) NOT NULL,
    smtp_port VARCHAR(10) NOT NULL,
    smtp_encryption ENUM('ssl','tls','none') NOT NULL DEFAULT 'ssl',
    smtp_username VARCHAR(190) NOT NULL,
    smtp_password VARCHAR(255) NOT NULL,
    from_email VARCHAR(190) NOT NULL,
    from_name VARCHAR(190) NOT NULL,
    reply_to VARCHAR(190) NULL,
    default_cc TEXT NULL,
    default_bcc TEXT NULL,
    email_signature_html MEDIUMTEXT NULL,
    email_signature_text MEDIUMTEXT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    UNIQUE KEY uq_user_mail_user (user_id),
    CONSTRAINT fk_user_mail_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE password_resets (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    email VARCHAR(190) NOT NULL,
    token_hash CHAR(64) NOT NULL,
    expires_at DATETIME NOT NULL,
    used_at DATETIME NULL,
    created_at DATETIME NOT NULL,
    UNIQUE KEY uq_password_reset_token (token_hash),
    KEY idx_password_reset_email (email),
    KEY idx_password_reset_exp (expires_at),
    CONSTRAINT fk_password_reset_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE app_settings (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) NOT NULL,
    setting_value TEXT NULL,
    setting_group VARCHAR(100) NOT NULL DEFAULT 'general',
    updated_by INT UNSIGNED NULL,
    updated_at DATETIME NOT NULL,
    UNIQUE KEY uq_app_settings_key (setting_key),
    KEY idx_app_settings_group (setting_group),
    CONSTRAINT fk_app_settings_user FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE clients (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    owner_id INT UNSIGNED NOT NULL,
    primary_client_type ENUM('cumparator','colaborator_isf','colaborator') NOT NULL DEFAULT 'cumparator',
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    phone VARCHAR(50) NULL,
    email VARCHAR(190) NULL,
    city VARCHAR(120) NULL,
    county VARCHAR(120) NULL,
    client_status ENUM('necontactat','interesat','ofertat','de_recontactat','activ','inactiv') NOT NULL DEFAULT 'necontactat',
    general_notes MEDIUMTEXT NULL,
    canonical_phone VARCHAR(50) NULL,
    canonical_email VARCHAR(190) NULL,
    created_by INT UNSIGNED NULL,
    updated_by INT UNSIGNED NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    KEY idx_clients_owner (owner_id),
    KEY idx_clients_status (client_status),
    KEY idx_clients_type (primary_client_type),
    KEY idx_clients_active (is_active),
    KEY idx_clients_cphone (canonical_phone),
    KEY idx_clients_cemail (canonical_email),
    CONSTRAINT fk_clients_owner FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_clients_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    CONSTRAINT fk_clients_updated_by FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE client_type_map (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    client_id INT UNSIGNED NOT NULL,
    client_type ENUM('cumparator','colaborator_isf','colaborator') NOT NULL,
    owner_id INT UNSIGNED NOT NULL,
    created_at DATETIME NOT NULL,
    UNIQUE KEY uq_client_type_map (client_id, client_type),
    KEY idx_client_type_owner (owner_id),
    CONSTRAINT fk_client_type_map_client FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
    CONSTRAINT fk_client_type_map_owner FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE client_interests (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    client_id INT UNSIGNED NOT NULL,
    owner_id INT UNSIGNED NOT NULL,
    interest_type ENUM('locuinta','viata','sanatate','calatorie','credit','imobiliare','altceva') NOT NULL,
    title VARCHAR(150) NOT NULL,
    description MEDIUMTEXT NULL,
    expiration_date DATE NULL,
    notes MEDIUMTEXT NULL,
    status ENUM('active','inactive','in_progress','closed') NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    KEY idx_client_interests_client (client_id),
    KEY idx_client_interests_owner (owner_id),
    KEY idx_client_interests_type (interest_type),
    KEY idx_client_interests_exp (expiration_date),
    KEY idx_client_interests_status (status),
    CONSTRAINT fk_client_interests_client FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
    CONSTRAINT fk_client_interests_owner FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE vehicles (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    client_id INT UNSIGNED NOT NULL,
    owner_id INT UNSIGNED NOT NULL,
    brand VARCHAR(100) NOT NULL,
    model VARCHAR(100) NOT NULL,
    plate_number VARCHAR(50) NOT NULL,
    manufacture_year SMALLINT NULL,
    vin VARCHAR(100) NULL,
    notes MEDIUMTEXT NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    KEY idx_vehicles_client (client_id),
    KEY idx_vehicles_owner (owner_id),
    KEY idx_vehicles_plate (plate_number),
    KEY idx_vehicles_vin (vin),
    CONSTRAINT fk_vehicles_client FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
    CONSTRAINT fk_vehicles_owner FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE vehicle_alerts (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    vehicle_id INT UNSIGNED NOT NULL,
    owner_id INT UNSIGNED NOT NULL,
    alert_type ENUM('rca','rovinieta','itp','asistenta_rutiera','casco','revizie','extinctor','trusa_medicala','custom') NOT NULL,
    custom_label VARCHAR(150) NULL,
    expiration_date DATE NOT NULL,
    notes MEDIUMTEXT NULL,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    KEY idx_vehicle_alerts_vehicle (vehicle_id),
    KEY idx_vehicle_alerts_owner (owner_id),
    KEY idx_vehicle_alerts_type (alert_type),
    KEY idx_vehicle_alerts_exp (expiration_date),
    KEY idx_vehicle_alerts_status (status),
    CONSTRAINT fk_vehicle_alerts_vehicle FOREIGN KEY (vehicle_id) REFERENCES vehicles(id) ON DELETE CASCADE,
    CONSTRAINT fk_vehicle_alerts_owner FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE reminders (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    relation_type ENUM('vehicle_alert','client_interest') NOT NULL,
    relation_id INT UNSIGNED NOT NULL,
    owner_id INT UNSIGNED NOT NULL,
    reminder_key ENUM('reminder_60','reminder_30','reminder_15','reminder_7','reminder_1','reminder_0','expired_notice') NOT NULL,
    threshold_days SMALLINT NOT NULL,
    scheduled_for DATETIME NOT NULL,
    recipient_scope ENUM('client','owner') NOT NULL,
    status ENUM('scheduled','processed','cancelled') NOT NULL DEFAULT 'scheduled',
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    KEY idx_reminders_owner_sched (owner_id, scheduled_for, status),
    KEY idx_reminders_relation (relation_type, relation_id),
    CONSTRAINT fk_reminders_owner FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE email_templates (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    owner_id INT UNSIGNED NULL,
    name VARCHAR(150) NOT NULL,
    template_key VARCHAR(100) NOT NULL,
    subject VARCHAR(255) NOT NULL,
    body_html MEDIUMTEXT NOT NULL,
    body_text MEDIUMTEXT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    is_system TINYINT(1) NOT NULL DEFAULT 0,
    created_by INT UNSIGNED NULL,
    updated_by INT UNSIGNED NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    KEY idx_email_templates_owner (owner_id),
    KEY idx_email_templates_key (template_key),
    KEY idx_email_templates_active (is_active),
    CONSTRAINT fk_email_templates_owner FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE SET NULL,
    CONSTRAINT fk_email_templates_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    CONSTRAINT fk_email_templates_updated_by FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE email_template_attachments (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    template_id INT UNSIGNED NOT NULL,
    file_name VARCHAR(255) NOT NULL,
    stored_name VARCHAR(255) NOT NULL,
    file_path VARCHAR(500) NOT NULL,
    mime_type VARCHAR(190) NULL,
    file_size BIGINT UNSIGNED NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL,
    KEY idx_eta_template (template_id),
    CONSTRAINT fk_eta_template FOREIGN KEY (template_id) REFERENCES email_templates(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE email_jobs (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    owner_id INT UNSIGNED NOT NULL,
    reminder_id INT UNSIGNED NULL,
    template_id INT UNSIGNED NULL,
    job_type ENUM('reminder_60','reminder_30','reminder_15','reminder_7','reminder_1','reminder_0','expired_notice','manual') NOT NULL,
    relation_type VARCHAR(50) NOT NULL,
    relation_id INT UNSIGNED NULL,
    client_id INT UNSIGNED NULL,
    vehicle_id INT UNSIGNED NULL,
    alert_id INT UNSIGNED NULL,
    recipient_email VARCHAR(190) NOT NULL,
    recipient_name VARCHAR(190) NULL,
    cc TEXT NULL,
    bcc TEXT NULL,
    subject_rendered VARCHAR(255) NOT NULL,
    body_html_rendered MEDIUMTEXT NOT NULL,
    body_text_rendered MEDIUMTEXT NULL,
    trigger_label VARCHAR(150) NULL,
    plate_number_snapshot VARCHAR(50) NULL,
    scheduled_for DATETIME NOT NULL,
    status ENUM('queued','ready','sent','failed','cancelled') NOT NULL DEFAULT 'queued',
    retry_count INT UNSIGNED NOT NULL DEFAULT 0,
    last_retry_at DATETIME NULL,
    error_message TEXT NULL,
    sent_at DATETIME NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    KEY idx_email_jobs_status_sched (status, scheduled_for),
    KEY idx_email_jobs_owner_status (owner_id, status),
    KEY idx_email_jobs_client (client_id),
    KEY idx_email_jobs_vehicle (vehicle_id),
    KEY idx_email_jobs_alert (alert_id),
    KEY idx_email_jobs_type (job_type),
    CONSTRAINT fk_email_jobs_owner FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_email_jobs_reminder FOREIGN KEY (reminder_id) REFERENCES reminders(id) ON DELETE SET NULL,
    CONSTRAINT fk_email_jobs_template FOREIGN KEY (template_id) REFERENCES email_templates(id) ON DELETE SET NULL,
    CONSTRAINT fk_email_jobs_client FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE SET NULL,
    CONSTRAINT fk_email_jobs_vehicle FOREIGN KEY (vehicle_id) REFERENCES vehicles(id) ON DELETE SET NULL,
    CONSTRAINT fk_email_jobs_alert FOREIGN KEY (alert_id) REFERENCES vehicle_alerts(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE email_job_attachments (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email_job_id INT UNSIGNED NOT NULL,
    source_type ENUM('template','manual_upload') NOT NULL DEFAULT 'template',
    file_name VARCHAR(255) NOT NULL,
    stored_name VARCHAR(255) NOT NULL,
    file_path VARCHAR(500) NOT NULL,
    mime_type VARCHAR(190) NULL,
    file_size BIGINT UNSIGNED NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL,
    KEY idx_email_job_attach_job (email_job_id),
    CONSTRAINT fk_email_job_attach_job FOREIGN KEY (email_job_id) REFERENCES email_jobs(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE email_logs (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email_job_id INT UNSIGNED NULL,
    recipient_email VARCHAR(190) NOT NULL,
    recipient_name VARCHAR(190) NULL,
    cc TEXT NULL,
    bcc TEXT NULL,
    subject VARCHAR(255) NOT NULL,
    body_html MEDIUMTEXT NOT NULL,
    template_id INT UNSIGNED NULL,
    relation_type VARCHAR(50) NULL,
    relation_id INT UNSIGNED NULL,
    status ENUM('sent','failed','cancelled','test_sent') NOT NULL,
    error_message TEXT NULL,
    sent_at DATETIME NULL,
    retry_count INT UNSIGNED NOT NULL DEFAULT 0,
    last_retry_at DATETIME NULL,
    owner_id INT UNSIGNED NOT NULL,
    created_at DATETIME NOT NULL,
    KEY idx_email_logs_owner (owner_id),
    KEY idx_email_logs_status (status),
    KEY idx_email_logs_sent (sent_at),
    CONSTRAINT fk_email_logs_job FOREIGN KEY (email_job_id) REFERENCES email_jobs(id) ON DELETE SET NULL,
    CONSTRAINT fk_email_logs_template FOREIGN KEY (template_id) REFERENCES email_templates(id) ON DELETE SET NULL,
    CONSTRAINT fk_email_logs_owner FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE contact_book (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    owner_id INT UNSIGNED NOT NULL,
    first_name VARCHAR(100) NULL,
    last_name VARCHAR(100) NULL,
    phone VARCHAR(50) NULL,
    email VARCHAR(190) NULL,
    canonical_phone VARCHAR(50) NULL,
    canonical_email VARCHAR(190) NULL,
    source_type ENUM('google_csv','manual','api') NOT NULL DEFAULT 'manual',
    prospect_status ENUM('client','colaborator','viitor_client','neinteresat','none') NOT NULL DEFAULT 'none',
    last_contact_at DATETIME NULL,
    notes MEDIUMTEXT NULL,
    import_batch VARCHAR(100) NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    KEY idx_contact_book_owner (owner_id),
    KEY idx_contact_book_status (prospect_status),
    KEY idx_contact_book_source (source_type),
    KEY idx_contact_book_cphone (canonical_phone),
    KEY idx_contact_book_cemail (canonical_email),
    CONSTRAINT fk_contact_book_owner FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE interactions (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    relation_type ENUM('client','prospect') NOT NULL,
    relation_id INT UNSIGNED NOT NULL,
    interaction_type ENUM('apel','email','whatsapp','intalnire','oferta','follow_up','nota') NOT NULL,
    subject VARCHAR(150) NOT NULL,
    content MEDIUMTEXT NULL,
    interaction_at DATETIME NOT NULL,
    owner_id INT UNSIGNED NOT NULL,
    created_at DATETIME NOT NULL,
    KEY idx_interactions_relation (relation_type, relation_id),
    KEY idx_interactions_owner (owner_id),
    KEY idx_interactions_date (interaction_at),
    CONSTRAINT fk_interactions_owner FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE tasks (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    related_type ENUM('client','prospect','vehicle','interest','general') NOT NULL DEFAULT 'general',
    related_id INT UNSIGNED NULL,
    title VARCHAR(150) NOT NULL,
    description MEDIUMTEXT NULL,
    due_date DATE NULL,
    priority ENUM('low','medium','high','urgent') NOT NULL DEFAULT 'medium',
    status ENUM('open','done','cancelled') NOT NULL DEFAULT 'open',
    owner_id INT UNSIGNED NOT NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    KEY idx_tasks_owner (owner_id),
    KEY idx_tasks_related (related_type, related_id),
    KEY idx_tasks_status_due (status, due_date),
    CONSTRAINT fk_tasks_owner FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE client_notes (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    client_id INT UNSIGNED NOT NULL,
    note_text MEDIUMTEXT NOT NULL,
    owner_id INT UNSIGNED NOT NULL,
    created_by INT UNSIGNED NULL,
    updated_at DATETIME NOT NULL,
    created_at DATETIME NOT NULL,
    KEY idx_client_notes_client (client_id),
    KEY idx_client_notes_owner (owner_id),
    CONSTRAINT fk_client_notes_client FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
    CONSTRAINT fk_client_notes_owner FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_client_notes_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE client_tags (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    owner_id INT UNSIGNED NOT NULL,
    name VARCHAR(100) NOT NULL,
    color_hex VARCHAR(10) NOT NULL DEFAULT '#3B82F6',
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    UNIQUE KEY uq_client_tags_owner_name (owner_id, name),
    KEY idx_client_tags_owner (owner_id),
    CONSTRAINT fk_client_tags_owner FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE client_tag_map (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    client_id INT UNSIGNED NOT NULL,
    tag_id INT UNSIGNED NOT NULL,
    owner_id INT UNSIGNED NOT NULL,
    created_at DATETIME NOT NULL,
    UNIQUE KEY uq_client_tag_map (client_id, tag_id),
    KEY idx_client_tag_map_owner (owner_id),
    CONSTRAINT fk_client_tag_map_client FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
    CONSTRAINT fk_client_tag_map_tag FOREIGN KEY (tag_id) REFERENCES client_tags(id) ON DELETE CASCADE,
    CONSTRAINT fk_client_tag_map_owner FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE activity_logs (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    owner_id INT UNSIGNED NULL,
    actor_user_id INT UNSIGNED NULL,
    entity_type VARCHAR(50) NOT NULL,
    entity_id INT UNSIGNED NULL,
    action VARCHAR(100) NOT NULL,
    description TEXT NULL,
    ip_address VARCHAR(45) NULL,
    user_agent VARCHAR(255) NULL,
    created_at DATETIME NOT NULL,
    KEY idx_activity_owner (owner_id),
    KEY idx_activity_actor (actor_user_id),
    KEY idx_activity_created (created_at),
    CONSTRAINT fk_activity_owner FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE SET NULL,
    CONSTRAINT fk_activity_actor FOREIGN KEY (actor_user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;