TripApp/admin_schema_updates.sql at master · kv-248/TripApp · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
-- Admin functionality database updates for TripSync
USE tripsync;

-- Add admin role to User table
ALTER TABLE User ADD COLUMN role ENUM('user', 'admin') DEFAULT 'user';

-- Update Holiday_Package table to include admin management fields
ALTER TABLE Holiday_Package
ADD COLUMN description TEXT AFTER Package_Name,
ADD COLUMN duration_days INT AFTER Time_Period,
ADD COLUMN image_url VARCHAR(500) AFTER duration_days,
ADD COLUMN created_by INT AFTER image_url,
ADD COLUMN status ENUM('active', 'inactive') DEFAULT 'active' AFTER created_by,
ADD FOREIGN KEY (created_by) REFERENCES User(User_ID) ON DELETE SET NULL;

-- Create default admin user
INSERT INTO User (Name, Email, Phone_No, Age_DOB, Age_Year, Sex, Address_House_No, Address_City, Address_PIN, Password, role)
VALUES (
    'Admin User',
    'admin@tripsync.com',
    '+1234567999',
    '1985-01-01',
    1985,
    'Other',
    'Admin',
    'System',
    '00000',
    '$2a$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', -- password: "admin123"
    'admin'
);

-- Update existing packages with description and other fields
UPDATE Holiday_Package SET
    description = CASE
        WHEN Package_Name = 'NYC Weekend Getaway' THEN 'Experience the vibrant energy of New York City with this exciting weekend package. Includes accommodation at Grand Plaza Hotel and explore iconic landmarks like Times Square, Central Park, and the Statue of Liberty.'
        WHEN Package_Name = 'LA Beach Vacation' THEN 'Relax and unwind with this amazing Los Angeles beach vacation. Enjoy sunny beaches, Hollywood attractions, and luxurious accommodation at Sunset Resort.'
        WHEN Package_Name = 'Chicago City Break' THEN 'Discover the architectural wonders and cultural richness of Chicago. Perfect city break with comfortable accommodation and easy access to downtown attractions.'
        WHEN Package_Name = 'Miami Paradise Package' THEN 'Ultimate tropical getaway in Miami! Enjoy pristine beaches, vibrant nightlife, and luxury accommodation at Beach Paradise Resort.'
        WHEN Package_Name = 'Denver Mountain Adventure' THEN 'Adventure awaits in the beautiful Colorado mountains. Perfect for nature lovers with stunning mountain views and outdoor activities.'
        ELSE 'Amazing travel package with great accommodations and experiences.'
    END,
    duration_days = CASE
        WHEN Time_Period LIKE '%3 Days%' THEN 3
        WHEN Time_Period LIKE '%4 Days%' THEN 4
        WHEN Time_Period LIKE '%5 Days%' THEN 5
        WHEN Time_Period LIKE '%6 Days%' THEN 6
        WHEN Time_Period LIKE '%7 Days%' THEN 7
        ELSE 3
    END,
    image_url = CASE
        WHEN Package_Name = 'NYC Weekend Getaway' THEN 'https://images.unsplash.com/photo-1496442226666-8d4d0e62e6e9?w=500'
        WHEN Package_Name = 'LA Beach Vacation' THEN 'https://images.unsplash.com/photo-1580655653885-65763b2597d0?w=500'
        WHEN Package_Name = 'Chicago City Break' THEN 'https://images.unsplash.com/photo-1477414348463-c0eb7f1359b6?w=500'
        WHEN Package_Name = 'Miami Paradise Package' THEN 'https://images.unsplash.com/photo-1506905925346-21bda4d32df4?w=500'
        WHEN Package_Name = 'Denver Mountain Adventure' THEN 'https://images.unsplash.com/photo-1469474968028-56623f02e42e?w=500'
        ELSE 'https://images.unsplash.com/photo-1488646953014-85cb44e25828?w=500'
    END,
    created_by = (SELECT User_ID FROM User WHERE role = 'admin' LIMIT 1);

-- Create index for better performance
CREATE INDEX idx_user_role ON User(role);
CREATE INDEX idx_package_status ON Holiday_Package(status);
CREATE INDEX idx_package_created_by ON Holiday_Package(created_by);

COMMIT;