server/migrations/init.sql

-- Create news table
CREATE TABLE IF NOT EXISTS news (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    excerpt TEXT,
    date DATE NOT NULL,
    category VARCHAR(100)
);

-- Create datasets table
CREATE TABLE IF NOT EXISTS datasets (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    category VARCHAR(100),
    size VARCHAR(50),
    downloads INTEGER DEFAULT 0
);

-- Insert sample data for news
INSERT INTO news (title, excerpt, date, category) VALUES
('New Cold Chain Initiative Launched in Southeast Asia', 'Regional partnership aims to reduce post-harvest losses by 30% through solar-powered refrigeration.', '2025-10-15', 'Policy'),
('Innovative Edible Coatings Show Promise in Lab Trials', 'New biodegradable coatings extend shelf life of fruits by up to 2 weeks.', '2025-10-10', 'Technology'),
('Global Fund Announces $50M for FLW Reduction Projects', 'Funding opportunity for pilot projects connecting smallholders to processing facilities.', '2025-10-05', 'Finance');

-- Insert sample data for datasets
INSERT INTO datasets (title, description, category, size, downloads) VALUES
('Cold Chain Infrastructure Map', 'Geospatial data on refrigeration facilities across Sub-Saharan Africa', 'Infrastructure', '2.4 GB', 1240),
('Post-Harvest Loss Hotspots', 'Identified areas with highest food loss rates in South Asia', 'Analytics', '890 MB', 890),
('Processing Capacity Database', 'List of food processing facilities with available capacity', 'Industry', '1.1 GB', 1560);