-- phpMyAdmin SQL Dump
-- version 5.2.0
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Jun 08, 2025 at 09:04 PM
-- Server version: 10.4.27-MariaDB
-- PHP Version: 7.4.33

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: `expiry_reminders`
--

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

--
-- Table structure for table `items`
--

CREATE TABLE `items` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL COMMENT 'Foreign key to users table',
  `name` varchar(255) NOT NULL COMMENT 'Name of the item (domain, hosting plan, etc.)',
  `type` enum('domain','hosting','general') NOT NULL COMMENT 'Type of item being tracked',
  `expiry_date` date NOT NULL COMMENT 'Date when the item expires',
  `owner_email` varchar(255) NOT NULL COMMENT 'Email address to send reminders to',
  `one_month_reminder_sent` tinyint(1) DEFAULT 0 COMMENT 'Flag to track if one-month reminder was sent',
  `last_daily_reminder_sent_on` date DEFAULT NULL COMMENT 'Date when last daily reminder was sent',
  `created_at` timestamp NULL DEFAULT current_timestamp() COMMENT 'When the record was created'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Table to store items with expiry dates for reminder system';

--
-- Dumping data for table `items`
--

INSERT INTO `items` (`id`, `user_id`, `name`, `type`, `expiry_date`, `owner_email`, `one_month_reminder_sent`, `last_daily_reminder_sent_on`, `created_at`) VALUES
(7, 1, 'MyCompany.com Domain', 'domain', '2025-08-15', 'dastgirpgc317@gmail.com', 0, NULL, '2025-06-08 18:15:30'),
(8, 1, 'Premium Web Hosting', 'hosting', '2025-07-20', 'dastgirpgc317@gmail.com', 0, NULL, '2025-06-08 18:15:30'),
(9, 1, 'SSL Certificate - Wildcard', 'general', '2025-09-10', 'dastgirpgc317@gmail.com', 0, NULL, '2025-06-08 18:15:30'),
(11, 1, 'Software License - Adobe Creative Suite', 'general', '2025-12-31', 'dastgirpgc317@gmail.com', 0, NULL, '2025-06-08 18:15:30'),
(12, 1, 'ClientPortal.net Domain', 'domain', '2025-11-05', 'dastgirpgc317@gmail.com', 0, NULL, '2025-06-08 18:15:30'),
(13, 1, 'Cloud Storage Subscription', 'general', '2025-08-30', 'dastgirpgc317@gmail.com', 0, NULL, '2025-06-08 18:15:30'),
(14, 1, 'VPS Server Hosting', 'hosting', '2025-10-15', 'dastgirpgc317@gmail.com', 0, NULL, '2025-06-08 18:15:30'),
(15, 1, 'ProjectManager.org Domain', 'domain', '2025-07-08', 'dastgirpgc317@gmail.com', 0, NULL, '2025-06-08 18:15:30'),
(16, 1, 'API Service Subscription', 'general', '2025-09-22', 'dastgirpgc317@gmail.com', 0, NULL, '2025-06-08 18:15:30');

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

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

CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  `username` varchar(50) NOT NULL,
  `email` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL COMMENT 'Hashed password',
  `full_name` varchar(100) NOT NULL,
  `role` enum('user','admin','super_admin') DEFAULT 'user' COMMENT 'User role: user, admin, super_admin',
  `is_active` tinyint(1) DEFAULT 1 COMMENT '1 = active, 0 = inactive',
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `last_login` timestamp NULL DEFAULT NULL,
  `is_approved` tinyint(1) DEFAULT 1 COMMENT 'User approval status',
  `approved_by` int(11) DEFAULT NULL COMMENT 'ID of admin who approved',
  `approved_at` timestamp NULL DEFAULT NULL COMMENT 'When user was approved'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='User accounts for the expiry reminder system';

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

INSERT INTO `users` (`id`, `username`, `email`, `password`, `full_name`, `role`, `is_active`, `created_at`, `last_login`, `is_approved`, `approved_by`, `approved_at`) VALUES
(1, 'dastgirsabri', 'dastgirpgc317@gmail.com', '$2y$10$fYNS80h.giTa3qf7j/L7Z.NsG4f4gZI5Z8TTA0aI830inC9y4BDN.', 'Dastgir Sabri', 'super_admin', 1, '2025-06-05 17:57:50', '2025-06-06 18:50:08', 1, NULL, NULL),
(6, 'admin', 'admin@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'System Administrator', 'user', 0, '2025-06-05 19:46:13', NULL, 1, NULL, NULL);

--
-- Indexes for dumped tables
--

--
-- Indexes for table `items`
--
ALTER TABLE `items`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_user_id` (`user_id`),
  ADD KEY `idx_expiry_date` (`expiry_date`),
  ADD KEY `idx_owner_email` (`owner_email`),
  ADD KEY `idx_type` (`type`);

--
-- Indexes for table `users`
--
ALTER TABLE `users`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `username` (`username`),
  ADD UNIQUE KEY `email` (`email`),
  ADD KEY `idx_username` (`username`),
  ADD KEY `idx_email` (`email`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `items`
--
ALTER TABLE `items`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=17;

--
-- AUTO_INCREMENT for table `users`
--
ALTER TABLE `users`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;

--
-- Constraints for dumped tables
--

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

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