CREATE TABLE IF NOT EXISTS `visit` (
  `id` BIGINT(6) NOT NULL AUTO_INCREMENT,
  `siteId` BIGINT(6) NOT NULL,
  `clientId` BIGINT(6) NULL,
  `guest` TINYINT(1) NULL DEFAULT 1,
  `ip` LONGTEXT  NULL,
  `os` LONGTEXT NULL,
  `screen` LONGTEXT  NULL,
  `page` LONGTEXT NULL,
  `referer` LONGTEXT NULL,
  `device` LONGTEXT  NULL,
  `location` LONGTEXT  NULL,
  `created` TIMESTAMP(6) NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id`, `siteId`),
  INDEX `fk_visit_site1_idx` (`siteId` ASC),
  CONSTRAINT `fk_visit_site1`
    FOREIGN KEY (`siteId`)
    REFERENCES `site` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

SELECT v.id AS id, v.siteId, v.clientId, v.guest, MAX(v.ip) AS ip, MAX(v.os) AS os, MAX(v.screen) AS screen, MAX(v.device) AS device, MAX(v.location) AS location, MAX(v.date) AS date, MAX(v.time) AS time, MAX(v.created) AS created FROM visit AS v WHERE siteId = ? GROUP BY v.id, v.siteId, v.clientId ORDER BY MAX(v.created) DESC;

CREATE TABLE IF NOT EXISTS `visit` (
  `id` BIGINT(6) NOT NULL AUTO_INCREMENT,
  `siteId` BIGINT(6) NOT NULL,
  `clientId` BIGINT(6) NULL,
  `guest` TINYINT(1) NULL DEFAULT 1,
  `created` TIMESTAMP(6) NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id`, `siteId`),
  INDEX `fk_visit_site1_idx` (`siteId` ASC),
  CONSTRAINT `fk_visit_site1`
    FOREIGN KEY (`siteId`)
    REFERENCES `site` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `visitinfo` (
  `id` BIGINT(6) NOT NULL AUTO_INCREMENT,
  `visitId` BIGINT(6) NOT NULL,
  `ip` LONGTEXT NULL,
  `os` LONGTEXT NULL,
  `screen` LONGTEXT NULL,
  `page` LONGTEXT NULL,
  `referer` LONGTEXT NULL,
  `device` LONGTEXT NULL,
  `created` TIMESTAMP(6) NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id`, `visitId`),
  INDEX `fk_visitinfo_visit2_idx` (`visitId` ASC),
  CONSTRAINT `fk_visitinfo_visit2`
    FOREIGN KEY (`visitId`)
    REFERENCES `visit` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;