-- Adminer 4.8.1 MySQL 8.0.40-0ubuntu0.20.04.1 dump

SET foreign_key_checks = 0;
SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';

DELIMITER ;;

DROP PROCEDURE IF EXISTS `update_daily_stats`;;
CREATE PROCEDURE `update_daily_stats`()
BEGIN
   INSERT INTO daily_stats (stat_date, unique_visitors, total_visits, avg_duration)
   SELECT 
       DATE(visit_date) as stat_date,
       COUNT(DISTINCT ip_address) as unique_visitors,
       COUNT(*) as total_visits,
       AVG(session_duration) as avg_duration
   FROM visitors 
   WHERE DATE(visit_date) = CURDATE()
   ON DUPLICATE KEY UPDATE
       unique_visitors = VALUES(unique_visitors),
       total_visits = VALUES(total_visits),
       avg_duration = VALUES(avg_duration);
END;;

DELIMITER ;


CREATE TABLE `apps` (
  `id` int NOT NULL AUTO_INCREMENT,
  `nome` varchar(100) NOT NULL,
  `pacote` varchar(255) NOT NULL,
  `categoria` varchar(50) DEFAULT NULL,
  `icone` varchar(255) DEFAULT NULL,
  `ordem` int DEFAULT '0',
  `status` tinyint DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `apk_url` varchar(500) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `apps` (`id`, `nome`, `pacote`, `categoria`, `icone`, `ordem`, `status`, `created_at`, `updated_at`, `apk_url`) VALUES
(77,	'Player N Pro',	'playerpro.com.player',	'midia',	'player_n_pro_1735168752.png',	3,	1,	'2024-12-25 23:15:27',	'2024-12-25 23:19:12',	'https://launcher.ath.cx/launcher/apks/playern_pro.apk');

CREATE TABLE `banners` (
  `id` int NOT NULL AUTO_INCREMENT,
  `titulo` varchar(255) NOT NULL,
  `imagem` varchar(255) NOT NULL,
  `link` varchar(255) DEFAULT NULL,
  `ordem` int DEFAULT NULL,
  `logo` varchar(100) DEFAULT NULL,
  `status` int DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `banners` (`id`, `titulo`, `imagem`, `link`, `ordem`, `logo`, `status`, `created_at`, `updated_at`) VALUES
(10,	'Banner 1',	'banner1.png',	'',	1,	NULL,	1,	'2024-11-01 02:44:30',	'2024-12-25 18:37:25'),
(15,	'Banner 2',	'banner2.png',	'',	2,	NULL,	1,	'2024-11-01 03:11:18',	'2024-12-25 18:37:25'),
(16,	'Banner 3',	'banner3.png',	'',	3,	NULL,	1,	'2024-11-01 03:11:41',	'2024-12-25 18:37:25'),
(17,	'Banner 4',	'banner4.png',	'',	4,	NULL,	1,	'2024-11-01 03:11:41',	'2024-12-25 18:37:25');

CREATE TABLE `configuracoes` (
  `id` int NOT NULL AUTO_INCREMENT,
  `logo` varchar(255) DEFAULT NULL,
  `background` varchar(255) DEFAULT NULL,
  `qr_code` varchar(255) DEFAULT NULL,
  `qr_code_texto` varchar(100) DEFAULT NULL,
  `cor_primaria` varchar(7) DEFAULT '#0066CC',
  `cor_secundaria` varchar(7) DEFAULT '#003399',
  `texto_superior` varchar(255) DEFAULT NULL,
  `status` tinyint DEFAULT '1',
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `icone_sistema` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `configuracoes` (`id`, `logo`, `background`, `qr_code`, `qr_code_texto`, `cor_primaria`, `cor_secundaria`, `texto_superior`, `status`, `updated_at`, `icone_sistema`) VALUES
(1,	'logo.png',	'background.png',	'qrcode.png',	'',	'#0066CC',	'#003399',	'',	1,	'2024-10-31 23:58:02',	'icone_sistema.png');

CREATE TABLE `configuracoes_dominio` (
  `id` int NOT NULL AUTO_INCREMENT,
  `dominio` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `configuracoes_dominio` (`id`, `dominio`) VALUES
(1,	'https://launcher.ath.cx');

CREATE TABLE `daily_stats` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `stat_date` date NOT NULL,
  `unique_visitors` int NOT NULL DEFAULT '0',
  `total_visits` int NOT NULL DEFAULT '0',
  `avg_duration` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_date` (`stat_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


CREATE TABLE `device_apps` (
  `id` int NOT NULL AUTO_INCREMENT,
  `device_id` int DEFAULT NULL,
  `app_id` int DEFAULT NULL,
  `status` tinyint(1) DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `device_id` (`device_id`),
  KEY `app_id` (`app_id`),
  CONSTRAINT `device_apps_ibfk_1` FOREIGN KEY (`device_id`) REFERENCES `devices` (`id`),
  CONSTRAINT `device_apps_ibfk_2` FOREIGN KEY (`app_id`) REFERENCES `apps` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `device_apps` (`id`, `device_id`, `app_id`, `status`, `created_at`, `updated_at`) VALUES
(59,	12,	77,	1,	'2024-12-25 23:19:26',	'2024-12-25 23:19:26'),
(60,	13,	77,	1,	'2024-12-26 00:12:21',	'2024-12-26 00:12:21');

CREATE TABLE `devices` (
  `id` int NOT NULL AUTO_INCREMENT,
  `mac_address` varchar(17) NOT NULL,
  `device_name` varchar(100) DEFAULT NULL,
  `status` tinyint(1) DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `mac_address` (`mac_address`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `devices` (`id`, `mac_address`, `device_name`, `status`, `created_at`) VALUES
(12,	'1E:66:87:DC:67:3C',	'Tv Box Sala',	1,	'2024-12-25 23:12:09'),
(13,	'64:54:43:47:87:05',	'Bluestack',	1,	'2024-12-26 00:12:14');

CREATE TABLE `launcher_updates` (
  `id` int NOT NULL AUTO_INCREMENT,
  `version_code` int NOT NULL,
  `version_name` varchar(50) NOT NULL,
  `apk_file` varchar(255) NOT NULL,
  `changelog` text,
  `force_update` tinyint(1) DEFAULT '0',
  `status` tinyint(1) DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `launcher_updates` (`id`, `version_code`, `version_name`, `apk_file`, `changelog`, `force_update`, `status`, `created_at`, `updated_at`) VALUES
(17,	15,	'1.0.15',	'launcher_v1.0.15_1735168177.apk',	'Nova interface.\r\nMelhorias no sistema.',	0,	1,	'2024-12-25 23:09:37',	'2024-12-25 23:09:37'),
(18,	16,	'1.0.16',	'launcher_v1.0.16_1735219304.apk',	'Melhorias, em algumas funções da UI.\r\nNovas funcionalidades.\r\nNovas configurações de Rede.',	0,	1,	'2024-12-26 13:21:44',	'2024-12-26 13:21:44'),
(19,	17,	'1.0.17',	'launcher_v1.0.17_1735220851.apk',	'Melhorias no sistema.',	0,	1,	'2024-12-26 13:47:31',	'2024-12-26 13:47:31'),
(20,	18,	'1.0.18',	'launcher_v1.0.18_1735222814.apk',	'Melhorias.',	0,	1,	'2024-12-26 14:20:14',	'2024-12-26 14:20:14'),
(21,	19,	'1.0.19',	'launcher_v1.0.19_1735227336.apk',	'Nova interface.',	0,	1,	'2024-12-26 15:35:36',	'2024-12-26 15:35:36');

CREATE TABLE `system_version` (
  `id` int NOT NULL AUTO_INCREMENT,
  `current_version` varchar(20) NOT NULL DEFAULT '1.0.0',
  `last_check` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `system_version` (`id`, `current_version`, `last_check`, `updated_at`) VALUES
(1,	'1.0.0',	'2024-11-07 00:09:39',	'2024-11-07 00:09:39');

CREATE TABLE `visitors` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `ip_address` varchar(50) NOT NULL,
  `visit_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `page_url` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `visitors` (`id`, `ip_address`, `visit_date`, `page_url`) VALUES
(1,	'127.0.0.1',	'2024-11-27 15:52:55',	'/launcher/visitor_counter.php'),
(2,	'127.0.0.1',	'2024-11-27 15:52:55',	'/launcher/visitor_counter.php'),
(3,	'127.0.0.1',	'2024-11-27 15:53:08',	'/launcher/visitor_counter.php'),
(4,	'127.0.0.1',	'2024-11-27 15:54:24',	'/launcher/visitor_counter.php'),
(5,	'127.0.0.1',	'2024-11-27 15:56:46',	'/launcher/visitor_counter.php');

-- 2024-12-27 22:27:44
