-- phpMyAdmin SQL Dump
-- version 5.2.1
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1:3306
-- Generation Time: Oct 09, 2025 at 04:29 AM
-- Server version: 9.1.0
-- PHP Version: 8.3.14

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `promo`
--

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

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

DROP TABLE IF EXISTS `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`
--

DROP TABLE IF EXISTS `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`
--

DROP TABLE IF EXISTS `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`
--

DROP TABLE IF EXISTS `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 `previous_passwords`
--

DROP TABLE IF EXISTS `previous_passwords`;
CREATE TABLE IF NOT EXISTS `previous_passwords` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL,
  `password_hash` varchar(255) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `previous_passwords`
--

INSERT INTO `previous_passwords` (`id`, `user_id`, `password_hash`, `created_at`) VALUES
(1, 1, '$2y$10$oFA8cIHmshWYjztw6/PmlO4.zHYby/8YSqODI1S7lZlLnYSd7MPza', '2025-10-07 15:09:09');

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

--
-- Table structure for table `users`
--

DROP TABLE IF EXISTS `users`;
CREATE TABLE IF NOT EXISTS `users` (
  `id` int NOT NULL AUTO_INCREMENT,
  `email` varchar(190) NOT NULL,
  `first_name` varchar(100) DEFAULT NULL,
  `last_name` varchar(100) DEFAULT NULL,
  `department` varchar(150) DEFAULT NULL,
  `password_hash` varchar(255) DEFAULT NULL,
  `role` enum('super_admin','admin') NOT NULL DEFAULT 'admin',
  `is_active` tinyint(1) NOT NULL DEFAULT '0',
  `password_changed_at` datetime DEFAULT NULL,
  `last_activation_token` varchar(255) DEFAULT NULL,
  `totp_secret` varchar(64) DEFAULT NULL,
  `totp_enabled` tinyint(1) NOT NULL DEFAULT '0',
  `totp_verified_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `reset_token` varchar(128) DEFAULT NULL,
  `reset_expires` datetime DEFAULT NULL,
  `reset_requested_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`id`, `email`, `first_name`, `last_name`, `department`, `password_hash`, `role`, `is_active`, `password_changed_at`, `last_activation_token`, `totp_secret`, `totp_enabled`, `totp_verified_at`, `created_at`, `reset_token`, `reset_expires`, `reset_requested_at`) VALUES
(1, 'bongkwan.p@gmail.com', 'bongkwan', 'ponyiam', 'it', '$2y$10$oFA8cIHmshWYjztw6/PmlO4.zHYby/8YSqODI1S7lZlLnYSd7MPza', 'super_admin', 1, '2025-10-07 15:09:09', 'b9f6a0b1e3f44f4ab2e9b1d0c8b1f7f21a2c3d4e5f6a7b8c9d0e1f2a3b4c5d6e', '5SLNR2HDW7A43SEZZJZAJ2FIPD24W7RL', 1, '2025-10-07 15:09:09', '2025-10-07 15:04:51', NULL, NULL, NULL),
(7, 'bongkwan.p@kolfers.com', 'bongkwan', 'ponyiam', 'it', NULL, 'admin', 1, NULL, '5299a38573c5dbc9181f461ff6cd5155887a06215fc32c8f3a6c746f6d92af1f', NULL, 0, NULL, '2025-10-09 11:24:21', NULL, NULL, NULL);

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

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

DROP TABLE IF EXISTS `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;

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

--
-- Table structure for table `user_activation_tokens`
--

DROP TABLE IF EXISTS `user_activation_tokens`;
CREATE TABLE IF NOT EXISTS `user_activation_tokens` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL,
  `token` varchar(128) NOT NULL,
  `expires_at` datetime NOT NULL,
  `used_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `token` (`token`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `user_activation_tokens`
--

INSERT INTO `user_activation_tokens` (`id`, `user_id`, `token`, `expires_at`, `used_at`, `created_at`) VALUES
(1, 1, 'b9f6a0b1e3f44f4ab2e9b1d0c8b1f7f21a2c3d4e5f6a7b8c9d0e1f2a3b4c5d6e', '2025-10-09 15:04:52', '2025-10-07 15:09:09', '2025-10-07 15:04:52'),
(6, 7, '5299a38573c5dbc9181f461ff6cd5155887a06215fc32c8f3a6c746f6d92af1f', '2025-10-11 11:24:21', NULL, '2025-10-09 11:24:21');

--
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 `previous_passwords`
--
ALTER TABLE `previous_passwords`
  ADD CONSTRAINT `previous_passwords_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- 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;

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