SET NAMES utf8mb4;

ALTER TABLE users
    ADD COLUMN last_login_at DATETIME NULL AFTER status,
    ADD INDEX idx_users_last_login_at (last_login_at);

CREATE TABLE system_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    level VARCHAR(30) NOT NULL,
    message TEXT NOT NULL,
    file VARCHAR(500) NULL,
    line INT UNSIGNED NULL,
    context_json JSON NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_system_logs_level_created_at (level, created_at),
    INDEX idx_system_logs_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE network_locations (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    network_id BIGINT UNSIGNED NOT NULL,
    external_id VARCHAR(190) NOT NULL,
    name VARCHAR(190) NOT NULL,
    address VARCHAR(255) NULL,
    postal_code VARCHAR(30) NULL,
    city VARCHAR(120) NULL,
    phone VARCHAR(40) NULL,
    email VARCHAR(190) NULL,
    status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
    last_synced_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_network_locations_external (network_id, external_id),
    INDEX idx_network_locations_status (network_id, status),
    CONSTRAINT fk_network_locations_network FOREIGN KEY (network_id) REFERENCES networks(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE network_location_shops (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    network_location_id BIGINT UNSIGNED NOT NULL,
    shop_id BIGINT UNSIGNED NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_network_location_shop (network_location_id, shop_id),
    INDEX idx_network_location_shops_shop (shop_id),
    CONSTRAINT fk_network_location_shops_location FOREIGN KEY (network_location_id) REFERENCES network_locations(id),
    CONSTRAINT fk_network_location_shops_shop FOREIGN KEY (shop_id) REFERENCES shops(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE network_role_mappings (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    network_id BIGINT UNSIGNED NOT NULL,
    external_role VARCHAR(190) NOT NULL,
    target_scope ENUM('network', 'shop') NOT NULL,
    role_code VARCHAR(100) NOT NULL,
    is_active TINYINT NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_network_role_mapping (network_id, external_role),
    INDEX idx_network_role_mappings_active (network_id, is_active),
    CONSTRAINT fk_network_role_mappings_network FOREIGN KEY (network_id) REFERENCES networks(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE network_users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    network_id BIGINT UNSIGNED NOT NULL,
    location_id BIGINT UNSIGNED NULL,
    account_user_id BIGINT UNSIGNED NULL,
    external_id VARCHAR(190) NOT NULL,
    firstname VARCHAR(120) NOT NULL,
    lastname VARCHAR(120) NOT NULL,
    email VARCHAR(190) NOT NULL,
    external_role VARCHAR(190) NULL,
    role_code VARCHAR(100) NULL,
    is_active TINYINT NOT NULL DEFAULT 1,
    last_synced_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_network_users_external (network_id, external_id),
    INDEX idx_network_users_location (location_id),
    INDEX idx_network_users_account (account_user_id),
    INDEX idx_network_users_active (network_id, is_active),
    CONSTRAINT fk_network_users_network FOREIGN KEY (network_id) REFERENCES networks(id),
    CONSTRAINT fk_network_users_location FOREIGN KEY (location_id) REFERENCES network_locations(id),
    CONSTRAINT fk_network_users_account FOREIGN KEY (account_user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE network_user_location_history (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    network_user_id BIGINT UNSIGNED NOT NULL,
    from_location_id BIGINT UNSIGNED NULL,
    to_location_id BIGINT UNSIGNED NULL,
    changed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_network_user_location_history_user (network_user_id, changed_at),
    CONSTRAINT fk_network_user_location_history_user FOREIGN KEY (network_user_id) REFERENCES network_users(id),
    CONSTRAINT fk_network_user_location_history_from FOREIGN KEY (from_location_id) REFERENCES network_locations(id),
    CONSTRAINT fk_network_user_location_history_to FOREIGN KEY (to_location_id) REFERENCES network_locations(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE network_api_clients (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    network_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(190) NOT NULL,
    api_key_hash CHAR(64) NOT NULL,
    api_key_prefix VARCHAR(24) NOT NULL,
    is_active TINYINT NOT NULL DEFAULT 1,
    last_used_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_network_api_clients_hash (api_key_hash),
    INDEX idx_network_api_clients_active (network_id, is_active),
    CONSTRAINT fk_network_api_clients_network FOREIGN KEY (network_id) REFERENCES networks(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE network_api_rate_limits (
    api_client_id BIGINT UNSIGNED PRIMARY KEY,
    requests_count INT UNSIGNED NOT NULL DEFAULT 0,
    window_started_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_network_api_rate_limits_client FOREIGN KEY (api_client_id) REFERENCES network_api_clients(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE network_sync_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    network_id BIGINT UNSIGNED NOT NULL,
    api_client_id BIGINT UNSIGNED NOT NULL,
    request_id VARCHAR(64) NOT NULL,
    sync_mode ENUM('partial', 'full') NOT NULL,
    status ENUM('processing', 'success', 'failed') NOT NULL DEFAULT 'processing',
    received_locations INT UNSIGNED NOT NULL DEFAULT 0,
    received_users INT UNSIGNED NOT NULL DEFAULT 0,
    created_locations INT UNSIGNED NOT NULL DEFAULT 0,
    updated_locations INT UNSIGNED NOT NULL DEFAULT 0,
    deactivated_locations INT UNSIGNED NOT NULL DEFAULT 0,
    created_users INT UNSIGNED NOT NULL DEFAULT 0,
    updated_users INT UNSIGNED NOT NULL DEFAULT 0,
    deactivated_users INT UNSIGNED NOT NULL DEFAULT 0,
    errors_count INT UNSIGNED NOT NULL DEFAULT 0,
    errors_json JSON NULL,
    ip_address VARCHAR(64) NULL,
    user_agent VARCHAR(500) NULL,
    started_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    completed_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_network_sync_logs_request (request_id),
    INDEX idx_network_sync_logs_network_created_at (network_id, created_at),
    INDEX idx_network_sync_logs_status (status, created_at),
    CONSTRAINT fk_network_sync_logs_network FOREIGN KEY (network_id) REFERENCES networks(id),
    CONSTRAINT fk_network_sync_logs_client FOREIGN KEY (api_client_id) REFERENCES network_api_clients(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

