migration_script.sql

-- ============================================================
-- CampusMate VIT - Supabase Migration Script
-- Run this in: Supabase Dashboard -> SQL Editor -> New Query
-- ============================================================


-- ============================================================
-- STEP 1: CREATE TABLES (if they don't already exist)
-- ============================================================

CREATE TABLE IF NOT EXISTS public.locations (
    id          text PRIMARY KEY,
    name        text        NOT NULL,
    category    text        NOT NULL,
    description text        NOT NULL,
    image_path  text        NOT NULL,
    video_path  text        NOT NULL,
    latitude    float8      NOT NULL,
    longitude   float8      NOT NULL,
    features    text[]      NOT NULL DEFAULT '{}',
    voiceover_text text     NOT NULL,
    is_available boolean    NOT NULL DEFAULT true,
    user_id     text,
    ar_model_path text,
    gender_type text,
    created_at  timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS public.hostel_rooms (
    id          text PRIMARY KEY,
    location_id text        NOT NULL REFERENCES public.locations(id) ON DELETE CASCADE,
    room_type   text        NOT NULL,
    photo_urls  text[]      NOT NULL DEFAULT '{}',
    features    text[]      NOT NULL DEFAULT '{}',
    pano_url    text        NOT NULL DEFAULT '',
    created_at  timestamptz NOT NULL DEFAULT now()
);

-- Indexes for faster lookups
CREATE INDEX IF NOT EXISTS idx_locations_category    ON public.locations(category);
CREATE INDEX IF NOT EXISTS idx_locations_gender_type ON public.locations(gender_type);
CREATE INDEX IF NOT EXISTS idx_hostel_rooms_location ON public.hostel_rooms(location_id);


-- ============================================================
-- STEP 2: INSERT / UPSERT ALL LOCATIONS
-- (ON CONFLICT DO UPDATE ensures re-runs are safe)
-- ============================================================

INSERT INTO public.locations
    (id, name, category, description, image_path, video_path, latitude, longitude, features, voiceover_text, is_available, gender_type)
VALUES

-- E Block (Mens Hostel)
(
    '1',
    'E Block',
    'Hostel',
    'Modern hostel facility for male students...',
    'assets/images/Boys Hostel A.jpg',
    'https://www.example.com/videos/hostel_tour.mp4',
    12.972907522318591,
    79.15971575083208,
    ARRAY['24/7 Security', 'WiFi', 'Common Room', 'Laundry', 'Mess Facility'],
    'Boys Hostel A provides a comfortable home...',
    true,
    'Mens'
),

-- Suu Kyi Block (Ladies Hostel)
(
    '2',
    'Suu Kyi Block',
    'Hostel',
    'Secure and comfortable accommodation for female students...',
    'assets/images/Girls Hostel.jpg',
    'https://www.example.com/videos/hostel_tour.mp4',
    12.971017948339103,
    79.16249753312404,
    ARRAY['Enhanced Security', 'Study Rooms', 'Recreation Area', 'WiFi', 'Mess Facility'],
    'Girls Hostel B provides a safe and nurturing environment...',
    true,
    'Ladies'
),

-- MB - Main Building
(
    '11',
    'MB - Main Building (Dr. MGR Block)',
    'Academic Block',
    'The main academic building housing multiple departments including Computer Science, Electronics, and Mathematics. Features modern classrooms, lecture halls, and faculty offices.',
    'assets/images/Main Academic Block (1).jpg',
    'https://example.com/videos/main_building_tour.mp4',
    12.9705,
    79.1565,
    ARRAY['Classrooms', 'Faculty Offices', 'Administrative'],
    'Explore the Main Building.',
    true,
    'Other'
),

-- MG - Main Gate
(
    '10',
    'MG - Main Gate',
    'Other',
    'The primary entrance to the VIT University campus.',
    'assets/images/maingate.jpg',
    'https://example.com/videos/main_gate_tour.mp4',
    12.9699,
    79.1559,
    ARRAY['Security Checkpoint', 'Visitor Information'],
    'Explore the Main Gate.',
    true,
    'Other'
),

-- Central Library
(
    '12',
    'LIBRARY - Central Library (Periyar EVR Central Library)',
    'Library',
    'The central library, offering a vast collection of academic resources.',
    'assets/images/periyar.jpg',
    'https://example.com/videos/central_library_tour.mp4',
    12.9710,
    79.1580,
    ARRAY['100K+ Digital Resources', 'Reading Rooms', 'Research Sections'],
    'Explore the Central Library.',
    true,
    'Other'
),

-- Health Centre
(
    '15',
    'HC - Health Centre (A L Mudhaliar Block)',
    'Other',
    'The campus health center, providing medical services.',
    'assets/images/health.jpg',
    'https://example.com/videos/health_centre_tour.mp4',
    12.9720,
    79.1545,
    ARRAY['Medical Services', 'First Aid'],
    'Explore the Health Centre.',
    true,
    'Other'
),

-- Main Canteen
(
    '17',
    'MC - Main Canteen',
    'Cafeteria',
    'The main canteen offering a variety of food options.',
    'assets/images/maincanteen.jpg',
    'https://example.com/videos/main_canteen_tour.mp4',
    12.9730,
    79.1555,
    ARRAY['Dining Area', 'Food Stalls'],
    'Explore the Main Canteen.',
    true,
    'Other'
),

-- Anna Auditorium
(
    '19',
    'AUDI - Auditorium (Anna Auditorium)',
    'Other',
    'A large auditorium for events and performances.',
    'assets/images/anna.jpg',
    'https://example.com/videos/anna_auditorium_tour.mp4',
    12.9740,
    79.1570,
    ARRAY['Large Seating Capacity', 'Stage', 'Sound System'],
    'Explore the Anna Auditorium.',
    true,
    'Other'
),

-- Food Court
(
    '20',
    'FC - Food Court',
    'Cafeteria',
    'A dedicated area with multiple food vendors.',
    'assets/images/food court.jpg',
    'https://example.com/videos/food_court_tour.mp4',
    12.9745,
    79.1585,
    ARRAY['Variety of Cuisines', 'Seating Area'],
    'Explore the Food Court.',
    true,
    'Other'
),

-- Silver Jubilee Tower
(
    '22',
    'SJT - Silver Jubilee Tower',
    'Academic Block',
    'A prominent academic tower, celebrating the silver jubilee.',
    'assets/images/sjt.jpg',
    'https://example.com/videos/sjt_tower_tour.mp4',
    12.9665,
    79.1615,
    ARRAY['Classrooms', 'Labs', 'Departments'],
    'Explore the Silver Jubilee Tower.',
    true,
    'Other'
),

-- VIT Lake
(
    '24',
    'LAKE - VIT Lake',
    'Other',
    'A serene lake within the campus, offering a peaceful environment.',
    'assets/images/lake.jpg',
    'https://example.com/videos/vit_lake_tour.mp4',
    12.9680,
    79.1565,
    ARRAY['Scenic Views', 'Relaxation Spot'],
    'Explore the VIT Lake.',
    true,
    'Other'
),

-- ---- Ladies Hostels ----

-- LH-A Block - Indira Gandhi Block
(
    '30',
    'LH-A Block - Ladies Hostel (Indira Gandhi Block)',
    'Hostel',
    'Ladies Hostel A Block, named after Indira Gandhi.',
    'assets/images/indria.jpg',
    'https://example.com/videos/ladies_hostel_a_tour.mp4',
    12.9690,
    79.1550,
    ARRAY['Accommodation', 'Common Rooms', 'Mess'],
    'Explore Ladies Hostel A Block.',
    true,
    'Ladies'
),

-- LH-B Block - Kalpana Chawla Block
(
    '31',
    'LH-B Block - Ladies Hostel (Kalpana Chawla Block)',
    'Hostel',
    'Ladies Hostel B Block, named after Kalpana Chawla.',
    'assets/images/Girls Hostel.jpg',
    'https://example.com/videos/ladies_hostel_b_tour.mp4',
    12.9695,
    79.1555,
    ARRAY['Accommodation', 'Common Rooms', 'Mess'],
    'Explore Ladies Hostel B Block.',
    true,
    'Ladies'
),

-- LH-D Block - Jhansi Rani Block
(
    '33',
    'LH-D Block - Ladies Hostel (Jhansi Rani Block)',
    'Hostel',
    'Ladies Hostel D Block, named after Jhansi Rani.',
    'assets/images/Girls Hostel.jpg',
    'https://example.com/videos/ladies_hostel_d_tour.mp4',
    12.9680,
    79.1600,
    ARRAY['Accommodation', 'Common Rooms', 'Mess'],
    'Explore Ladies Hostel D Block.',
    true,
    'Ladies'
),

-- LH-E Block - Dr. Ida Scudder Block
(
    '34',
    'LH-E Block - Ladies Hostel (Dr. Ida Scudder Block)',
    'Hostel',
    'Ladies Hostel E Block, named after Dr. Ida Scudder.',
    'assets/images/ida.jpg',
    'https://example.com/videos/ladies_hostel_e_tour.mp4',
    12.9675,
    79.1605,
    ARRAY['Accommodation', 'Common Rooms', 'Mess'],
    'Explore Ladies Hostel E Block.',
    true,
    'Ladies'
),

-- ---- Mens Hostels ----

-- MH-A Block - Albert Einstein Block
(
    '40',
    'MH-A Block - Mens Hostel (Albert Einstein Block)',
    'Hostel',
    'Mens Hostel A Block, named after Albert Einstein.',
    'assets/images/Boys Hostel A.jpg',
    'https://example.com/videos/mens_hostel_a_tour.mp4',
    12.9720,
    79.1525,
    ARRAY['Accommodation', 'Common Rooms', 'Mess'],
    'Explore Mens Hostel A Block.',
    true,
    'Mens'
),

-- MH-B Block - Swami Vivekananda Block
(
    '41',
    'MH-B Block - Mens Hostel (Swami Vivekananda Block)',
    'Hostel',
    'Mens Hostel B Block, named after Swami Vivekananda.',
    'assets/images/Boys Hostel A.jpg',
    'https://example.com/videos/mens_hostel_b_tour.mp4',
    12.9745,
    79.1500,
    ARRAY['Accommodation', 'Common Rooms', 'Mess'],
    'Explore Mens Hostel B Block.',
    true,
    'Mens'
),

-- MH-C Block - Rabindranath Tagore Block
(
    '42',
    'MH-C Block - Mens Hostel (Rabindranath Tagore Block)',
    'Hostel',
    'Mens Hostel C Block, named after Rabindranath Tagore.',
    'assets/images/Boys Hostel A.jpg',
    'https://example.com/videos/mens_hostel_c_tour.mp4',
    12.9725,
    79.1520,
    ARRAY['Accommodation', 'Common Rooms', 'Mess'],
    'Explore Mens Hostel C Block.',
    true,
    'Mens'
),

-- MH-G Block - Socrates Block
(
    '46',
    'MH-G Block - Mens Hostel (Socrates Block)',
    'Hostel',
    'Mens Hostel G Block, named after Socrates.',
    'assets/images/Boys Hostel A.jpg',
    'https://example.com/videos/mens_hostel_g_tour.mp4',
    12.9740,
    79.1505,
    ARRAY['Accommodation', 'Common Rooms', 'Mess'],
    'Explore Mens Hostel G Block.',
    true,
    'Mens'
),

-- MH-H Block - John F Kennedy Block
(
    '47',
    'MH-H Block - Mens Hostel (John F Kennedy Block)',
    'Hostel',
    'Mens Hostel H Block, named after John F Kennedy.',
    'assets/images/j.jpg',
    'https://example.com/videos/mens_hostel_h_tour.mp4',
    12.9710,
    79.1530,
    ARRAY['Accommodation', 'Common Rooms', 'Mess'],
    'Explore Mens Hostel H Block.',
    true,
    'Mens'
),

-- MH-J Block - John F Kennedy Block
(
    '48',
    'MH-J Block - Mens Hostel (John F Kennedy Block)',
    'Hostel',
    'Mens Hostel J Block, named after John F Kennedy.',
    'assets/images/j.jpg',
    'https://example.com/videos/mens_hostel_j_tour.mp4',
    12.9715,
    79.1535,
    ARRAY['Accommodation', 'Common Rooms', 'Mess'],
    'Explore Mens Hostel J Block.',
    true,
    'Mens'
),

-- MH-K Block - Dr. Sarvepalli Radha Krishnan Block
(
    '49',
    'MH-K Block - Mens Hostel (Dr. Sarvepalli Radha Krishnan Block)',
    'Hostel',
    'Mens Hostel K Block, named after Dr. Sarvepalli Radha Krishnan.',
    'assets/images/Boys Hostel A.jpg',
    'https://example.com/videos/mens_hostel_k_tour.mp4',
    12.9760,
    79.1485,
    ARRAY['Accommodation', 'Common Rooms', 'Mess'],
    'Explore Mens Hostel K Block.',
    true,
    'Mens'
),

-- MH-L Block - Nethaji Subash Chandra Bose Block
(
    '50',
    'MH-L Block - Mens Hostel (Nethaji Subash Chandra Bose Block)',
    'Hostel',
    'Mens Hostel L Block, named after Nethaji Subash Chandra Bose.',
    'assets/images/Boys Hostel A.jpg',
    'https://example.com/videos/mens_hostel_l_tour.mp4',
    12.9765,
    79.1480,
    ARRAY['Accommodation', 'Common Rooms', 'Mess'],
    'Explore Mens Hostel L Block.',
    true,
    'Mens'
)

ON CONFLICT (id) DO UPDATE SET
    name           = EXCLUDED.name,
    category       = EXCLUDED.category,
    description    = EXCLUDED.description,
    image_path     = EXCLUDED.image_path,
    video_path     = EXCLUDED.video_path,
    latitude       = EXCLUDED.latitude,
    longitude      = EXCLUDED.longitude,
    features       = EXCLUDED.features,
    voiceover_text = EXCLUDED.voiceover_text,
    is_available   = EXCLUDED.is_available,
    gender_type    = EXCLUDED.gender_type;


-- ============================================================
-- STEP 3: INSERT / UPSERT ALL HOSTEL ROOMS
-- ============================================================

INSERT INTO public.hostel_rooms
    (id, location_id, room_type, photo_urls, features, pano_url)
VALUES

-- E Block rooms (location_id = '1')
(
    'room_101',
    '1',
    'Single Room',
    ARRAY['https://via.placeholder.com/150/hostel_room_1.jpg'],
    ARRAY['Attached Bathroom', 'Study Table', 'Cupboard'],
    'https://pannellum.org/images/alma.jpg'
),
(
    'room_102',
    '1',
    'Double Room',
    ARRAY['https://via.placeholder.com/150/hostel_room_2.jpg'],
    ARRAY['Balcony', 'Two beds', 'Shared Bathroom'],
    'https://pannellum.org/images/alma.jpg'
),

-- Suu Kyi Block rooms (location_id = '2')
(
    'room_201',
    '2',
    'Single Room',
    ARRAY['https://via.placeholder.com/150/g_hostel_room_1.jpg'],
    ARRAY['Attached Bathroom', 'Study Table', 'Cupboard'],
    'https://pannellum.org/images/alma.jpg'
),
(
    'room_202',
    '2',
    'Triple Room',
    ARRAY['https://via.placeholder.com/150/g_hostel_room_2.jpg'],
    ARRAY['Balcony', 'Three beds', 'Shared Bathroom'],
    'https://pannellum.org/images/alma.jpg'
)

ON CONFLICT (id) DO UPDATE SET
    location_id = EXCLUDED.location_id,
    room_type   = EXCLUDED.room_type,
    photo_urls  = EXCLUDED.photo_urls,
    features    = EXCLUDED.features,
    pano_url    = EXCLUDED.pano_url;


-- ============================================================
-- STEP 4: ROW LEVEL SECURITY (RLS) — PUBLIC READ ACCESS
-- Locations & rooms are public (anyone can view). Only
-- authenticated users can insert/update/delete.
-- ============================================================

-- Enable RLS on both tables
ALTER TABLE public.locations    ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.hostel_rooms ENABLE ROW LEVEL SECURITY;

-- Allow anyone (including anonymous) to SELECT
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_policies
        WHERE tablename = 'locations' AND policyname = 'Allow public read on locations'
    ) THEN
        CREATE POLICY "Allow public read on locations"
            ON public.locations FOR SELECT USING (true);
    END IF;

    IF NOT EXISTS (
        SELECT 1 FROM pg_policies
        WHERE tablename = 'hostel_rooms' AND policyname = 'Allow public read on hostel_rooms'
    ) THEN
        CREATE POLICY "Allow public read on hostel_rooms"
            ON public.hostel_rooms FOR SELECT USING (true);
    END IF;

    -- Only authenticated users can modify data
    IF NOT EXISTS (
        SELECT 1 FROM pg_policies
        WHERE tablename = 'locations' AND policyname = 'Allow authenticated write on locations'
    ) THEN
        CREATE POLICY "Allow authenticated write on locations"
            ON public.locations FOR ALL
            USING (auth.role() = 'authenticated')
            WITH CHECK (auth.role() = 'authenticated');
    END IF;

    IF NOT EXISTS (
        SELECT 1 FROM pg_policies
        WHERE tablename = 'hostel_rooms' AND policyname = 'Allow authenticated write on hostel_rooms'
    ) THEN
        CREATE POLICY "Allow authenticated write on hostel_rooms"
            ON public.hostel_rooms FOR ALL
            USING (auth.role() = 'authenticated')
            WITH CHECK (auth.role() = 'authenticated');
    END IF;
END
$$;


-- ============================================================
-- VERIFICATION QUERIES (optional – run after the insert)
-- ============================================================

-- SELECT count(*) FROM public.locations;           -- should be 26
-- SELECT count(*) FROM public.hostel_rooms;        -- should be 4
-- SELECT id, name, category, gender_type FROM public.locations ORDER BY id::int;
-- SELECT * FROM public.hostel_rooms;