-- Call tracking MVP schema
SET NAMES utf8mb4;
SET time_zone = '+00:00';

CREATE TABLE IF NOT EXISTS devices (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    device_id VARCHAR(64) NOT NULL,
    device_secret_hash VARCHAR(255) NOT NULL,
    name VARCHAR(120) NOT NULL DEFAULT '',
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    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 uniq_device_id (device_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS used_nonces (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    device_id VARCHAR(64) NOT NULL,
    nonce VARCHAR(128) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uniq_device_nonce (device_id, nonce),
    KEY idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS call_clicks (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    click_id CHAR(36) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    clicked_phone VARCHAR(20) NOT NULL,
    page_url VARCHAR(2048) NULL,
    button_position VARCHAR(64) NULL,
    session_id VARCHAR(64) NULL,
    fbclid VARCHAR(255) NULL,
    fbc VARCHAR(255) NULL,
    fbp VARCHAR(255) NULL,
    gclid VARCHAR(255) NULL,
    gbraid VARCHAR(255) NULL,
    wbraid VARCHAR(255) NULL,
    utm_source VARCHAR(120) NULL,
    utm_medium VARCHAR(120) NULL,
    utm_campaign VARCHAR(120) NULL,
    utm_content VARCHAR(120) NULL,
    utm_term VARCHAR(120) NULL,
    client_ip VARCHAR(45) NULL,
    user_agent VARCHAR(512) NULL,
    raw_payload_json JSON NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uniq_click_id (click_id),
    KEY idx_phone_created (clicked_phone, created_at),
    KEY idx_session_id (session_id),
    KEY idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS app_calls (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    call_id CHAR(36) NOT NULL,
    device_id VARCHAR(64) NOT NULL,
    caller_phone VARCHAR(20) NULL,
    called_phone VARCHAR(20) NOT NULL,
    client_name VARCHAR(120) NULL,
    client_address VARCHAR(255) NULL,
    area_m2 DECIMAL(10,2) NULL,
    service_type ENUM('roofs') NOT NULL DEFAULT 'roofs',
    traffic_source ENUM('fb','google','other') NOT NULL DEFAULT 'other',
    call_started_at DATETIME NOT NULL,
    duration_sec INT UNSIGNED NOT NULL DEFAULT 0,
    direction ENUM('incoming','outgoing','unknown') NOT NULL DEFAULT 'unknown',
    lead_type ENUM('trash','missed','normal_lead','qualified_lead','measurement','order') NOT NULL,
    operator_note VARCHAR(1000) NULL,
    matched_click_id CHAR(36) NULL,
    raw_payload_json JSON NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uniq_call_id (call_id),
    KEY idx_called_started (called_phone, call_started_at),
    KEY idx_matched_click (matched_click_id),
    KEY idx_device_id (device_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS lead_events (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    event_id CHAR(36) NOT NULL,
    call_id CHAR(36) NOT NULL,
    matched_click_id CHAR(36) NULL,
    event_name VARCHAR(64) NOT NULL,
    event_time DATETIME NOT NULL,
    lead_type ENUM('trash','missed','normal_lead','qualified_lead','measurement','order') NOT NULL,
    status ENUM('pending','processing','sent','failed','skipped') NOT NULL DEFAULT 'pending',
    attempts INT UNSIGNED NOT NULL DEFAULT 0,
    last_error VARCHAR(1000) 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 uniq_event_id (event_id),
    KEY idx_status (status),
    KEY idx_event_time (event_time),
    KEY idx_call_id (call_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS integration_logs (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    lead_event_id BIGINT UNSIGNED NOT NULL,
    platform ENUM('meta','google') NOT NULL,
    status ENUM('pending','sent','failed') NOT NULL DEFAULT 'pending',
    request_json JSON NULL,
    response_json JSON NULL,
    error_message VARCHAR(2000) NULL,
    attempts INT UNSIGNED NOT NULL DEFAULT 0,
    sent_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_platform_status (platform, status),
    KEY idx_lead_event (lead_event_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS allowed_phones (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    phone VARCHAR(20) NOT NULL,
    label VARCHAR(120) NOT NULL DEFAULT '',
    source VARCHAR(120) NOT NULL DEFAULT '',
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uniq_phone (phone)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS rate_limit_hits (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    bucket VARCHAR(120) NOT NULL,
    ip VARCHAR(45) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_bucket_ip_created (bucket, ip, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;