-- https://www.phpmyadmin.net/
--

START TRANSACTION;



--
-- Database: `promo`
--

-- --------------------------------------------------------

--
-- Table structure for table `activity_logs`
--

CREATE TABLE IF NOT EXISTS `activity_logs` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int DEFAULT NULL,
  `event` varchar(100) NOT NULL,
  `ip_address` varchar(64) DEFAULT NULL,
  `user_agent` text,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `activity_logs`
--

INSERT INTO `activity_logs` (`id`, `user_id`, `event`, `ip_address`, `user_agent`, `created_at`) VALUES
(1, 1, 'login_success', '127.0.0.1', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/141.0.0.0 Safari/537.36', '2025-10-07 15:10:07'),
(2, 1, 'login_success', '127.0.0.1', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/141.0.0.0 Safari/537.36', '2025-10-07 15:21:47'),
(3, 1, 'login_success', '127.0.0.1', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/141.0.0.0 Safari/537.36', '2025-10-08 10:07:59'),
(4, 1, 'login_success', '127.0.0.1', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/141.0.0.0 Safari/537.36', '2025-10-09 11:23:28');

-- --------------------------------------------------------

--
-- Table structure for table `banners`
--

CREATE TABLE IF NOT EXISTS `banners` (
  `id` int NOT NULL AUTO_INCREMENT,
  `campaign_id` int NOT NULL,
  `category` enum('Bank Promotion','General Banner','Page') NOT NULL DEFAULT 'General Banner',
  `name` varchar(255) NOT NULL,
  `url_slug` varchar(255) DEFAULT NULL,
  `status` tinyint(1) NOT NULL DEFAULT '0',
  `details_html` mediumtext,
  `banner_image_1` varchar(500) DEFAULT NULL,
  `banner_image_2` varchar(500) DEFAULT NULL,
  `display` enum('file','whole_page') NOT NULL DEFAULT 'file',
  `sort_order` int NOT NULL DEFAULT '0',
  `banner_start` datetime DEFAULT NULL,
  `banner_end` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_banners_category_slug` (`category`,`url_slug`),
  KEY `campaign_id` (`campaign_id`),
  KEY `fk_banners_created_by` (`created_by`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `campaigns`
--

CREATE TABLE IF NOT EXISTS `campaigns` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(200) NOT NULL,
  `description` text,
  `status` tinyint(1) NOT NULL DEFAULT '0',
  `campaign_start` datetime DEFAULT NULL,
  `campaign_end` datetime DEFAULT NULL,
  `url_rewrite` varchar(255) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `url_rewrite` (`url_rewrite`),
  KEY `fk_campaigns_created_by` (`created_by`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `campaigns`
--

INSERT INTO `campaigns` (`id`, `name`, `description`, `status`, `campaign_start`, `campaign_end`, `url_rewrite`, `created_at`, `created_by`) VALUES
(1, '1-31 ตุลาคม 2568', '', 1, '2025-10-01 15:11:00', '2025-10-31 15:11:00', 'bank-oct-2025', '2025-10-07 15:11:20', NULL);

-- --------------------------------------------------------

--
-- Table structure for table `homepage_menus`
--

CREATE TABLE IF NOT EXISTS `homepage_menus` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `details_html` mediumtext,
  `status` tinyint(1) NOT NULL DEFAULT '0',
  `display_top` tinyint(1) NOT NULL DEFAULT '0',
  `sort_order` int NOT NULL DEFAULT '0',
  `link_type` enum('campaign','banner_page','external') NOT NULL,
  `link_campaign_id` int DEFAULT NULL,
  `link_banner_id` int DEFAULT NULL,
  `link_external_url` varchar(500) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `link_campaign_id` (`link_campaign_id`),
  KEY `link_banner_id` (`link_banner_id`),
  KEY `created_by` (`created_by`),
  KEY `link_type` (`link_type`),
  KEY `status` (`status`),
  KEY `display_top` (`display_top`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `homepage_menus`
--

INSERT INTO `homepage_menus` (`id`, `name`, `details_html`, `status`, `display_top`, `sort_order`, `link_type`, `link_campaign_id`, `link_banner_id`, `link_external_url`, `created_at`, `created_by`) VALUES
(1, 'HOME', '<p><br></p>', 1, 1, 3, 'external', 1, NULL, 'https://promotion.homepro.co.th', '2025-10-07 16:15:07', 1),
(2, 'SHOP', '', 1, 1, 1, 'external', NULL, NULL, 'https://homepro.co.th', '2025-10-08 10:09:34', 1),
(3, 'BANK & PARTNER', '<p><br></p>', 1, 1, 2, 'external', NULL, NULL, 'https://promotion.homepro.co.th/bank-promotion', '2025-10-08 10:10:19', 1),
(5, 'HOMEGURU', '', 1, 1, 4, 'external', NULL, NULL, 'https://www.homepro.co.th/homeguru', '2025-10-08 10:23:56', 1);

-- --------------------------------------------------------

--
-- Table structure for table `user_2fa_codes`
--

CREATE TABLE IF NOT EXISTS `user_2fa_codes` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL,
  `code` varchar(10) NOT NULL,
  `expires_at` datetime NOT NULL,
  `used_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`,`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
ALTER TABLE `banners`
  ADD CONSTRAINT `banners_ibfk_1` FOREIGN KEY (`campaign_id`) REFERENCES `campaigns` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_banners_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `campaigns`
--
ALTER TABLE `campaigns`
  ADD CONSTRAINT `fk_campaigns_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `homepage_menus`
--
ALTER TABLE `homepage_menus`
  ADD CONSTRAINT `homepage_menus_ibfk_1` FOREIGN KEY (`link_campaign_id`) REFERENCES `campaigns` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `homepage_menus_ibfk_2` FOREIGN KEY (`link_banner_id`) REFERENCES `banners` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `homepage_menus_ibfk_3` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `user_2fa_codes`
--
ALTER TABLE `user_2fa_codes`
  ADD CONSTRAINT `user_2fa_codes_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;
COMMIT;

