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

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

-- ============================================
-- 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: 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 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');

-- 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;
    END IF;
END//
DELIMITER ;
    END IF;
END//
DELIMITER ;
END//
DELIMITER ;
END//
DELIMITER ;
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';

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