SET NAMES utf8mb4;
SET time_zone = '+00:00';

CREATE TABLE IF NOT EXISTS `users_id` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(100) NOT NULL,
  `password` varchar(225) NULL,
  `email` varchar(225) NOT NULL UNIQUE,
  `phone` varchar(225) NULL,
  `role` varchar(10) NOT NULL,
  `register_date` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `locked` TINYINT(1) NOT NULL DEFAULT 0,
  `broadcast_quota` INT NOT NULL DEFAULT 25,
  `broadcast_sent` INT NOT NULL DEFAULT 0,
  PRIMARY KEY(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `comment_id` (
  `no_id` bigint(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `nama` varchar(225) NOT NULL,
  `tgl` varchar(25) DEFAULT NULL,
  `jam` varchar(25) DEFAULT NULL,
  `tamu` varchar(25) NULL,
  `jml_tamu` INT UNSIGNED NULL,
  `whatsapp` varchar(100) NULL,
  PRIMARY KEY(`no_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE INDEX IF NOT EXISTS `idx_nama` ON `comment_id`(`nama`);
CREATE INDEX IF NOT EXISTS `idx_whatsapp` ON `comment_id`(`whatsapp`);
CREATE INDEX IF NOT EXISTS `idx_tgl` ON `comment_id`(`tgl`);

CREATE TABLE IF NOT EXISTS `event_id` (
  `id` bigint(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `acara` varchar(225) NULL,
  `jam` varchar(225) NULL,
  `tanggal` varchar(225) NULL,
  `lokasi` varchar(255) NULL,
  PRIMARY KEY(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `smtp` (
  `id` bigint(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `host` varchar(225) DEFAULT NULL,
  `port` varchar(225) DEFAULT NULL,
  `encryption` varchar(225) DEFAULT NULL,
  `username` varchar(225) DEFAULT NULL,
  `password` varchar(225) DEFAULT NULL,
  `emailweb` varchar(225) DEFAULT NULL,
  `nameweb` varchar(225) DEFAULT NULL,
  PRIMARY KEY(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `categories` (
  `id` bigint(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `category_name` varchar(225) NOT NULL,
  PRIMARY KEY(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `general_settings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `iframe` varchar(255) NULL,
  `logo` varchar(225) NULL,
  `bg` TINYINT(1) NULL,
  `video` varchar(255) NULL,
  `image` varchar(255) NULL,
  `color` varchar(32) NULL,
  `text` TEXT NULL,
  `font` varchar(100) NULL,
  `hero_color_start` varchar(32) NULL,
  `hero_color_end` varchar(32) NULL,
  `hero_text_color` varchar(32) NULL,
  `hero_image` varchar(255) NULL,
  `size_welcome` int(11) NULL,
  `size_tamu` int(11) NULL,
  `size_event_name` int(11) NULL,
  `size_event_date` int(11) NULL,
  `size_event_location` int(11) NULL,
  `size_event_time` int(11) NULL,
  `font_color` varchar(32) NULL,
  `timer` varchar(50) NULL,
  `timezone` varchar(50) NULL,
  `cover` TINYINT(1) NULL,
  `out_animation_style` VARCHAR(32) NULL,
  `out_animation_duration_ms` INT NULL,
  `delay_welcome` INT NULL,
  `delay_gethering` INT NULL,
  `manual_loop_enable` TINYINT(1) NULL,
  `delay_manual_loop` INT NULL,
  `invite_link` varchar(255) NULL,
  `frame_overlay` varchar(255) NULL,
  `show_frame_overlay` TINYINT(1) NULL,
  `show_event_name` TINYINT(1) NOT NULL DEFAULT 1,
  `show_guest_category` TINYINT(1) NOT NULL DEFAULT 1,
  `show_event_date` TINYINT(1) NOT NULL DEFAULT 1,
  `show_event_time` TINYINT(1) NOT NULL DEFAULT 1,
  `show_event_location` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `datawa` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `whatsapp` LONGTEXT NULL,
  `wa_provider` TINYINT(1) NULL,
  `apikey` varchar(225) NULL,
  `apikeyone` varchar(225) NULL,
  `apiurlonesender` varchar(225) NULL,
  PRIMARY KEY(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `app_language_settings` (
  `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `lang_code` VARCHAR(5) NOT NULL DEFAULT 'en',
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `login_attempts` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `ip` VARCHAR(45) NOT NULL,
  `attempt_count` INT(11) NOT NULL DEFAULT 0,
  `last_attempt` DATETIME DEFAULT NULL,
  `locked_until` DATETIME DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY(`id`),
  KEY `idx_ip` (`ip`),
  KEY `idx_locked` (`locked_until`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `user_login_attempts` (
  `user_id` INT NOT NULL PRIMARY KEY,
  `attempt_count` INT NOT NULL DEFAULT 0,
  `last_attempt` DATETIME NULL,
  `locked_until` DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `password_resets` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `user_id` INT(11) NOT NULL,
  `token_hash` VARCHAR(64) NOT NULL,
  `expires_at` DATETIME DEFAULT NULL,
  `used_at` DATETIME DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY(`id`),
  KEY `idx_token` (`token_hash`),
  KEY `idx_user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `users_id` (`username`,`password`,`email`,`phone`,`role`,`register_date`,`locked`,`broadcast_quota`,`broadcast_sent`) VALUES
('admin', MD5('admin'), 'emailadmin@gmail.com', '000000', 'admin', NOW(), 0, 0, 0);

INSERT INTO `event_id` (`acara`,`jam`,`tanggal`,`lokasi`) VALUES
('Buku Tamu Digital', '09:30 s.d 12:00 WIT', '12 Desember 202x', 'WDP Convention Hall');

INSERT INTO `smtp` (`host`,`port`,`encryption`,`username`,`password`,`emailweb`,`nameweb`) VALUES
('','','','','','','');

INSERT INTO `categories` (`category_name`) VALUES
('VIP'),('Keluarga');

INSERT INTO `general_settings` (
  `iframe`,`invite_link`,`logo`,`bg`,`video`,`image`,`color`,`text`,`font`,`hero_color_start`,`hero_color_end`,`hero_text_color`,`hero_image`,`size_welcome`,`size_tamu`,`size_event_name`,`size_event_date`,`size_event_location`,`size_event_time`,`font_color`,`timer`,`timezone`,`cover`,`out_animation_style`,`out_animation_duration_ms`,`delay_welcome`,`delay_gethering`,`manual_loop_enable`,`delay_manual_loop`,`frame_overlay`,`show_frame_overlay`
) VALUES (
  'https://bukutamu.net','https://weddingpress.net','images/icon.png',0,'https://www.youtube.com/watch?v=XWKfMPkXyG0','','#c89556','Selamat Datang','Poppins','#1e73ff','#4ca0ff','#ffffff','images/WDP-Wedding-Couple.webp',18,39,24,16,16,16,'#ffffff','5000','Asia/Jakarta',1,'fade',300,3,3,1,3,NULL,1
);

INSERT INTO `datawa` (`whatsapp`,`wa_provider`,`apikey`,`apikeyone`,`apiurlonesender`) VALUES
('Terima kasih yang sebesar-besarnya untuk [nama] telah berhadir di hari berbahagia kami pada hari [tgl] jam [jam]\nTerima kasih atas Doa restu terbaik yang anda berikan pada kami dan keluarga.',0,'','','');

INSERT INTO `app_language_settings` (`lang_code`) VALUES ('en');