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

START TRANSACTION;



--
-- Database: `serenity_premium`
--

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

--
-- Table structure for table `account_extension`
--

CREATE TABLE `account_extension` (
  `id_account` varchar(36) NOT NULL,
  `name_account` varchar(100) NOT NULL,
  `icon_url` text DEFAULT NULL,
  `redirect_url` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `account_extension`
--

INSERT INTO `account_extension` (`id_account`, `name_account`, `icon_url`, `redirect_url`, `created_at`, `updated_at`) VALUES
('bb11e686-ff4b-11ef-a027-42010ab80003', 'Netflix', 'https://www.netflix.com/favicon.ico', 'https://www.netflix.com/', '2025-03-12 14:10:19', '2025-03-12 14:10:19'),
('f4c74c9d-fd6a-11ef-a027-42010ab80003', 'ChatGPT Plus', 'https://chatgpt.com/favicon.ico', 'https://chatgpt.com/', '2025-03-10 04:48:48', '2025-03-10 04:48:48');

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

--
-- Table structure for table `account_number`
--

CREATE TABLE `account_number` (
  `id_number` varchar(36) NOT NULL,
  `id_account` varchar(36) NOT NULL,
  `number_account` varchar(100) NOT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `account_number`
--

INSERT INTO `account_number` (`id_number`, `id_account`, `number_account`, `is_active`, `created_at`, `updated_at`) VALUES
('55a0813d-fdf4-11ef-a027-42010ab80003', 'f4c74c9d-fd6a-11ef-a027-42010ab80003', 'ChatGPT Plus 27', 1, '2025-03-10 21:12:12', '2025-03-10 21:47:05'),
('ae0861cf-fdac-11ef-a027-42010ab80003', 'f4c74c9d-fd6a-11ef-a027-42010ab80003', 'ChatGPT Plus 26', 1, '2025-03-10 12:39:16', '2025-03-10 21:46:58'),
('de599a4e-ff4b-11ef-a027-42010ab80003', 'bb11e686-ff4b-11ef-a027-42010ab80003', 'Netflix 37', 1, '2025-03-12 14:11:18', '2025-03-12 14:11:18');

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

--
-- Table structure for table `extension_files`
--

CREATE TABLE `extension_files` (
  `id_file` varchar(36) NOT NULL,
  `version` varchar(20) NOT NULL,
  `file_name` varchar(255) NOT NULL,
  `file_path` varchar(255) NOT NULL,
  `file_size` bigint(20) NOT NULL,
  `file_type` varchar(50) NOT NULL,
  `description` text DEFAULT NULL,
  `changelog` text DEFAULT NULL,
  `uploaded_by` varchar(36) NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `extension_files`
--

INSERT INTO `extension_files` (`id_file`, `version`, `file_name`, `file_path`, `file_size`, `file_type`, `description`, `changelog`, `uploaded_by`, `created_at`, `updated_at`) VALUES
('d192bb0a-fef8-11ef-a027-42010ab80003', '2.5.1', 'serenity-extension-release2.5.1 (2).zip', 'extension-v2.5.1-1741753009312.zip', 1042219, 'application/x-zip-compressed', 'ss', 'sss', '45b2f51a-fd05-11ef-a027-42010ab80003', '2025-03-12 04:16:49', '2025-03-12 04:16:49');

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

--
-- Table structure for table `extension_popups`
--

CREATE TABLE `extension_popups` (
  `id_popup` varchar(36) NOT NULL,
  `title` varchar(255) NOT NULL,
  `message` text NOT NULL,
  `type` enum('info','warning','error','success') DEFAULT 'info',
  `button_type` enum('single','double') NOT NULL,
  `primary_button_text` varchar(50) NOT NULL,
  `secondary_button_text` varchar(50) DEFAULT NULL,
  `redirect_url` text DEFAULT NULL,
  `max_displays` int(11) DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `start_date` timestamp NULL DEFAULT current_timestamp(),
  `end_date` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `extension_settings`
--

CREATE TABLE `extension_settings` (
  `id_setting` varchar(36) NOT NULL,
  `current_version` varchar(20) NOT NULL,
  `min_required_version` varchar(20) NOT NULL,
  `is_maintenance` tinyint(1) DEFAULT 0,
  `maintenance_message` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `extension_settings`
--

INSERT INTO `extension_settings` (`id_setting`, `current_version`, `min_required_version`, `is_maintenance`, `maintenance_message`, `created_at`, `updated_at`) VALUES
('e6a00ec4-fec3-11ef-a027-42010ab80003', '1.1.0', '1.1.0', 0, NULL, '2025-03-11 21:58:01', '2025-03-12 22:27:18');

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

--
-- Table structure for table `packages`
--

CREATE TABLE `packages` (
  `id_package` varchar(36) NOT NULL,
  `name` varchar(100) NOT NULL,
  `price` decimal(10,2) NOT NULL,
  `duration` int(11) NOT NULL COMMENT 'Duration in days',
  `description` text DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `packages`
--

INSERT INTO `packages` (`id_package`, `name`, `price`, `duration`, `description`, `is_active`, `created_at`, `updated_at`) VALUES
('e83c57a1-fd38-11ef-a027-42010ab80003', 'Premium 1 bulan', 1000.00, 30, 'Mendapatkan akses premium 1 bulan', 1, '2025-03-09 22:50:32', '2025-03-11 10:06:00');

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

--
-- Table structure for table `popup_displays`
--

CREATE TABLE `popup_displays` (
  `id_display` varchar(36) NOT NULL,
  `id_popup` varchar(36) NOT NULL,
  `id_serenity` varchar(36) NOT NULL,
  `display_count` int(11) DEFAULT 0,
  `last_displayed` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `promo_codes`
--

CREATE TABLE `promo_codes` (
  `id_promo` varchar(36) NOT NULL,
  `promo_code` varchar(50) NOT NULL,
  `description` text DEFAULT NULL,
  `discount_type` enum('percentage','nominal') NOT NULL,
  `discount_value` decimal(10,2) NOT NULL,
  `min_purchase` decimal(10,2) DEFAULT 0.00,
  `max_discount` decimal(10,2) DEFAULT NULL,
  `starts_at` timestamp NULL DEFAULT current_timestamp(),
  `expires_at` timestamp NULL DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `max_uses` int(11) DEFAULT NULL,
  `used_count` int(11) DEFAULT 0,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `promo_codes`
--

INSERT INTO `promo_codes` (`id_promo`, `promo_code`, `description`, `discount_type`, `discount_value`, `min_purchase`, `max_discount`, `starts_at`, `expires_at`, `is_active`, `max_uses`, `used_count`, `created_at`, `updated_at`) VALUES
('7b1fc23b-fd62-11ef-a027-42010ab80003', 'SERENITY1616', NULL, 'percentage', 20.00, 0.00, 20000.00, '2025-03-10 03:48:08', '2025-03-20 10:46:00', 1, 5, 1, '2025-03-10 03:48:08', '2025-03-12 07:43:41');

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

--
-- Table structure for table `promo_package_relations`
--

CREATE TABLE `promo_package_relations` (
  `id_relation` varchar(36) NOT NULL,
  `id_promo` varchar(36) NOT NULL,
  `id_package` varchar(36) NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `promo_usage_history`
--

CREATE TABLE `promo_usage_history` (
  `id_usage` varchar(36) NOT NULL,
  `id_promo` varchar(36) NOT NULL,
  `id_serenity` varchar(36) NOT NULL,
  `transaction_id` varchar(36) NOT NULL,
  `discount_amount` decimal(10,2) NOT NULL,
  `used_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `transactions`
--

CREATE TABLE `transactions` (
  `transaction_id` varchar(36) NOT NULL,
  `id_serenity` varchar(36) NOT NULL,
  `id_package` varchar(36) NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `status` enum('pending','success','failed','expired') DEFAULT 'pending',
  `payment_method` varchar(50) DEFAULT NULL,
  `id_promo` varchar(36) DEFAULT NULL,
  `discount_amount` decimal(10,2) DEFAULT 0.00,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `qr_code` text DEFAULT NULL,
  `qris_id` varchar(100) DEFAULT NULL,
  `qris_status` varchar(50) DEFAULT NULL,
  `qris_expired_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `account_extension`
--
ALTER TABLE `account_extension`
  ADD PRIMARY KEY (`id_account`);

--
-- Indexes for table `account_number`
--
ALTER TABLE `account_number`
  ADD PRIMARY KEY (`id_number`),
  ADD KEY `idx_id_account` (`id_account`),
  ADD KEY `idx_is_active` (`is_active`);

--
-- Indexes for table `extension_files`
--
ALTER TABLE `extension_files`
  ADD PRIMARY KEY (`id_file`),
  ADD KEY `uploaded_by` (`uploaded_by`);

--
-- Indexes for table `extension_popups`
--
ALTER TABLE `extension_popups`
  ADD PRIMARY KEY (`id_popup`),
  ADD KEY `idx_is_active` (`is_active`),
  ADD KEY `idx_start_date` (`start_date`),
  ADD KEY `idx_end_date` (`end_date`);

--
-- Indexes for table `extension_settings`
--
ALTER TABLE `extension_settings`
  ADD PRIMARY KEY (`id_setting`);

--
-- Indexes for table `packages`
--
ALTER TABLE `packages`
  ADD PRIMARY KEY (`id_package`),
  ADD KEY `idx_is_active` (`is_active`);

--
-- Indexes for table `popup_displays`
--
ALTER TABLE `popup_displays`
  ADD PRIMARY KEY (`id_display`),
  ADD UNIQUE KEY `unique_popup_user` (`id_popup`,`id_serenity`),
  ADD KEY `idx_id_serenity` (`id_serenity`);

--
-- Indexes for table `promo_codes`
--
ALTER TABLE `promo_codes`
  ADD PRIMARY KEY (`id_promo`),
  ADD UNIQUE KEY `promo_code` (`promo_code`),
  ADD KEY `idx_promo_code` (`promo_code`),
  ADD KEY `idx_is_active` (`is_active`),
  ADD KEY `idx_expires_at` (`expires_at`);

--
-- Indexes for table `promo_package_relations`
--
ALTER TABLE `promo_package_relations`
  ADD PRIMARY KEY (`id_relation`),
  ADD UNIQUE KEY `unique_promo_package` (`id_promo`,`id_package`),
  ADD KEY `idx_id_package` (`id_package`);

--
-- Indexes for table `promo_usage_history`
--
ALTER TABLE `promo_usage_history`
  ADD PRIMARY KEY (`id_usage`),
  ADD KEY `id_promo` (`id_promo`),
  ADD KEY `transaction_id` (`transaction_id`),
  ADD KEY `idx_id_serenity` (`id_serenity`),
  ADD KEY `idx_used_at` (`used_at`);

--
-- Indexes for table `transactions`
--
ALTER TABLE `transactions`
  ADD PRIMARY KEY (`transaction_id`),
  ADD KEY `id_serenity` (`id_serenity`),
  ADD KEY `id_package` (`id_package`),
  ADD KEY `idx_status` (`status`),
  ADD KEY `idx_created_at` (`created_at`),
  ADD KEY `id_promo` (`id_promo`);

--
-- Constraints for dumped tables
--

--
-- Constraints for table `account_number`
--
ALTER TABLE `account_number`
  ADD CONSTRAINT `account_number_ibfk_1` FOREIGN KEY (`id_account`) REFERENCES `account_extension` (`id_account`) ON DELETE CASCADE;

--
-- Constraints for table `extension_files`
--
ALTER TABLE `extension_files`
  ADD CONSTRAINT `extension_files_ibfk_1` FOREIGN KEY (`uploaded_by`) REFERENCES `users` (`id_serenity`) ON DELETE CASCADE;

--
-- Constraints for table `popup_displays`
--
ALTER TABLE `popup_displays`
  ADD CONSTRAINT `popup_displays_ibfk_1` FOREIGN KEY (`id_popup`) REFERENCES `extension_popups` (`id_popup`) ON DELETE CASCADE,
  ADD CONSTRAINT `popup_displays_ibfk_2` FOREIGN KEY (`id_serenity`) REFERENCES `users` (`id_serenity`) ON DELETE CASCADE;

--
-- Constraints for table `promo_package_relations`
--
ALTER TABLE `promo_package_relations`
  ADD CONSTRAINT `promo_package_relations_ibfk_1` FOREIGN KEY (`id_promo`) REFERENCES `promo_codes` (`id_promo`) ON DELETE CASCADE,
  ADD CONSTRAINT `promo_package_relations_ibfk_2` FOREIGN KEY (`id_package`) REFERENCES `packages` (`id_package`) ON DELETE CASCADE;

--
-- Constraints for table `promo_usage_history`
--
ALTER TABLE `promo_usage_history`
  ADD CONSTRAINT `promo_usage_history_ibfk_1` FOREIGN KEY (`id_promo`) REFERENCES `promo_codes` (`id_promo`) ON DELETE CASCADE,
  ADD CONSTRAINT `promo_usage_history_ibfk_2` FOREIGN KEY (`id_serenity`) REFERENCES `users` (`id_serenity`) ON DELETE CASCADE,
  ADD CONSTRAINT `promo_usage_history_ibfk_3` FOREIGN KEY (`transaction_id`) REFERENCES `transactions` (`transaction_id`) ON DELETE CASCADE;

--
-- Constraints for table `transactions`
--
ALTER TABLE `transactions`
  ADD CONSTRAINT `transactions_ibfk_1` FOREIGN KEY (`id_serenity`) REFERENCES `users` (`id_serenity`) ON DELETE CASCADE,
  ADD CONSTRAINT `transactions_ibfk_2` FOREIGN KEY (`id_package`) REFERENCES `packages` (`id_package`) ON DELETE CASCADE,
  ADD CONSTRAINT `transactions_ibfk_3` FOREIGN KEY (`id_promo`) REFERENCES `promo_codes` (`id_promo`) ON DELETE SET NULL;
COMMIT;

