-- ============================================
-- LPG Vitrak Chayan - Small Website Database
-- Complete Database for Admin & User Panel
-- ============================================
-- Host: localhost
-- Database: lpgv650271_lpgwi
-- ============================================

-- ============================================
-- CREATE DATABASE
-- ============================================
CREATE DATABASE IF NOT EXISTS `lpgv650271_lpgwi`;
USE `lpgv650271_lpgwi`;

-- ============================================
-- TABLE: admin (Admin Login)
-- ============================================
CREATE TABLE `admin` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `password` varchar(255) NOT NULL,
  `full_name` varchar(100) DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  `phone` varchar(15) DEFAULT NULL,
  `role` enum('super_admin','admin') DEFAULT 'admin',
  `status` enum('Active','Inactive') DEFAULT 'Active',
  `last_login` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------
-- Insert default admin (username: admin, password: 12345)
-- --------------------------------------------------------
INSERT INTO `admin` (`username`, `password`, `full_name`, `email`, `phone`, `role`) VALUES
('admin', MD5('12345'), 'Super Admin', 'admin@lpgvitarakchayan.in', '9876543210', 'super_admin');

-- ============================================
-- TABLE: users (User Registration & Login)
-- ============================================
CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` varchar(20) DEFAULT NULL,
  `name` varchar(100) NOT NULL,
  `email` varchar(100) NOT NULL,
  `phone` varchar(15) NOT NULL,
  `password` varchar(255) NOT NULL,
  `address` text DEFAULT NULL,
  `city` varchar(50) DEFAULT NULL,
  `state` varchar(50) DEFAULT NULL,
  `pincode` varchar(10) DEFAULT NULL,
  `aadhar` varchar(20) DEFAULT NULL,
  `pan` varchar(20) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `gender` enum('Male','Female','Other') DEFAULT NULL,
  `payment_method` enum('Pending','UPI','Bank Transfer','Cash','Online') DEFAULT 'Pending',
  `payment_status` enum('Pending','Paid','Failed') DEFAULT 'Pending',
  `profile_pic` varchar(255) DEFAULT NULL,
  `status` enum('Active','Inactive','Suspended') DEFAULT 'Active',
  `email_verified` tinyint(1) DEFAULT 0,
  `phone_verified` tinyint(1) DEFAULT 0,
  `last_login` timestamp NULL DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`),
  UNIQUE KEY `phone` (`phone`),
  UNIQUE KEY `user_id` (`user_id`),
  UNIQUE KEY `aadhar` (`aadhar`),
  UNIQUE KEY `pan` (`pan`),
  KEY `status` (`status`),
  KEY `payment_status` (`payment_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ============================================
-- TABLE: messages (Admin to User Messages)
-- ============================================
CREATE TABLE `messages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `admin_id` int(11) DEFAULT NULL,
  `message` text NOT NULL,
  `type` enum('admin_to_user','user_to_admin') DEFAULT 'admin_to_user',
  `is_read` tinyint(1) DEFAULT 0,
  `read_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `admin_id` (`admin_id`),
  CONSTRAINT `messages_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `messages_ibfk_2` FOREIGN KEY (`admin_id`) REFERENCES `admin` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ============================================
-- TABLE: payment_history (Payment Records)
-- ============================================
CREATE TABLE `payment_history` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `payment_method` varchar(50) NOT NULL,
  `amount` decimal(10,2) DEFAULT NULL,
  `transaction_id` varchar(100) DEFAULT NULL,
  `payment_details` text DEFAULT NULL,
  `status` enum('Pending','Completed','Failed') DEFAULT 'Pending',
  `payment_date` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `status` (`status`),
  CONSTRAINT `payment_history_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ============================================
-- TABLE: activity_logs (User & Admin Activities)
-- ============================================
CREATE TABLE `activity_logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `admin_id` int(11) DEFAULT NULL,
  `action` varchar(255) NOT NULL,
  `details` text DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `admin_id` (`admin_id`),
  KEY `action` (`action`),
  CONSTRAINT `activity_logs_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `activity_logs_ibfk_2` FOREIGN KEY (`admin_id`) REFERENCES `admin` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ============================================
-- TABLE: user_sessions (Remember Me & Session Management)
-- ============================================
CREATE TABLE `user_sessions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `session_token` varchar(255) NOT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` text DEFAULT NULL,
  `expires_at` timestamp NOT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `session_token` (`session_token`),
  CONSTRAINT `user_sessions_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ============================================
-- TABLE: password_resets (Forgot Password)
-- ============================================
CREATE TABLE `password_resets` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `email` varchar(100) NOT NULL,
  `token` varchar(255) NOT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `expires_at` timestamp NOT NULL,
  `used` tinyint(1) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `email` (`email`),
  KEY `token` (`token`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ============================================
-- TABLE: settings (Website Settings)
-- ============================================
CREATE TABLE `settings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `setting_key` varchar(50) NOT NULL,
  `setting_value` text DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `setting_key` (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------
-- Insert default settings
-- --------------------------------------------------------
INSERT INTO `settings` (`setting_key`, `setting_value`, `description`) VALUES
('site_title', 'LPG Vitrak Chayan', 'Website Title'),
('site_description', 'LPG Distributorship Application System', 'Website Description'),
('admin_email', 'admin@lpgvitarakchayan.in', 'Admin Email'),
('site_status', 'active', 'Site Status (active/maintenance)'),
('registration_open', '1', 'User Registration (1=Open, 0=Closed)'),
('payment_gateway', 'pending', 'Payment Gateway Status'),
('contact_email', 'support@lpgvitarakchayan.in', 'Support Email');

-- ============================================
-- TABLE: notifications (User Notifications)
-- ============================================
CREATE TABLE `notifications` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `message` text NOT NULL,
  `type` enum('info','success','warning','error','payment','message') DEFAULT 'info',
  `is_read` tinyint(1) DEFAULT 0,
  `link` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `notifications_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ============================================
-- TABLE: feedback (User Feedback)
-- ============================================
CREATE TABLE `feedback` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `name` varchar(100) NOT NULL,
  `email` varchar(100) NOT NULL,
  `phone` varchar(15) DEFAULT NULL,
  `subject` varchar(200) DEFAULT NULL,
  `message` text NOT NULL,
  `rating` tinyint(1) DEFAULT NULL,
  `status` enum('Pending','Read','Replied') DEFAULT 'Pending',
  `reply` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `feedback_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ============================================
-- TABLE: login_attempts (Brute Force Protection)
-- ============================================
CREATE TABLE `login_attempts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ip_address` varchar(45) NOT NULL,
  `email` varchar(100) DEFAULT NULL,
  `attempts` int(11) DEFAULT 1,
  `last_attempt` timestamp NOT NULL DEFAULT current_timestamp(),
  `blocked_until` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ip_address` (`ip_address`),
  KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ============================================
-- SAMPLE DATA (For Testing)
-- ============================================

-- Sample Users
INSERT INTO `users` (`user_id`, `name`, `email`, `phone`, `password`, `address`, `city`, `state`, `pincode`, `aadhar`, `pan`, `dob`, `gender`, `payment_method`, `payment_status`, `status`, `email_verified`) VALUES
('USR20260001', 'Rajesh Kumar', 'rajesh@email.com', '9876543210', MD5('password123'), '123, Gandhi Nagar, Lucknow', 'Lucknow', 'Uttar Pradesh', '226001', '123456789012', 'ABCDE1234F', '1985-06-15', 'Male', 'UPI', 'Paid', 'Active', 1),
('USR20260002', 'Priya Sharma', 'priya@email.com', '9876543211', MD5('password123'), '456, Model Town, Delhi', 'Delhi', 'Delhi', '110009', '987654321098', 'FGHIJ5678K', '1990-03-22', 'Female', 'Bank Transfer', 'Pending', 'Active', 1),
('USR20260003', 'Amit Singh', 'amit@email.com', '9876543212', MD5('password123'), '789, Civil Lines, Jaipur', 'Jaipur', 'Rajasthan', '302001', '456789012345', 'KLMNO9012P', '1988-11-10', 'Male', 'Cash', 'Pending', 'Active', 0);

-- Sample Messages
INSERT INTO `messages` (`user_id`, `admin_id`, `message`, `type`, `is_read`) VALUES
(1, 1, 'Welcome to LPG Vitrak Chayan! Your application is being processed.', 'admin_to_user', 0),
(1, 1, 'Please submit your documents for verification.', 'admin_to_user', 0),
(2, 1, 'Your payment is pending. Please complete the payment process.', 'admin_to_user', 0);

-- Sample Payment History
INSERT INTO `payment_history` (`user_id`, `payment_method`, `amount`, `transaction_id`, `status`, `payment_date`) VALUES
(1, 'UPI', 500.00, 'TXN123456789', 'Completed', NOW()),
(2, 'Bank Transfer', 500.00, 'TXN987654321', 'Pending', NOW());

-- Sample Notifications
INSERT INTO `notifications` (`user_id`, `title`, `message`, `type`, `is_read`) VALUES
(1, 'Welcome!', 'Welcome to LPG Vitrak Chayan platform.', 'success', 0),
(1, 'Application Status', 'Your application is under review.', 'info', 0),
(2, 'Payment Reminder', 'Please complete your payment to proceed.', 'warning', 0);

-- Sample Activity Logs
INSERT INTO `activity_logs` (`user_id`, `admin_id`, `action`, `details`, `ip_address`) VALUES
(1, NULL, 'user_login', 'User logged in', '192.168.1.1'),
(NULL, 1, 'admin_login', 'Admin logged in', '192.168.1.1'),
(1, NULL, 'profile_update', 'User updated profile', '192.168.1.1'),
(NULL, 1, 'payment_update', 'Admin updated payment for user ID 1', '192.168.1.1');

-- ============================================
-- VIEWS (For Dashboard Statistics)
-- ============================================

-- User Statistics View
CREATE VIEW `user_stats` AS
SELECT 
  COUNT(*) as total_users,
  SUM(CASE WHEN status = 'Active' THEN 1 ELSE 0 END) as active_users,
  SUM(CASE WHEN status = 'Inactive' THEN 1 ELSE 0 END) as inactive_users,
  SUM(CASE WHEN payment_status = 'Paid' THEN 1 ELSE 0 END) as paid_users,
  SUM(CASE WHEN payment_status = 'Pending' THEN 1 ELSE 0 END) as pending_payment,
  SUM(CASE WHEN email_verified = 1 THEN 1 ELSE 0 END) as verified_users,
  DATE(created_at) as registration_date
FROM users
GROUP BY DATE(created_at);

-- Payment Statistics View
CREATE VIEW `payment_stats` AS
SELECT 
  payment_status,
  COUNT(*) as count,
  SUM(amount) as total_amount
FROM payment_history
GROUP BY payment_status;

-- Message Statistics View
CREATE VIEW `message_stats` AS
SELECT 
  COUNT(*) as total_messages,
  SUM(CASE WHEN is_read = 0 THEN 1 ELSE 0 END) as unread_messages,
  SUM(CASE WHEN type = 'admin_to_user' THEN 1 ELSE 0 END) as admin_messages
FROM messages;

-- ============================================
-- TRIGGERS
-- ============================================

-- Auto-generate User ID
DELIMITER //
CREATE TRIGGER before_user_insert
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
    IF NEW.user_id IS NULL THEN
        SET NEW.user_id = CONCAT('USR', DATE_FORMAT(NOW(), '%Y'), 
                               LPAD((SELECT COUNT(*) + 1 FROM users WHERE YEAR(created_at) = YEAR(NOW())), 5, '0'));
    END IF;
END//
DELIMITER ;

-- Auto-update last_login when user logs in
DELIMITER //
CREATE TRIGGER after_user_login
BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
    IF NEW.last_login IS NOT NULL AND OLD.last_login IS NULL THEN
        SET NEW.last_login = NOW();
    END IF;
END//
DELIMITER ;

-- Auto-clean expired sessions
DELIMITER //
CREATE EVENT clean_expired_sessions
ON SCHEDULE EVERY 1 HOUR
DO
BEGIN
    DELETE FROM user_sessions WHERE expires_at < NOW() OR is_active = 0;
END//
DELIMITER ;

-- Auto-clean expired password resets
DELIMITER //
CREATE EVENT clean_expired_resets
ON SCHEDULE EVERY 6 HOUR
DO
BEGIN
    DELETE FROM password_resets WHERE expires_at < NOW() OR used = 1;
END//
DELIMITER ;

-- ============================================
-- INDEXES (For Better Performance)
-- ============================================

CREATE INDEX idx_users_email_status ON users(email, status);
CREATE INDEX idx_users_phone_status ON users(phone, status);
CREATE INDEX idx_users_payment_status ON users(payment_status, status);
CREATE INDEX idx_messages_user_read ON messages(user_id, is_read);
CREATE INDEX idx_payments_user_status ON payment_history(user_id, status);
CREATE INDEX idx_activity_user_action ON activity_logs(user_id, action);
CREATE INDEX idx_notifications_user_read ON notifications(user_id, is_read);
CREATE INDEX idx_login_attempts_ip ON login_attempts(ip_address);

-- ============================================
-- FINAL CHECK
-- ============================================

SELECT '✅ Database Setup Complete!' as Status;
SELECT '📊 Tables Created: ' as Info, COUNT(*) as Count FROM information_schema.tables WHERE table_schema = 'lpgv650271_lpgwi';
SELECT '👤 Admin Created: ' as Info, username FROM admin;
SELECT '👥 Sample Users: ' as Info, COUNT(*) as Count FROM users;

-- ============================================
-- DONE!
-- ============================================