-- =====================================================
-- Veer App - Business Networking Database Schema
-- =====================================================

CREATE DATABASE IF NOT EXISTS `veer_app` 
DEFAULT CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

USE `veer_app`;

-- =====================================================
-- Users Table
-- =====================================================
CREATE TABLE IF NOT EXISTS `users` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(255) NOT NULL,
    `email` VARCHAR(255) UNIQUE NOT NULL,
    `password` VARCHAR(255) NOT NULL,
    `phone` VARCHAR(20),
    `profile_photo` VARCHAR(500) DEFAULT NULL,
    `business_category` VARCHAR(255),
    `company_name` VARCHAR(255),
    `group_name` VARCHAR(255) DEFAULT 'Veer Business Chapter',
    `membership_status` ENUM('Active', 'Inactive', 'Expired') DEFAULT 'Active',
    `membership_due_date` DATE,
    `tyfcb_count` INT DEFAULT 0,
    `referrals_given` INT DEFAULT 0,
    `referrals_received` INT DEFAULT 0,
    `ceus_count` INT DEFAULT 0,
    `one_to_one_count` INT DEFAULT 0,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- Slips Table (Activity Feed)
-- =====================================================
CREATE TABLE IF NOT EXISTS `slips` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `from_user_id` INT NOT NULL,
    `to_user_id` INT NOT NULL,
    `type` ENUM('referral', 'tyfcb', 'ceu', 'one_to_one') NOT NULL,
    `description` TEXT,
    `amount` VARCHAR(50),
    `slip_date` DATE,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`from_user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`to_user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- Members (Chapter Roster)
-- =====================================================
CREATE TABLE IF NOT EXISTS `members` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    `profile_photo` VARCHAR(500) DEFAULT NULL,
    `business_category` VARCHAR(255),
    `company_name` VARCHAR(255),
    `phone` VARCHAR(20),
    `email` VARCHAR(255),
    `is_connected` TINYINT(1) DEFAULT 0,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- Events Table
-- =====================================================
CREATE TABLE IF NOT EXISTS `events` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `title` VARCHAR(255) NOT NULL,
    `description` TEXT,
    `location` VARCHAR(500),
    `event_date` DATETIME,
    `event_time` VARCHAR(50),
    `image_url` VARCHAR(500) DEFAULT NULL,
    `attendees` INT DEFAULT 0,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- Posts Table (Community Feed)
-- =====================================================
CREATE TABLE IF NOT EXISTS `posts` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT NOT NULL,
    `content` TEXT NOT NULL,
    `image_url` VARCHAR(500) DEFAULT NULL,
    `likes` INT DEFAULT 0,
    `comments_count` INT DEFAULT 0,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- Testimonials Table
-- =====================================================
CREATE TABLE IF NOT EXISTS `testimonials` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `from_user_id` INT NOT NULL,
    `to_user_id` INT NOT NULL,
    `content` TEXT NOT NULL,
    `rating` DECIMAL(2,1) DEFAULT 5.0,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`from_user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`to_user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- Visitors Table
-- =====================================================
CREATE TABLE IF NOT EXISTS `visitors` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `invited_by` INT NOT NULL,
    `visitor_name` VARCHAR(255) NOT NULL,
    `visitor_email` VARCHAR(255),
    `visitor_phone` VARCHAR(20),
    `business_category` VARCHAR(255),
    `invite_method` ENUM('email', 'sms', 'share') DEFAULT 'email',
    `visit_date` DATE,
    `status` ENUM('Pending', 'Attended', 'Cancelled') DEFAULT 'Pending',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`invited_by`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- Meetings Table
-- =====================================================
CREATE TABLE IF NOT EXISTS `meetings` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `title` VARCHAR(255) DEFAULT 'Weekly Chapter Meeting',
    `meeting_date` DATE NOT NULL,
    `meeting_type` ENUM('In-Person', 'Online', 'Hybrid') DEFAULT 'In-Person',
    `tyfcb_amount` DECIMAL(12,2) DEFAULT 0,
    `speakers` INT DEFAULT 0,
    `visitors_count` INT DEFAULT 0,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- Post Likes Table
-- =====================================================
CREATE TABLE IF NOT EXISTS `post_likes` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `post_id` INT NOT NULL,
    `user_id` INT NOT NULL,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`post_id`) REFERENCES `posts`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    UNIQUE KEY `unique_like` (`post_id`, `user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- Connections Table
-- =====================================================
CREATE TABLE IF NOT EXISTS `connections` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT NOT NULL,
    `connected_user_id` INT NOT NULL,
    `status` ENUM('pending', 'accepted', 'rejected') DEFAULT 'pending',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`connected_user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    UNIQUE KEY `unique_connection` (`user_id`, `connected_user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- =====================================================
-- SEED DATA
-- =====================================================

-- Insert Users
INSERT INTO `users` (`name`, `email`, `password`, `phone`, `business_category`, `company_name`, `group_name`, `membership_status`, `membership_due_date`, `tyfcb_count`, `referrals_given`, `referrals_received`, `ceus_count`, `one_to_one_count`) VALUES
('Nitin Kumar', 'nitin@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', '+91 98765 43210', 'IT Services', 'TechVentures Pvt Ltd', 'Velocity', 'Active', '2026-06-01', 12, 61, 29, 8, 59),
('Akash Pasricha', 'akash@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', '+91 98765 11111', 'Diamonds', 'Shree Diamond Mart', 'Velocity', 'Active', '2026-08-15', 8, 45, 22, 6, 38),
('Akhil Gupta', 'akhil@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', '+91 98765 22222', 'Finance & Insurance (Other)', 'JMK SERVICES AND SOLUTIONS', 'Velocity', 'Active', '2026-09-20', 5, 32, 18, 4, 27),
('Anurag Lamba', 'anurag@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', '+91 98765 33333', 'Mutual Funds', 'Krishna Financial Services', 'Velocity', 'Active', '2026-07-10', 10, 55, 30, 7, 42),
('Dharam Bir Ajmani', 'dharam@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', '+91 98765 44444', 'Retail (Other)', 'SUBHASH HARDWARE AND SANITARY STORE', 'Velocity', 'Active', '2026-05-25', 3, 28, 15, 5, 33),
('Geeta Prasad Verma', 'geeta@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', '+91 98765 55555', 'Manufacturing (Other)', 'Crystal Oil & Spices', 'Velocity', 'Active', '2026-11-30', 7, 40, 25, 9, 51),
('Gurvinder Singh Chawla', 'gurvinder@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', '+91 98765 66666', 'Construction (Other)', 'UNITED POWER SOLUTION', 'Velocity', 'Active', '2026-10-05', 6, 35, 20, 3, 29),
('Jatin Nanda', 'jatin@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', '+91 98765 77777', 'Corporate Law', 'TRADEWELL ADVISORY SERVICES', 'Velocity', 'Active', '2026-12-15', 9, 48, 28, 6, 44),
('Prashant Sharma', 'prashant@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', '+91 98765 88888', 'Marketing', 'DigiMark Solutions', 'Velocity', 'Active', '2026-04-20', 4, 22, 12, 5, 25),
('Surender Jain', 'surender@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', '+91 98765 99999', 'Healthcare', 'HealthFirst Clinic', 'Velocity', 'Active', '2026-08-01', 11, 50, 35, 8, 55);

-- Insert Slips (Activity Feed Data matching screenshots)
INSERT INTO `slips` (`from_user_id`, `to_user_id`, `type`, `description`, `amount`, `slip_date`) VALUES
(1, 2, 'referral', 'Diamond jewelry referral for wedding client', '₹2,50,000', '2026-01-24'),
(1, 3, 'tyfcb', 'Thank you for insurance referral', '₹30,000', '2026-01-22'),
(9, 1, 'referral', 'Prashant Sharma - January 16 2026', '₹50,000', '2026-01-16'),
(10, 1, 'referral', 'Surender Jain - January 16 2026', '₹75,000', '2026-01-16'),
(4, 5, 'ceu', 'Financial planning seminar presentation', NULL, '2026-01-13'),
(6, 1, 'tyfcb', 'Thank you for spices distribution contact', '₹1,20,000', '2026-01-13'),
(2, 4, 'referral', 'Mutual fund client referral', '₹15,000', '2026-01-10'),
(3, 6, 'one_to_one', 'One-to-one meeting discussion', NULL, '2026-01-08'),
(7, 8, 'referral', 'Legal advisory for construction project', '₹85,000', '2026-01-05'),
(1, 9, 'tyfcb', 'Marketing campaign thank you', '₹45,000', '2026-01-03');

-- Insert Events
INSERT INTO `events` (`title`, `description`, `location`, `event_date`, `event_time`, `attendees`) VALUES
('Weekly Chapter Meeting', 'Regular weekly meeting with business updates and referral exchange', 'Hotel Grand, Conference Hall', '2026-03-26 09:00:00', '9:00 AM - 11:00 AM', 32),
('Business Networking Mixer', 'Open networking event for all chapters. Great opportunity to expand your network', 'Convention Center, Hall B', '2026-03-30 18:00:00', '6:00 PM - 9:00 PM', 85),
('Leadership Training', 'Special training session on business leadership and team management', 'Training Center, Room 201', '2026-04-06 10:00:00', '10:00 AM - 4:00 PM', 45);

-- Insert Posts (Community Feed)
INSERT INTO `posts` (`user_id`, `content`, `likes`, `comments_count`, `created_at`) VALUES
(2, 'Great meeting today! Got 3 solid referrals. 💪 Thank you team for the amazing support!', 12, 5, NOW() - INTERVAL 2 HOUR),
(4, 'Closed a deal worth ₹5L today, all thanks to a referral from Gurvinder! This is the power of networking. 🤝', 24, 8, NOW() - INTERVAL 5 HOUR),
(7, 'Reminder: Don''t forget to bring your business cards to tomorrow''s meeting. We have 5 visitors coming! 📋', 8, 3, NOW() - INTERVAL 8 HOUR),
(6, 'Just launched our new organic spices range. Special discount for Velocity members - 20% off on all products! 🌿', 18, 12, NOW() - INTERVAL 1 DAY);

-- Insert Testimonials
INSERT INTO `testimonials` (`from_user_id`, `to_user_id`, `content`, `rating`, `created_at`) VALUES
(2, 1, 'Nitin provided excellent IT solutions for our diamond mart''s inventory management. Highly recommended!', 5.0, NOW() - INTERVAL 10 DAY),
(4, 1, 'Outstanding financial advisory and great communication. The software delivered exceeded our expectations!', 4.5, NOW() - INTERVAL 20 DAY),
(7, 1, 'Professional, punctual, and delivers quality work. A reliable business partner for construction tech.', 5.0, NOW() - INTERVAL 30 DAY);

-- Insert Visitors
INSERT INTO `visitors` (`invited_by`, `visitor_name`, `visitor_email`, `visitor_phone`, `business_category`, `invite_method`, `visit_date`, `status`) VALUES
(1, 'Rohit Malhotra', 'rohit@example.com', '+91 99876 11111', 'Finance', 'email', '2026-03-20', 'Attended'),
(1, 'Ananya Mehta', 'ananya@example.com', '+91 99876 22222', 'Healthcare', 'sms', '2026-03-18', 'Attended'),
(1, 'Karan Chopra', 'karan@example.com', '+91 99876 33333', 'Education', 'share', '2026-03-15', 'Pending');

-- Insert Next Meeting
INSERT INTO `meetings` (`title`, `meeting_date`, `meeting_type`, `tyfcb_amount`, `speakers`, `visitors_count`) VALUES
('Weekly Chapter Meeting', '2026-03-19', 'In-Person', 0, 0, 1);

-- Insert Connections
INSERT INTO `connections` (`user_id`, `connected_user_id`, `status`) VALUES
(1, 2, 'accepted'),
(1, 4, 'accepted'),
(1, 5, 'accepted'),
(1, 7, 'accepted'),
(2, 4, 'accepted'),
(3, 6, 'pending');