-- Create a default author if not exists
INSERT INTO auth_user (username, email, password, is_superuser, is_staff, is_active, avatar, bio, created_at, first_name, last_name, date_joined)
SELECT 
    'travel_admin',
    'travel@example.com',
    'pbkdf2_sha256$600000$ucCGsrxXqKxV8RGYC9Vd$+0/JqHUulwYH6UQrHGn7esz6KQlQOGqzHshVyuAZ6GM=', -- password is 'travel123'
    1,
    1,
    1,
    'https://example.com/default-avatar.jpg',
    'Travel enthusiast and content creator',
    datetime('now'),
    'Travel',
    'Admin',
    datetime('now')
WHERE NOT EXISTS (
    SELECT 1 FROM auth_user WHERE username = 'travel_admin'
);

-- Create travel category
INSERT INTO api_category (name, description, created_at, updated_at)
VALUES (
    'Travel',
    'Explore amazing destinations and travel experiences around the world',
    datetime('now'),
    datetime('now')
);

-- Create travel-related tags
INSERT INTO api_tag (name, created_at, updated_at)
VALUES 
    ('Adventure', datetime('now'), datetime('now')),
    ('Culture', datetime('now'), datetime('now')),
    ('Food', datetime('now'), datetime('now')),
    ('Photography', datetime('now'), datetime('now')),
    ('Tips', datetime('now'), datetime('now')),
    ('Nature', datetime('now'), datetime('now')),
    ('City', datetime('now'), datetime('now')),
    ('Beach', datetime('now'), datetime('now')),
    ('Mountains', datetime('now'), datetime('now')),
    ('Budget Travel', datetime('now'), datetime('now'));

-- Create a temporary table for travel destinations
CREATE TEMPORARY TABLE temp_destinations (city TEXT);
INSERT INTO temp_destinations (city) VALUES 
    ('Paris'), ('Tokyo'), ('New York'), ('London'), ('Rome'),
    ('Barcelona'), ('Sydney'), ('Dubai'), ('Singapore'), ('Bangkok'),
    ('Amsterdam'), ('Venice'), ('San Francisco'), ('Hong Kong'), ('Istanbul'),
    ('Prague'), ('Rio de Janeiro'), ('Vienna'), ('Seoul'), ('Marrakech');

-- Create a temporary table for activities
CREATE TEMPORARY TABLE temp_activities (activity TEXT);
INSERT INTO temp_activities (activity) VALUES 
    ('exploring'), ('discovering'), ('wandering through'), ('experiencing'),
    ('traveling around'), ('visiting'), ('adventuring in'), ('touring');

-- Generate 100 travel posts
WITH RECURSIVE
  cnt(x) AS (
    SELECT 1
    UNION ALL
    SELECT x+1 FROM cnt
    LIMIT 100
  )
INSERT INTO api_post (
    title,
    content,
    summary,
    author_id,
    category_id,
    created_at,
    updated_at,
    is_deleted
)
SELECT 
    CASE (x % 5)
        WHEN 0 THEN 'Ultimate Travel Guide: ' || city
        WHEN 1 THEN 'A Weekend in ' || city
        WHEN 2 THEN 'Hidden Gems of ' || city
        WHEN 3 THEN activity || ' ' || city
        WHEN 4 THEN 'Top 10 Must-See Places in ' || city
    END AS title,
    'Welcome to ' || city || char(10) || char(10) ||
    'Experience the magic of this incredible destination. From its stunning landmarks to its hidden corners, ' || city || ' offers something for every traveler.' || char(10) || char(10) ||
    'Key Highlights:' || char(10) ||
    '1. Cultural Heritage' || char(10) ||
    '2. Local Cuisine' || char(10) ||
    '3. Architecture' || char(10) ||
    '4. Local Life' || char(10) ||
    '5. Entertainment' || char(10) || char(10) ||
    'Tips for Visitors:' || char(10) ||
    '- Best time to visit: Spring and Fall' || char(10) ||
    '- Local transportation options' || char(10) ||
    '- Recommended accommodations' || char(10) ||
    '- Must-try local dishes' || char(10) || char(10) ||
    'Whether you''re a first-time visitor or a seasoned traveler, ' || city || ' will captivate you with its unique charm and endless possibilities for adventure.' AS content,
    'Discover the best of ' || city || ' with our comprehensive guide. From cultural highlights to local secrets, plan your perfect trip with these essential tips and recommendations.' AS summary,
    (SELECT id FROM auth_user WHERE username = 'travel_admin' LIMIT 1),
    (SELECT id FROM api_category WHERE name = 'Travel' LIMIT 1),
    datetime('now', '-' || x || ' days'),
    datetime('now', '-' || x || ' days'),
    0
FROM cnt
CROSS JOIN temp_destinations
CROSS JOIN temp_activities
ORDER BY random()
LIMIT 100;

-- Add tags to posts (approximately 3 tags per post)
INSERT INTO api_post_tags (post_id, tag_id)
SELECT 
    p.id,
    t.id
FROM api_post p
CROSS JOIN api_tag t
WHERE p.category_id = (SELECT id FROM api_category WHERE name = 'Travel')
ORDER BY random()
LIMIT (SELECT COUNT(*) FROM api_post) * 3;

-- Clean up temporary tables
DROP TABLE temp_destinations;
DROP TABLE temp_activities;
