SET NAMES utf8mb4;
SET time_zone = '+00:00';

CREATE TABLE IF NOT EXISTS admins (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(120) NOT NULL,
    email VARCHAR(190) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('owner','manager') NOT NULL DEFAULT 'owner',
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    failed_attempts SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    locked_until DATETIME NULL,
    last_login_at DATETIME NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS clients (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(120) NOT NULL,
    email VARCHAR(190) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    phone VARCHAR(60) NULL,
    messenger VARCHAR(190) NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    failed_attempts SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    locked_until DATETIME NULL,
    last_login_at DATETIME NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    INDEX idx_clients_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS settings (
    setting_key VARCHAR(120) PRIMARY KEY,
    setting_value LONGTEXT NULL,
    updated_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS categories (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    parent_id INT UNSIGNED NULL,
    name VARCHAR(160) NOT NULL,
    slug VARCHAR(180) NOT NULL UNIQUE,
    short_description VARCHAR(320) NULL,
    description MEDIUMTEXT NULL,
    icon VARCHAR(80) NOT NULL DEFAULT 'industry',
    seo_title VARCHAR(220) NULL,
    seo_description VARCHAR(320) NULL,
    is_featured TINYINT(1) NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    sort_order INT NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    CONSTRAINT fk_categories_parent FOREIGN KEY (parent_id) REFERENCES categories(id) ON DELETE SET NULL,
    INDEX idx_categories_active_sort (is_active, sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS companies (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(190) NOT NULL,
    slug VARCHAR(210) NOT NULL UNIQUE,
    legal_name VARCHAR(255) NULL,
    edrpou VARCHAR(12) NULL,
    city VARCHAR(120) NOT NULL,
    region VARCHAR(120) NULL,
    address VARCHAR(255) NULL,
    service_area ENUM('local','regional','ukraine') NOT NULL DEFAULT 'ukraine',
    description MEDIUMTEXT NULL,
    capabilities MEDIUMTEXT NULL,
    materials MEDIUMTEXT NULL,
    equipment MEDIUMTEXT NULL,
    min_order VARCHAR(190) NULL,
    lead_time VARCHAR(190) NULL,
    delivery_info VARCHAR(255) NULL,
    website VARCHAR(255) NULL,
    public_email VARCHAR(190) NULL,
    public_phone VARCHAR(50) NULL,
    contact_name VARCHAR(120) NULL,
    contact_email VARCHAR(190) NULL,
    contact_phone VARCHAR(50) NULL,
    telegram VARCHAR(120) NULL,
    logo_path VARCHAR(255) NULL,
    cover_path VARCHAR(255) NULL,
    status ENUM('draft','pending','published','blocked') NOT NULL DEFAULT 'pending',
    is_verified TINYINT(1) NOT NULL DEFAULT 0,
    accepts_requests TINYINT(1) NOT NULL DEFAULT 1,
    response_score TINYINT UNSIGNED NOT NULL DEFAULT 0,
    views_count INT UNSIGNED NOT NULL DEFAULT 0,
    last_verified_at DATETIME NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    INDEX idx_companies_status_city (status, city),
    INDEX idx_companies_verified (is_verified),
    INDEX idx_companies_accepts (accepts_requests)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS company_categories (
    company_id INT UNSIGNED NOT NULL,
    category_id INT UNSIGNED NOT NULL,
    PRIMARY KEY (company_id, category_id),
    CONSTRAINT fk_cc_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    CONSTRAINT fk_cc_category FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS company_gallery (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id INT UNSIGNED NOT NULL,
    file_path VARCHAR(255) NOT NULL,
    alt_text VARCHAR(220) NULL,
    sort_order INT NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL,
    CONSTRAINT fk_gallery_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    INDEX idx_gallery_company_sort (company_id, sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS requests (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    public_id CHAR(12) NOT NULL UNIQUE,
    client_id INT UNSIGNED NULL,
    access_code_hash CHAR(64) NULL,
    category_id INT UNSIGNED NULL,
    title VARCHAR(220) NOT NULL,
    description MEDIUMTEXT NOT NULL,
    quantity VARCHAR(120) NULL,
    material VARCHAR(190) NULL,
    city VARCHAR(120) NULL,
    delivery_required TINYINT(1) NOT NULL DEFAULT 1,
    desired_deadline VARCHAR(190) NULL,
    client_name VARCHAR(120) NOT NULL,
    client_phone VARCHAR(60) NULL,
    client_email VARCHAR(190) NULL,
    client_messenger VARCHAR(190) NULL,
    consent TINYINT(1) NOT NULL DEFAULT 0,
    status ENUM('new','review','clarification','matching','sent','waiting','offers','contacts_shared','selected','completed','cancelled','spam') NOT NULL DEFAULT 'new',
    source VARCHAR(120) NULL,
    utm_source VARCHAR(190) NULL,
    utm_campaign VARCHAR(190) NULL,
    internal_notes MEDIUMTEXT NULL,
    client_last_viewed_at DATETIME NULL,
    ip_hash CHAR(64) NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    CONSTRAINT fk_requests_category FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL,
    CONSTRAINT fk_requests_client FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE SET NULL,
    INDEX idx_requests_status_created (status, created_at),
    INDEX idx_requests_category (category_id),
    INDEX idx_requests_client_created (client_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS request_files (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    request_id INT UNSIGNED NOT NULL,
    original_name VARCHAR(255) NOT NULL,
    stored_name VARCHAR(255) NOT NULL,
    mime_type VARCHAR(120) NOT NULL,
    file_size INT UNSIGNED NOT NULL,
    created_at DATETIME NOT NULL,
    CONSTRAINT fk_files_request FOREIGN KEY (request_id) REFERENCES requests(id) ON DELETE CASCADE,
    INDEX idx_files_request (request_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS request_company_matches (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    request_id INT UNSIGNED NOT NULL,
    company_id INT UNSIGNED NOT NULL,
    status ENUM('selected','sent','viewed','interested','clarification','declined','offer','won','lost') NOT NULL DEFAULT 'selected',
    response_text MEDIUMTEXT NULL,
    offered_timeline VARCHAR(190) NULL,
    offered_price VARCHAR(190) NULL,
    sent_at DATETIME NULL,
    responded_at DATETIME NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    CONSTRAINT fk_match_request FOREIGN KEY (request_id) REFERENCES requests(id) ON DELETE CASCADE,
    CONSTRAINT fk_match_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    UNIQUE KEY uq_request_company (request_id, company_id),
    INDEX idx_match_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS request_messages (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    request_id INT UNSIGNED NOT NULL,
    sender_type ENUM('client','admin') NOT NULL,
    sender_id INT UNSIGNED NULL,
    message MEDIUMTEXT NOT NULL,
    is_read TINYINT(1) NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL,
    CONSTRAINT fk_request_messages_request FOREIGN KEY (request_id) REFERENCES requests(id) ON DELETE CASCADE,
    INDEX idx_request_messages_request_created (request_id, created_at),
    INDEX idx_request_messages_unread (sender_type, is_read)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS client_login_attempts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(190) NULL,
    ip_address VARCHAR(45) NOT NULL,
    was_successful TINYINT(1) NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL,
    INDEX idx_client_login_ip_created (ip_address, created_at),
    INDEX idx_client_login_email_created (email, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS company_submissions (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_name VARCHAR(190) NOT NULL,
    contact_name VARCHAR(120) NOT NULL,
    phone VARCHAR(60) NULL,
    email VARCHAR(190) NULL,
    city VARCHAR(120) NOT NULL,
    website VARCHAR(255) NULL,
    services MEDIUMTEXT NOT NULL,
    comment MEDIUMTEXT NULL,
    status ENUM('new','contacted','approved','declined','spam') NOT NULL DEFAULT 'new',
    ip_hash CHAR(64) NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    INDEX idx_submission_status_created (status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS login_attempts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(190) NULL,
    ip_address VARCHAR(45) NOT NULL,
    was_successful TINYINT(1) NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL,
    INDEX idx_login_ip_created (ip_address, created_at),
    INDEX idx_login_email_created (email, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS two_factor_codes (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    admin_id INT UNSIGNED NOT NULL,
    code_hash CHAR(64) NOT NULL,
    expires_at DATETIME NOT NULL,
    attempts TINYINT UNSIGNED NOT NULL DEFAULT 0,
    used_at DATETIME NULL,
    created_at DATETIME NOT NULL,
    CONSTRAINT fk_2fa_admin FOREIGN KEY (admin_id) REFERENCES admins(id) ON DELETE CASCADE,
    INDEX idx_2fa_admin_expiry (admin_id, expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS analytics_pageviews (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    visitor_hash CHAR(64) NOT NULL,
    session_hash CHAR(64) NOT NULL,
    path VARCHAR(255) NOT NULL,
    referrer_domain VARCHAR(190) NULL,
    device_type ENUM('desktop','mobile','tablet','other') NOT NULL DEFAULT 'other',
    browser_name VARCHAR(80) NOT NULL DEFAULT 'Інший',
    created_at DATETIME NOT NULL,
    INDEX idx_analytics_created (created_at),
    INDEX idx_analytics_visitor_created (visitor_hash, created_at),
    INDEX idx_analytics_path_created (path, created_at),
    INDEX idx_analytics_referrer_created (referrer_domain, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS admin_audit_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    admin_id INT UNSIGNED NULL,
    action VARCHAR(190) NOT NULL,
    entity_type VARCHAR(120) NULL,
    entity_id INT UNSIGNED NULL,
    ip_address VARCHAR(45) NOT NULL,
    user_agent VARCHAR(500) NULL,
    meta_json LONGTEXT NULL,
    created_at DATETIME NOT NULL,
    CONSTRAINT fk_audit_admin FOREIGN KEY (admin_id) REFERENCES admins(id) ON DELETE SET NULL,
    INDEX idx_audit_admin_created (admin_id, created_at),
    INDEX idx_audit_action (action)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO categories (name, slug, short_description, description, icon, is_featured, is_active, sort_order, created_at, updated_at) VALUES
('Лазерне різання', 'lazerne-rizannia', 'Точне різання сталі, нержавійки, алюмінію та інших матеріалів.', 'Знайдіть виробництво для лазерного різання деталей за кресленням, одиничних виробів або серійних партій.', 'sparkles', 1, 1, 10, NOW(), NOW()),
('Токарні роботи', 'tokarni-roboty', 'Виготовлення валів, втулок, різьбових і точних деталей.', 'Підбір токарного виробництва для прототипів, ремонту вузлів і серійного виготовлення деталей.', 'settings', 1, 1, 20, NOW(), NOW()),
('Фрезерування ЧПК', 'frezeruvannia-chpk', 'Обробка металу, пластику та композитів на верстатах ЧПК.', 'Пошук виконавців для 2D/3D-фрезерування, пресформ, корпусів та складних деталей.', 'box', 1, 1, 30, NOW(), NOW()),
('3D-друк і прототипи', '3d-druk', 'Швидке виготовлення прототипів і малих серій деталей.', 'Підбір студій і виробництв для FDM, SLA, SLS та інженерного 3D-друку.', 'cube', 1, 1, 40, NOW(), NOW()),
('Гнуття металу', 'hnuttia-metalu', 'Листозгинальні роботи за кресленням або зразком.', 'Виконавці для гнуття листового металу, профілю та виробництва корпусних деталей.', 'layers', 1, 1, 50, NOW(), NOW()),
('Зварювання', 'zvariuvannia', 'MIG/MAG, TIG, точкове та роботизоване зварювання.', 'Пошук виробництв для зварних конструкцій, рам, корпусів і серійних виробів.', 'link', 1, 1, 60, NOW(), NOW()),
('Порошкове фарбування', 'poroshkove-farbuvannia', 'Стійке покриття металевих деталей у потрібному кольорі.', 'Підбір цеху для підготовки поверхні та порошкового фарбування деталей і конструкцій.', 'droplet', 0, 1, 70, NOW(), NOW()),
('Виготовлення корпусів', 'vyhotovlennia-korpusiv', 'Металеві та пластикові корпуси під вашу електроніку або обладнання.', 'Комплексне виготовлення корпусів: різання, гнуття, зварювання, фарбування та складання.', 'briefcase', 0, 1, 80, NOW(), NOW())
ON DUPLICATE KEY UPDATE name = VALUES(name), updated_at = NOW();

INSERT INTO settings (setting_key, setting_value, updated_at) VALUES
('site_name', 'STVORO', NOW()),
('site_tagline', 'Від задуму до виробництва', NOW()),
('contact_email', '', NOW()),
('contact_phone', '', NOW()),
('contact_hours', 'Пн–Пт, 09:00–18:00', NOW()),
('analytics_enabled', '1', NOW()),
('analytics_retention_days', '365', NOW()),
('platform_mode', 'free', NOW()),
('operator_name', '', NOW()),
('operator_edrpou', '', NOW()),
('operator_address', '', NOW()),
('privacy_updated_at', '', NOW()),
('terms_updated_at', '', NOW()),
('telegram_bot_token', '', NOW()),
('telegram_chat_id', '', NOW()),
('telegram_2fa_enabled', '0', NOW()),
('telegram_notifications_enabled', '0', NOW()),
('maintenance_mode', '0', NOW()),
('schema_version', '5', NOW())
ON DUPLICATE KEY UPDATE setting_value = VALUES(setting_value), updated_at = NOW();