Spaces:

srinu1912066
/

hack-yatra

Paused

hack-yatra / supabase_schema.sql
srinu1912066
Upload folder using huggingface_hub (part 2)
0a3679a verified 3 months ago
24.2 kB
-- ====================================================================
-- Greater Visakhapatnam Municipal Corporation (GVMC) Open Data Portal
-- UNIFIED CENTRALIZED APPROVAL SYSTEM & AUTOMATED MIGRATIONS (100% ERROR-FREE)
-- 
-- Copy and paste this script directly into Supabase SQL Editor:
-- https://supabase.com/dashboard/project/srmdeiiibddnuizyoqxg/sql/new
-- ====================================================================

-- DROP OLD TABLES IF EXISTS FOR A CLEAN FRESH DATABASE INSTANCE
DROP TABLE IF EXISTS public.approval_history CASCADE;
DROP TABLE IF EXISTS public.approval_requests CASCADE;
DROP TABLE IF EXISTS public.notifications CASCADE;
DROP TABLE IF EXISTS public.student_projects CASCADE;
DROP TABLE IF EXISTS public.civic_issues CASCADE;
DROP TABLE IF EXISTS public.research_studies CASCADE;
DROP TABLE IF EXISTS public.api_key_applications CASCADE;
DROP TABLE IF EXISTS public.confidential_data_requests CASCADE;
DROP TABLE IF EXISTS public.problem_statements CASCADE;
DROP TABLE IF EXISTS public.announcements CASCADE;
DROP TABLE IF EXISTS public.datasets CASCADE;

-- 1. CENTRAL APPROVAL TABLE
CREATE TABLE public.approval_requests (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    source_type TEXT NOT NULL, -- 'Civic Report', 'Student Innovation', 'Research Project', 'Research Paper'
    source_record_id TEXT NOT NULL,
    title TEXT NOT NULL,
    description TEXT NOT NULL,
    submitted_by TEXT DEFAULT 'Visakhapatnam User',
    submitted_by_role TEXT DEFAULT 'CITIZEN',
    department TEXT DEFAULT 'GVMC Municipal Infrastructure',
    ward INT DEFAULT 12,
    status TEXT DEFAULT 'Pending', -- 'Pending', 'Approved', 'Rejected'
    rejection_reason TEXT,
    reviewer_id TEXT,
    reviewer_name TEXT,
    reviewed_at TIMESTAMP WITH TIME ZONE,
    priority TEXT DEFAULT 'NORMAL',
    metadata JSONB DEFAULT '{}'::jsonb,
    attachments JSONB DEFAULT '[]'::jsonb,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now()) NOT NULL,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now()) NOT NULL
);

-- 2. APPROVAL HISTORY / AUDIT TRAIL TABLE
CREATE TABLE public.approval_history (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    approval_request_id UUID REFERENCES public.approval_requests(id) ON DELETE CASCADE,
    previous_status TEXT,
    new_status TEXT NOT NULL,
    action TEXT NOT NULL,
    remarks TEXT,
    performed_by TEXT DEFAULT 'GVMC Administrator',
    performed_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now()) NOT NULL
);

-- 3. NOTIFICATIONS TABLE
CREATE TABLE public.notifications (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    recipient_id TEXT DEFAULT 'usr-all',
    title TEXT NOT NULL,
    description TEXT NOT NULL,
    notification_type TEXT DEFAULT 'APPROVAL_UPDATE',
    status TEXT DEFAULT 'UNREAD',
    source_type TEXT,
    source_record_id TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now()) NOT NULL,
    read_at TIMESTAMP WITH TIME ZONE
);

-- 4. BASE CORE TABLES
CREATE TABLE public.announcements (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    title TEXT NOT NULL,
    content TEXT NOT NULL,
    priority TEXT DEFAULT 'NORMAL',
    target_roles TEXT[] DEFAULT '{"CITIZEN","STUDENT","RESEARCHER","STARTUP"}',
    target_regions TEXT[] DEFAULT '{"ALL"}',
    image_url TEXT,
    voice_note_url TEXT,
    published_by TEXT DEFAULT 'Dr. G. V. Rao (GVMC Commissioner)',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now()) NOT NULL
);

CREATE TABLE public.problem_statements (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    problem_code TEXT NOT NULL,
    title TEXT NOT NULL,
    description TEXT NOT NULL,
    department TEXT NOT NULL,
    category TEXT DEFAULT 'General',
    track_type TEXT DEFAULT 'SOFTWARE',
    difficulty TEXT DEFAULT 'INTERMEDIATE',
    area_name TEXT DEFAULT 'Visakhapatnam',
    target_domains TEXT[] DEFAULT '{"COMPUTER_SCIENCE","DATA_SCIENCE"}',
    related_datasets TEXT[] DEFAULT '{}',
    is_featured BOOLEAN DEFAULT true,
    is_new BOOLEAN DEFAULT true,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now()) NOT NULL
);

CREATE TABLE public.student_projects (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    project_code TEXT NOT NULL,
    student_name TEXT DEFAULT 'Student Innovator',
    title TEXT NOT NULL,
    description TEXT NOT NULL,
    academic_domain TEXT DEFAULT 'COMPUTER_SCIENCE',
    selected_datasets TEXT[] DEFAULT '{}',
    problem_statement_id TEXT,
    github_link TEXT,
    presentation_url TEXT,
    demo_url TEXT,
    current_status TEXT DEFAULT 'SUBMITTED',
    supervisor_feedback TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now()) NOT NULL,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now()) NOT NULL
);

CREATE TABLE public.civic_issues (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    issue_code TEXT NOT NULL,
    citizen_name TEXT DEFAULT 'Visakhapatnam Resident',
    category TEXT NOT NULL,
    description TEXT NOT NULL,
    area TEXT DEFAULT 'Siripuram',
    ward_number INT DEFAULT 12,
    latitude FLOAT DEFAULT 17.7231,
    longitude FLOAT DEFAULT 83.3012,
    captured_images TEXT[] DEFAULT '{}',
    current_status TEXT DEFAULT 'SUBMITTED',
    priority TEXT DEFAULT 'MEDIUM',
    linked_dataset_id TEXT,
    domain_notification_sent BOOLEAN DEFAULT false,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now()) NOT NULL
);

CREATE TABLE public.research_studies (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    study_code TEXT NOT NULL,
    researcher_name TEXT DEFAULT 'Academic Researcher',
    institution TEXT DEFAULT 'Andhra University',
    title TEXT NOT NULL,
    abstract TEXT NOT NULL,
    target_domain TEXT DEFAULT 'DATA_SCIENCE',
    paper_url TEXT,
    dataset_ids TEXT[] DEFAULT '{}',
    current_status TEXT DEFAULT 'PENDING',
    admin_feedback TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now()) NOT NULL
);

CREATE TABLE public.api_key_applications (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    company_name TEXT NOT NULL,
    founder_name TEXT DEFAULT 'Startup Founder',
    use_case TEXT NOT NULL,
    requested_tier TEXT DEFAULT 'COMMERCIAL_STANDARD',
    status TEXT DEFAULT 'PENDING',
    api_key TEXT,
    admin_feedback TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now()) NOT NULL
);

CREATE TABLE public.confidential_data_requests (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    request_code TEXT NOT NULL,
    requester_name TEXT NOT NULL,
    requester_role TEXT NOT NULL,
    dataset_id TEXT NOT NULL,
    dataset_title TEXT NOT NULL,
    purpose TEXT NOT NULL,
    status TEXT DEFAULT 'PENDING',
    access_token TEXT,
    admin_notes TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now()) NOT NULL
);

CREATE TABLE public.datasets (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    title TEXT NOT NULL,
    slug TEXT NOT NULL,
    description TEXT NOT NULL,
    category TEXT NOT NULL,
    department TEXT NOT NULL,
    access_level TEXT DEFAULT 'PUBLIC',
    is_confidential BOOLEAN DEFAULT false,
    status TEXT DEFAULT 'PUBLISHED',
    update_frequency TEXT DEFAULT 'Daily',
    total_downloads INT DEFAULT 0,
    total_views INT DEFAULT 0,
    ward_number INT DEFAULT 12,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now()) NOT NULL
);

-- INDEXES FOR MAXIMUM QUERY PERFORMANCE
CREATE INDEX idx_approval_requests_status ON public.approval_requests(status);
CREATE INDEX idx_approval_requests_source_type ON public.approval_requests(source_type);
CREATE INDEX idx_approval_requests_created_at ON public.approval_requests(created_at DESC);
CREATE INDEX idx_approval_history_request_id ON public.approval_history(approval_request_id);
CREATE INDEX idx_notifications_recipient ON public.notifications(recipient_id);

-- RLS POLICIES
ALTER TABLE public.approval_requests ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.approval_history ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.notifications ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.announcements ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.problem_statements ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.student_projects ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.civic_issues ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.research_studies ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.api_key_applications ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.confidential_data_requests ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.datasets ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Allow public access to approval_requests" ON public.approval_requests FOR ALL USING (true);
CREATE POLICY "Allow public access to approval_history" ON public.approval_history FOR ALL USING (true);
CREATE POLICY "Allow public access to notifications" ON public.notifications FOR ALL USING (true);
CREATE POLICY "Allow public all access to announcements" ON public.announcements FOR ALL USING (true);
CREATE POLICY "Allow public all access to problem_statements" ON public.problem_statements FOR ALL USING (true);
CREATE POLICY "Allow public all access to student_projects" ON public.student_projects FOR ALL USING (true);
CREATE POLICY "Allow public all access to civic_issues" ON public.civic_issues FOR ALL USING (true);
CREATE POLICY "Allow public all access to research_studies" ON public.research_studies FOR ALL USING (true);
CREATE POLICY "Allow public all access to api_key_applications" ON public.api_key_applications FOR ALL USING (true);
CREATE POLICY "Allow public all access to confidential_data_requests" ON public.confidential_data_requests FOR ALL USING (true);
CREATE POLICY "Allow public all access to datasets" ON public.datasets FOR ALL USING (true);

-- REALTIME WEBSOCKET PUBLICATION FOR APPROVAL SYSTEM
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_publication_tables WHERE pubname = 'supabase_realtime' AND tablename = 'approval_requests') THEN
        ALTER PUBLICATION supabase_realtime ADD TABLE public.approval_requests;
    END IF;

    IF NOT EXISTS (SELECT 1 FROM pg_publication_tables WHERE pubname = 'supabase_realtime' AND tablename = 'approval_history') THEN
        ALTER PUBLICATION supabase_realtime ADD TABLE public.approval_history;
    END IF;

    IF NOT EXISTS (SELECT 1 FROM pg_publication_tables WHERE pubname = 'supabase_realtime' AND tablename = 'notifications') THEN
        ALTER PUBLICATION supabase_realtime ADD TABLE public.notifications;
    END IF;

    IF NOT EXISTS (SELECT 1 FROM pg_publication_tables WHERE pubname = 'supabase_realtime' AND tablename = 'announcements') THEN
        ALTER PUBLICATION supabase_realtime ADD TABLE public.announcements;
    END IF;

    IF NOT EXISTS (SELECT 1 FROM pg_publication_tables WHERE pubname = 'supabase_realtime' AND tablename = 'problem_statements') THEN
        ALTER PUBLICATION supabase_realtime ADD TABLE public.problem_statements;
    END IF;

    IF NOT EXISTS (SELECT 1 FROM pg_publication_tables WHERE pubname = 'supabase_realtime' AND tablename = 'student_projects') THEN
        ALTER PUBLICATION supabase_realtime ADD TABLE public.student_projects;
    END IF;

    IF NOT EXISTS (SELECT 1 FROM pg_publication_tables WHERE pubname = 'supabase_realtime' AND tablename = 'civic_issues') THEN
        ALTER PUBLICATION supabase_realtime ADD TABLE public.civic_issues;
    END IF;

    IF NOT EXISTS (SELECT 1 FROM pg_publication_tables WHERE pubname = 'supabase_realtime' AND tablename = 'research_studies') THEN
        ALTER PUBLICATION supabase_realtime ADD TABLE public.research_studies;
    END IF;

    IF NOT EXISTS (SELECT 1 FROM pg_publication_tables WHERE pubname = 'supabase_realtime' AND tablename = 'api_key_applications') THEN
        ALTER PUBLICATION supabase_realtime ADD TABLE public.api_key_applications;
    END IF;

    IF NOT EXISTS (SELECT 1 FROM pg_publication_tables WHERE pubname = 'supabase_realtime' AND tablename = 'confidential_data_requests') THEN
        ALTER PUBLICATION supabase_realtime ADD TABLE public.confidential_data_requests;
    END IF;

    IF NOT EXISTS (SELECT 1 FROM pg_publication_tables WHERE pubname = 'supabase_realtime' AND tablename = 'datasets') THEN
        ALTER PUBLICATION supabase_realtime ADD TABLE public.datasets;
    END IF;
END $$;

-- ====================================================================
-- AUTOMATED TRIGGERS: SOURCE 1 (CIVIC ISSUE) -> APPROVAL REQUEST
-- ====================================================================
CREATE OR REPLACE FUNCTION public.trg_fn_civic_issue_auto_approval()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO public.approval_requests (
        source_type,
        source_record_id,
        title,
        description,
        submitted_by,
        submitted_by_role,
        department,
        ward,
        status,
        priority,
        metadata,
        attachments
    ) VALUES (
        'Civic Report',
        NEW.id::text,
        NEW.category || ' - ' || NEW.area,
        NEW.description,
        COALESCE(NEW.citizen_name, 'Visakhapatnam Resident'),
        'CITIZEN',
        'GVMC Public Works & Engineering',
        COALESCE(NEW.ward_number, 12),
        'Pending',
        COALESCE(NEW.priority, 'HIGH'),
        jsonb_build_object(
            'category', NEW.category,
            'area', NEW.area,
            'latitude', NEW.latitude,
            'longitude', NEW.longitude,
            'issue_code', NEW.issue_code
        ),
        to_jsonb(COALESCE(NEW.captured_images, '{}'::text[]))
    );
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_civic_issue_auto_approval ON public.civic_issues;
CREATE TRIGGER trg_civic_issue_auto_approval
    AFTER INSERT ON public.civic_issues
    FOR EACH ROW EXECUTE FUNCTION public.trg_fn_civic_issue_auto_approval();

-- ====================================================================
-- AUTOMATED TRIGGERS: SOURCE 2 (STUDENT PROJECT) -> APPROVAL REQUEST
-- ====================================================================
CREATE OR REPLACE FUNCTION public.trg_fn_student_project_auto_approval()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO public.approval_requests (
        source_type,
        source_record_id,
        title,
        description,
        submitted_by,
        submitted_by_role,
        department,
        ward,
        status,
        priority,
        metadata,
        attachments
    ) VALUES (
        'Student Innovation',
        NEW.id::text,
        NEW.title,
        NEW.description,
        COALESCE(NEW.student_name, 'Student Innovator'),
        'STUDENT',
        'GVMC Innovation Cell & Academic Research',
        12,
        'Pending',
        'NORMAL',
        jsonb_build_object(
            'project_code', NEW.project_code,
            'academic_domain', NEW.academic_domain,
            'github_link', NEW.github_link,
            'demo_url', NEW.demo_url,
            'selected_datasets', NEW.selected_datasets
        ),
        to_jsonb(ARRAY[NEW.github_link, NEW.demo_url])
    );
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_student_project_auto_approval ON public.student_projects;
CREATE TRIGGER trg_student_project_auto_approval
    AFTER INSERT ON public.student_projects
    FOR EACH ROW EXECUTE FUNCTION public.trg_fn_student_project_auto_approval();

-- ====================================================================
-- AUTOMATED TRIGGERS: SOURCE 3 & 4 (RESEARCH STUDY/PAPER) -> APPROVAL REQUEST
-- ====================================================================
CREATE OR REPLACE FUNCTION public.trg_fn_research_study_auto_approval()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO public.approval_requests (
        source_type,
        source_record_id,
        title,
        description,
        submitted_by,
        submitted_by_role,
        department,
        ward,
        status,
        priority,
        metadata,
        attachments
    ) VALUES (
        'Research Project',
        NEW.id::text,
        NEW.title,
        NEW.abstract,
        COALESCE(NEW.researcher_name, 'Academic Researcher'),
        'RESEARCHER',
        NEW.institution,
        12,
        'Pending',
        'HIGH',
        jsonb_build_object(
            'study_code', NEW.study_code,
            'institution', NEW.institution,
            'target_domain', NEW.target_domain,
            'paper_url', NEW.paper_url
        ),
        to_jsonb(ARRAY[NEW.paper_url])
    );
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_research_study_auto_approval ON public.research_studies;
CREATE TRIGGER trg_research_study_auto_approval
    AFTER INSERT ON public.research_studies
    FOR EACH ROW EXECUTE FUNCTION public.trg_fn_research_study_auto_approval();

-- ====================================================================
-- AUTOMATED DECISION TRIGGER: APPROVAL REQUEST UPDATE -> HISTORY + SOURCE SYNC + NOTIFICATION
-- ====================================================================
CREATE OR REPLACE FUNCTION public.trg_fn_on_approval_decision()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.status <> OLD.status THEN
        -- 1. Create Immutable Audit History Entry
        INSERT INTO public.approval_history (
            approval_request_id,
            previous_status,
            new_status,
            action,
            remarks,
            performed_by
        ) VALUES (
            NEW.id,
            OLD.status,
            NEW.status,
            UPPER(NEW.status),
            NEW.rejection_reason,
            COALESCE(NEW.reviewer_name, 'GVMC Senior Administrator')
        );

        -- 2. Create Notification for Submitter (Using standard string concatenation)
        INSERT INTO public.notifications (
            recipient_id,
            title,
            description,
            notification_type,
            status,
            source_type,
            source_record_id
        ) VALUES (
            NEW.submitted_by,
            'Approval Status Updated: ' || NEW.title,
            CASE 
                WHEN NEW.status = 'Approved' THEN 'Your submission "' || NEW.title || '" has been APPROVED by ' || COALESCE(NEW.reviewer_name, 'GVMC Administrator') || '.'
                ELSE 'Your submission "' || NEW.title || '" was REJECTED. Reason: ' || COALESCE(NEW.rejection_reason, 'No remarks specified.')
            END,
            'APPROVAL_UPDATE',
            'UNREAD',
            NEW.source_type,
            NEW.source_record_id
        );

        -- 3. Synchronize Original Source Table Status
        IF NEW.source_type = 'Civic Report' THEN
            UPDATE public.civic_issues 
            SET current_status = CASE WHEN NEW.status = 'Approved' THEN 'RESOLVED' ELSE 'REJECTED' END
            WHERE id::text = NEW.source_record_id OR issue_code = NEW.source_record_id;
        ELSIF NEW.source_type = 'Student Innovation' THEN
            UPDATE public.student_projects
            SET current_status = CASE WHEN NEW.status = 'Approved' THEN 'APPROVED_FOR_PILOT' ELSE 'REJECTED' END,
                supervisor_feedback = NEW.rejection_reason
            WHERE id::text = NEW.source_record_id OR project_code = NEW.source_record_id;
        ELSIF NEW.source_type IN ('Research Project', 'Research Paper') THEN
            UPDATE public.research_studies
            SET current_status = CASE WHEN NEW.status = 'Approved' THEN 'APPROVED' ELSE 'REJECTED' END,
                admin_feedback = NEW.rejection_reason
            WHERE id::text = NEW.source_record_id OR study_code = NEW.source_record_id;
        END IF;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_on_approval_decision ON public.approval_requests;
CREATE TRIGGER trg_on_approval_decision
    AFTER UPDATE ON public.approval_requests
    FOR EACH ROW EXECUTE FUNCTION public.trg_fn_on_approval_decision();

-- ====================================================================
-- SEED DATA POPULATION FOR CENTRALIZED APPROVAL SYSTEM
-- ====================================================================

INSERT INTO public.approval_requests (
    source_type, source_record_id, title, description, submitted_by, submitted_by_role, department, ward, status, priority, metadata, attachments
) VALUES 
(
    'Civic Report', 
    'ISSUE-2026-9041', 
    'Water Pipeline Rupture - Siripuram Circle', 
    'Major drinking water pipeline rupture near Siripuram main junction. Severe water loss requiring urgent valve shutdown and pipe repair.', 
    'Visakhapatnam Resident', 
    'CITIZEN', 
    'GVMC Water Works Department', 
    12, 
    'Pending', 
    'HIGH', 
    jsonb_build_object('category', 'WATER_LEAKAGE', 'area', 'Siripuram', 'latitude', 17.7231, 'longitude', 83.3012), 
    jsonb_build_array('https://images.unsplash.com/photo-1584467735815-f778f274e296?w=600')
),
(
    'Student Innovation', 
    'PRJ-2026-8812', 
    'AI-Driven Municipal Solid Waste Route Optimizer', 
    'Genetic algorithm route planner for GVMC garbage trucks reducing fuel consumption by 18% in Ward 12 Siripuram.', 
    'Ananya Sharma (Team AU)', 
    'STUDENT', 
    'GVMC Public Health & Sanitation', 
    12, 
    'Pending', 
    'HIGH', 
    jsonb_build_object('tech_stack', 'Python, PyTorch, React, Mapbox', 'github', 'https://github.com/srinivas191206/rtih'), 
    jsonb_build_array('https://github.com/srinivas191206/rtih')
),
(
    'Research Project', 
    'STD-2026-401', 
    'Spatial Evaluation of Coastal Aquifer Salinity Telemetry', 
    'Hydro-geological study analyzing 5-year coastal groundwater salinity telemetry across 15 monitoring wells in Visakhapatnam.', 
    'Dr. Arisetti Rao', 
    'RESEARCHER', 
    'Andhra University Department of Geology', 
    12, 
    'Pending', 
    'NORMAL', 
    jsonb_build_object('institution', 'Andhra University', 'target_domain', 'DATA_SCIENCE'), 
    jsonb_build_array('https://gvmc-opendata.in/docs/pub-2026.pdf')
),
(
    'Research Paper', 
    'PUB-2026-902', 
    'IoT Micro-Sensor Network for Urban Air Quality Telemetry', 
    'Published peer-reviewed paper detailing deployment of 50 low-cost optical particulate sensors across Visakhapatnam Industrial Zone.', 
    'Prof. V. S. Ramachandran', 
    'RESEARCHER', 
    'GITAM University School of Technology', 
    14, 
    'Pending', 
    'NORMAL', 
    jsonb_build_object('keywords', 'IoT, Air Quality, Telemetry, Urban Health', 'authors', 'Prof. V. S. Ramachandran, Dr. K. Priya'), 
    jsonb_build_array('https://gvmc-opendata.in/docs/paper-air-quality.pdf')
);

INSERT INTO public.announcements (title, content, priority, target_roles, target_regions, published_by)
VALUES 
('Scheduled Water Supply Maintenance - Siripuram & MVP Colony', 'GVMC Engineering will execute main pipeline valve replacement near Siripuram Circle on 02 August 2026. Water supply will be paused from 10:00 PM to 06:00 AM.', 'HIGH', '{"CITIZEN","STUDENT","RESEARCHER","STARTUP"}', '{"Siripuram","MVP Colony"}', 'Dr. G. V. Rao (GVMC Commissioner)'),
('Clean Visakhapatnam Beach Telemetry Drive', 'Join GVMC and Andhra University for coastal cleanliness telemetry mapping at RK Beach.', 'NORMAL', '{"CITIZEN","STUDENT","RESEARCHER","STARTUP"}', '{"ALL"}', 'GVMC Environment Cell');

INSERT INTO public.problem_statements (problem_code, title, description, department, category, track_type, difficulty, area_name, target_domains)
VALUES 
('PS-2026-101', 'Smart Drainage Telemetry & Urban Flood Inundation Predictor', 'Develop an AI spatial forecasting engine using 10-year storm drain capacity telemetry in Zone-2 to predict urban flood inundation corridors.', 'GVMC Engineering & Disaster Cell', 'Urban Flooding', 'SOFTWARE', 'ADVANCED', 'Visakhapatnam Zone-2', '{"COMPUTER_SCIENCE","DATA_SCIENCE"}'),
('PS-2026-102', 'IoT Solid Waste Bin Fill-Level Optimization', 'Deploy real-time fill level sensors on municipal waste collection bins across Ward #12 Siripuram.', 'GVMC Public Health & Sanitation', 'Waste Management', 'HARDWARE', 'INTERMEDIATE', 'Ward #12 Siripuram', '{"ELECTRONICS","CIVIL_ENGINEERING"}');