 
CREATE TABLE user_api_usage (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    company_user_id DOUBLE DEFAULT NULL,
    endpoint VARCHAR(255) NOT NULL,
    method VARCHAR(10) NOT NULL,
    timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
    response_status INT,
    response_time_ms INT,
    ip_address VARCHAR(45) NOT NULL, -- IPv6 compatible
    user_agent VARCHAR(500),
    FOREIGN KEY (company_user_id) REFERENCES tbl_company_user(company_user_id)
);


CREATE TABLE user_interactions (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    company_user_id DOUBLE NOT NULL,
    interaction_type VARCHAR(50) NOT NULL,
    element_id VARCHAR(255),
    page_url VARCHAR(255) NOT NULL,
    timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
    additional_data JSON,
    FOREIGN KEY (company_user_id) REFERENCES tbl_company_user(company_user_id)
);

-- Table to track user engagement time
CREATE TABLE user_engagement (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    company_user_id DOUBLE NOT NULL,
    page_url VARCHAR(255) NOT NULL,
    start_time DATETIME NOT NULL,
    end_time DATETIME,
    duration_seconds INT,
    session_id VARCHAR(100),
    FOREIGN KEY (company_user_id) REFERENCES tbl_company_user(company_user_id)
);


-- Index for better query performance
CREATE INDEX idx_user_api_usage_user_id ON user_api_usage(user_id);
CREATE INDEX idx_user_api_usage_timestamp ON user_api_usage(timestamp);
CREATE INDEX idx_user_interactions_user_id ON user_interactions(user_id);
CREATE INDEX idx_user_interactions_timestamp ON user_interactions(timestamp);
CREATE INDEX idx_user_engagement_user_id ON user_engagement(user_id);
CREATE INDEX idx_user_engagement_start_time ON user_engagement(start_time);
                