-- Drop tables if they exist DROP TABLE IF EXISTS shared; DROP TABLE IF EXISTS shared_types; -- Create table for shared types CREATE TABLE shared_types ( key VARCHAR(50) PRIMARY KEY -- Type key (e.g., 'WORKSPACE', 'RICE') ); -- Create table for shared items CREATE TABLE shared ( id UUID NOT NULL PRIMARY KEY, -- Unique identifier slug VARCHAR(75) NOT NULL UNIQUE, -- Unique user-friendly identifier type VARCHAR(15) NOT NULL REFERENCES shared_types(key) ON DELETE CASCADE, -- Foreign key to shared_types version VARCHAR(10) NOT NULL, -- Data version os VARCHAR(30) NOT NULL, -- Operating system name VARCHAR(75) NOT NULL, -- Name of the rice author VARCHAR(100) NOT NULL, -- Name of the author token UUID NOT NULL, -- Unique authorization token visits INTEGER DEFAULT 0 NOT NULL, -- Visit counter, initialized to 0 level INTEGER DEFAULT 0 NOT NULL, -- Level: 0 (Public), 1 (Verified) created_at TIMESTAMP DEFAULT NOW(), -- Creation date updated_at TIMESTAMP -- Last update date ); -- Insert default types INSERT INTO shared_types (key) VALUES ('WORKSPACE'), ('RICE'); -- Create function to increment visit count CREATE OR REPLACE FUNCTION increment_visits(slug_param TEXT) RETURNS VOID AS $$ BEGIN UPDATE shared SET visits = visits + 1 WHERE slug = slug_param; END; $$ LANGUAGE plpgsql;