--
-- Host: localhost    Database: gradakan_gogens
-- ------------------------------------------------------


--
-- Table structure for table `shortlinks`
--

CREATE TABLE `shortlinks` (
  `code` varchar(100) NOT NULL COMMENT 'Unique shortlink code',
  `domain` varchar(255) DEFAULT NULL COMMENT 'Associated domain (NULL = default)',
  `og_title` varchar(500) NOT NULL,
  `og_description` text NOT NULL,
  `og_image_url` varchar(1000) DEFAULT '',
  `og_type` varchar(50) DEFAULT 'website',
  `og_site_name` varchar(255) DEFAULT '',
  `og_locale` varchar(10) DEFAULT 'id_ID',
  `domain_type` enum('global','user') DEFAULT NULL COMMENT 'Domain type: global or user',
  `domain_id` bigint(20) unsigned DEFAULT NULL COMMENT 'Reference to global_domains.id or user_domains.id',
  `use_shim` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Enable shim link wrapper for this shortlink',
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`code`),
  KEY `idx_is_active` (`is_active`),
  KEY `idx_domain_active` (`domain`,`is_active`),
  KEY `idx_use_shim` (`use_shim`),
  KEY `idx_created_at` (`created_at`),
  KEY `idx_domain_type` (`domain_type`),
  KEY `idx_domain_id` (`domain_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Shortlink codes with OpenGraph metadata';

--
-- Dumping data for table `shortlinks`
--


--
-- Table structure for table `traffic_analytics`
--

CREATE TABLE `traffic_analytics` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `shortlink_id` bigint(20) unsigned DEFAULT NULL COMMENT 'MUST match shortlinks.id type exactly',
  `shortlink_code` varchar(50) NOT NULL,
  `target_url` text NOT NULL,
  `redirect_url` text DEFAULT NULL,
  `ip_bin` varbinary(16) NOT NULL COMMENT 'Store inet_pton(ip); 4 bytes IPv4, 16 bytes IPv6',
  `ip_text` varchar(45) GENERATED ALWAYS AS (inet6_ntoa(`ip_bin`)) STORED,
  `ip_is_v6` tinyint(1) GENERATED ALWAYS AS (case when octet_length(`ip_bin`) = 16 then 1 else 0 end) STORED,
  `country_code` char(2) DEFAULT NULL,
  `country_name` varchar(100) DEFAULT NULL,
  `city` varchar(100) DEFAULT NULL,
  `continent_code` char(2) DEFAULT NULL,
  `latitude` decimal(10,8) DEFAULT NULL,
  `longitude` decimal(11,8) DEFAULT NULL,
  `asn` int(10) unsigned DEFAULT NULL,
  `asn_org` varchar(255) DEFAULT NULL,
  `isp` varchar(255) DEFAULT NULL,
  `is_vpn` tinyint(1) NOT NULL DEFAULT 0,
  `is_proxy` tinyint(1) NOT NULL DEFAULT 0,
  `is_tor` tinyint(1) NOT NULL DEFAULT 0,
  `is_datacenter` tinyint(1) NOT NULL DEFAULT 0,
  `risk_score` tinyint(3) unsigned NOT NULL DEFAULT 0 COMMENT 'App should keep this 0..100',
  `blackbox_signal` enum('Y','N','E') DEFAULT NULL,
  `device_type` enum('mobile','tablet','desktop','bot','unknown') NOT NULL DEFAULT 'unknown',
  `device_brand` varchar(50) DEFAULT NULL,
  `device_model` varchar(100) DEFAULT NULL,
  `browser_name` varchar(50) DEFAULT NULL,
  `browser_version` varchar(50) DEFAULT NULL,
  `os_name` varchar(50) DEFAULT NULL,
  `os_version` varchar(50) DEFAULT NULL,
  `platform` varchar(50) DEFAULT NULL,
  `user_agent` text DEFAULT NULL,
  `is_bot` tinyint(1) NOT NULL DEFAULT 0,
  `bot_name` varchar(100) DEFAULT NULL,
  `referer` text DEFAULT NULL,
  `http_method` enum('GET','POST','HEAD','PUT','DELETE','OPTIONS','PATCH') NOT NULL DEFAULT 'GET',
  `http_protocol` enum('HTTP/1.0','HTTP/1.1','HTTP/2','HTTP/3') DEFAULT NULL,
  `visited_at` datetime(6) NOT NULL DEFAULT current_timestamp(6),
  `response_time_ms` int(10) unsigned DEFAULT NULL COMMENT 'Response time in milliseconds',
  PRIMARY KEY (`id`),
  KEY `idx_shortlink_code_visited` (`shortlink_code`,`visited_at`),
  KEY `idx_visited_shortlink` (`visited_at`,`shortlink_code`),
  KEY `idx_ip_bin` (`ip_bin`),
  KEY `idx_visited_country` (`visited_at`,`country_code`),
  KEY `idx_visited_device` (`visited_at`,`device_type`),
  KEY `idx_is_bot_visited` (`is_bot`,`visited_at`),
  KEY `idx_risk_visited` (`risk_score`,`visited_at`),
  KEY `idx_shortlink_id` (`shortlink_id`),
  KEY `idx_visited_at` (`visited_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC COMMENT='Traffic analytics and monitoring data';

--
-- Dumping data for table `traffic_analytics`
--


--
-- Table structure for table `traffic_summary_daily`
--

CREATE TABLE `traffic_summary_daily` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `shortlink_code` varchar(50) NOT NULL,
  `summary_date` date NOT NULL,
  `total_visits` int(10) unsigned NOT NULL DEFAULT 0,
  `unique_ips` int(10) unsigned NOT NULL DEFAULT 0,
  `bot_visits` int(10) unsigned NOT NULL DEFAULT 0,
  `mobile_visits` int(10) unsigned NOT NULL DEFAULT 0,
  `desktop_visits` int(10) unsigned NOT NULL DEFAULT 0,
  `tablet_visits` int(10) unsigned NOT NULL DEFAULT 0,
  `risky_visits` int(10) unsigned NOT NULL DEFAULT 0,
  `vpn_visits` int(10) unsigned NOT NULL DEFAULT 0,
  `proxy_visits` int(10) unsigned NOT NULL DEFAULT 0,
  `tor_visits` int(10) unsigned NOT NULL DEFAULT 0,
  `top_countries` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '{"US": 100, "ID": 50}' CHECK (json_valid(`top_countries`)),
  `top_devices` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '{"iPhone": 50, "Samsung": 30}' CHECK (json_valid(`top_devices`)),
  `top_browsers` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '{"Chrome": 80, "Safari": 20}' CHECK (json_valid(`top_browsers`)),
  `top_referrers` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '{"facebook.com": 100}' CHECK (json_valid(`top_referrers`)),
  `avg_response_time_ms` int(10) unsigned DEFAULT NULL,
  `created_at` datetime(6) NOT NULL DEFAULT current_timestamp(6),
  `updated_at` datetime(6) NOT NULL DEFAULT current_timestamp(6) ON UPDATE current_timestamp(6),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_shortlink_date` (`shortlink_code`,`summary_date`),
  KEY `idx_summary_date` (`summary_date`),
  KEY `idx_summary_shortlink` (`shortlink_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC COMMENT='Daily summary for faster dashboard queries';

--
-- Dumping data for table `traffic_summary_daily`
--


--
-- Dumping events for database 'gradakan_gogens'
--

--
-- Dumping routines for database 'gradakan_gogens'
--


