INSERT INTO storage.buckets (id, name, public)
VALUES ('files_bucket', 'files_bucket', true);

CREATE POLICY "Whack"
    ON storage.objects
    FOR ALL
    USING (bucket_id = 'files_bucket');

CREATE TABLE study_spaces (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    description text,
    -- Location within building, e.g., "Room 101"
    location text,
    -- Not bothered to write a proper data migration
    building_location_old text,
    building_location jsonb,
    tags text[] NOT NULL DEFAULT array[]::text[],
    volume text NOT NULL,
    wifi text NOT NULL,
    power text NOT NULL,
     
    created_at timestamp with time zone DEFAULT now(),
    updated_at timestamp with time zone DEFAULT now()
);

CREATE TABLE study_space_images (
    study_space_id uuid REFERENCES study_spaces(id) ON DELETE CASCADE,
    image_path text NOT NULL,
    created_at timestamp with time zone DEFAULT now(),
    updated_at timestamp with time zone DEFAULT now(),
    PRIMARY KEY (study_space_id, image_path)
);

CREATE TABLE reports (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    study_space_id uuid REFERENCES study_spaces(id) ON DELETE CASCADE,
    created_at timestamp with time zone DEFAULT now(),
    updated_at timestamp with time zone DEFAULT now(),
    type text NOT NULL,
    content text
);

CREATE TABLE study_space_hours (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    study_space_id UUID REFERENCES study_spaces(id) ON DELETE CASCADE,
    day_of_week INT CHECK (day_of_week BETWEEN 0 AND 6),  -- 0 = Sunday, 6 = Saturday
    opens_at TIME NOT NULL,
    closes_at TIME NOT NULL,
    is_24_7 BOOLEAN DEFAULT FALSE,
    created_at timestamp with time zone DEFAULT now(),
    updated_at timestamp with time zone DEFAULT now()
);

-- Triggers
CREATE TRIGGER study_spaces_updated_at
AFTER UPDATE ON study_spaces
FOR EACH ROW EXECUTE FUNCTION handle_updated_at();

CREATE TRIGGER study_space_images_updated_at
AFTER UPDATE ON study_space_images
FOR EACH ROW EXECUTE FUNCTION handle_updated_at();

CREATE TRIGGER reports_updated_at
AFTER UPDATE ON reports
FOR EACH ROW EXECUTE FUNCTION handle_updated_at();

CREATE TRIGGER study_space_hours_updated_at
AFTER UPDATE ON study_space_hours
FOR EACH ROW EXECUTE FUNCTION handle_updated_at();