-- Welcome User App - MySQL Database Setup Script
-- Run this script to manually create the database structure
-- 
-- IMPORTANT: Replace 'welcomeapp' with your desired database name
-- This should match the DB_NAME in your .env file

-- Create database (change 'welcomeapp' to match your DB_NAME in .env)
CREATE DATABASE IF NOT EXISTS 	demosajjitechsys_welcome_db;
USE 	demosajjitechsys_welcome_db;

-- Alternative: Create with custom name
-- CREATE DATABASE IF NOT EXISTS your_custom_db_name;
-- USE your_custom_db_name;

-- Create users table
CREATE TABLE IF NOT EXISTS `users` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL COMMENT 'User full name (required)',
  `email` varchar(255) DEFAULT NULL COMMENT 'User email address (optional)',
  `visitCount` int NOT NULL DEFAULT '1' COMMENT 'Number of times user has visited',
  `lastVisit` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'When user last visited the app',
  `createdAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'When user record was created',
  `updatedAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'When user record was last updated',
  PRIMARY KEY (`id`),
  KEY `idx_users_name` (`name`) COMMENT 'Index for name searches',
  KEY `idx_users_email` (`email`) COMMENT 'Index for email lookups', 
  KEY `idx_users_lastVisit` (`lastVisit`) COMMENT 'Index for recent user queries',
  KEY `idx_users_createdAt` (`createdAt`) COMMENT 'Index for user creation date queries'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='Users table for Welcome User App';

-- Insert sample data (optional)
INSERT INTO `users` (`name`, `email`, `visitCount`, `lastVisit`) VALUES
('John Doe', 'john.doe@example.com', 1, NOW()),
('Jane Smith', 'jane.smith@example.com', 2, NOW() - INTERVAL 1 HOUR),
('Bob Wilson', NULL, 3, NOW() - INTERVAL 2 HOUR),
('Alice Johnson', 'alice@example.com', 1, NOW() - INTERVAL 1 DAY),
('Charlie Brown', 'charlie.brown@example.com', 5, NOW() - INTERVAL 30 MINUTE);

-- Verify table creation
DESCRIBE users;

-- Show sample data
SELECT * FROM users ORDER BY lastVisit DESC LIMIT 5;

-- Show table status
SHOW TABLE STATUS LIKE 'users';

PRINT 'Database setup completed successfully!';
PRINT 'Table: users created with sample data';
PRINT 'Ready to start the Node.js backend application';