temp.sql

-- WARNING: This schema is for context only and is not meant to be run.
-- Table order and constraints may not be valid for execution.

CREATE TABLE public.badges (
  id uuid NOT NULL DEFAULT gen_random_uuid(),
  name text NOT NULL UNIQUE,
  criteria text NOT NULL,
  ipfs_metadata_hash text NOT NULL,
  created_at timestamp with time zone DEFAULT now(),
  CONSTRAINT badges_pkey PRIMARY KEY (id)
);
CREATE TABLE public.bounties (
  id uuid NOT NULL DEFAULT gen_random_uuid(),
  question_id uuid NOT NULL,
  sponsor_address text NOT NULL,
  total_reward numeric NOT NULL,
  reward_wei bigint NOT NULL,
  status text DEFAULT 'active'::text CHECK (status = ANY (ARRAY['active'::text, 'completed'::text, 'cancelled'::text])),
  deadline timestamp with time zone,
  max_winners integer DEFAULT 1,
  description text,
  contract_address text,
  transaction_hash text,
  created_at timestamp with time zone DEFAULT now(),
  updated_at timestamp with time zone DEFAULT now(),
  CONSTRAINT bounties_pkey PRIMARY KEY (id),
  CONSTRAINT bounties_sponsor_address_fkey FOREIGN KEY (sponsor_address) REFERENCES public.profiles(wallet_address),
  CONSTRAINT bounties_question_id_fkey FOREIGN KEY (question_id) REFERENCES public.questions(id)
);
CREATE TABLE public.bounty_winners (
  id uuid NOT NULL DEFAULT gen_random_uuid(),
  bounty_id uuid NOT NULL,
  solution_id uuid NOT NULL,
  winner_address text NOT NULL,
  reward_amount numeric NOT NULL,
  position integer DEFAULT 1,
  payout_tx_hash text,
  claimed_at timestamp with time zone,
  created_at timestamp with time zone DEFAULT now(),
  CONSTRAINT bounty_winners_pkey PRIMARY KEY (id),
  CONSTRAINT bounty_winners_winner_address_fkey FOREIGN KEY (winner_address) REFERENCES public.profiles(wallet_address),
  CONSTRAINT bounty_winners_solution_id_fkey FOREIGN KEY (solution_id) REFERENCES public.solutions(id),
  CONSTRAINT bounty_winners_bounty_id_fkey FOREIGN KEY (bounty_id) REFERENCES public.bounties(id)
);
CREATE TABLE public.comments (
  id uuid NOT NULL DEFAULT uuid_generate_v4(),
  project_id uuid,
  user_id text NOT NULL,
  content text NOT NULL,
  created_at timestamp with time zone DEFAULT now(),
  updated_at timestamp with time zone DEFAULT now(),
  CONSTRAINT comments_pkey PRIMARY KEY (id),
  CONSTRAINT comments_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.profiles(wallet_address),
  CONSTRAINT comments_project_id_fkey FOREIGN KEY (project_id) REFERENCES public.projects(id)
);
CREATE TABLE public.donations (
  id uuid NOT NULL DEFAULT gen_random_uuid(),
  transaction_hash text NOT NULL UNIQUE,
  donor_address text NOT NULL,
  recipient_address text NOT NULL,
  amount numeric NOT NULL,
  amount_wei bigint NOT NULL,
  message text,
  donor_name text,
  block_number bigint,
  block_timestamp timestamp with time zone,
  created_at timestamp with time zone DEFAULT now(),
  updated_at timestamp with time zone DEFAULT now(),
  CONSTRAINT donations_pkey PRIMARY KEY (id),
  CONSTRAINT fk_recipient FOREIGN KEY (recipient_address) REFERENCES public.profiles(wallet_address),
  CONSTRAINT fk_donor FOREIGN KEY (donor_address) REFERENCES public.profiles(wallet_address)
);
CREATE TABLE public.posts (
  id uuid NOT NULL DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL,
  content text NOT NULL,
  image_url text,
  video_url text,
  created_at timestamp with time zone DEFAULT now(),
  updated_at timestamp with time zone DEFAULT now(),
  likes_count integer DEFAULT 0,
  comments_count integer DEFAULT 0,
  CONSTRAINT posts_pkey PRIMARY KEY (id),
  CONSTRAINT posts_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.profiles(id)
);
CREATE TABLE public.profiles (
  id uuid NOT NULL DEFAULT gen_random_uuid(),
  wallet_address text NOT NULL UNIQUE,
  username text NOT NULL UNIQUE,
  display_name text NOT NULL,
  bio text,
  skills ARRAY,
  github text,
  twitter text,
  website text,
  profile_image text,
  cover_image text,
  reputation integer DEFAULT 0,
  total_earnings numeric DEFAULT 0,
  projects_count integer DEFAULT 0,
  created_at timestamp with time zone DEFAULT now(),
  updated_at timestamp with time zone DEFAULT now(),
  total_donations_received numeric DEFAULT 0,
  total_donations_sent numeric DEFAULT 0,
  donations_received_count integer DEFAULT 0,
  donations_sent_count integer DEFAULT 0,
  role text DEFAULT 'developer'::text CHECK (role = ANY (ARRAY['company'::text, 'developer'::text])),
  CONSTRAINT profiles_pkey PRIMARY KEY (id)
);
CREATE TABLE public.project_donations (
  id uuid NOT NULL DEFAULT gen_random_uuid(),
  project_id uuid NOT NULL,
  donation_id uuid NOT NULL,
  created_at timestamp with time zone DEFAULT now(),
  CONSTRAINT project_donations_pkey PRIMARY KEY (id),
  CONSTRAINT project_donations_project_id_fkey FOREIGN KEY (project_id) REFERENCES public.projects(id),
  CONSTRAINT project_donations_donation_id_fkey FOREIGN KEY (donation_id) REFERENCES public.donations(id)
);
CREATE TABLE public.project_likes (
  id uuid NOT NULL DEFAULT uuid_generate_v4(),
  project_id uuid,
  user_address text NOT NULL,
  created_at timestamp with time zone DEFAULT now(),
  CONSTRAINT project_likes_pkey PRIMARY KEY (id),
  CONSTRAINT project_likes_project_id_fkey FOREIGN KEY (project_id) REFERENCES public.projects(id)
);
CREATE TABLE public.projects (
  id uuid NOT NULL DEFAULT gen_random_uuid(),
  title text NOT NULL,
  description text NOT NULL,
  video_url text,
  images ARRAY,
  tags ARRAY,
  github_url text,
  live_url text,
  likes integer DEFAULT 0,
  donations numeric DEFAULT 0,
  creator_address text,
  created_at timestamp with time zone DEFAULT now(),
  total_donations_received numeric DEFAULT 0,
  donations_count integer DEFAULT 0,
  like_count bigint,
  CONSTRAINT projects_pkey PRIMARY KEY (id),
  CONSTRAINT projects_creator_address_fkey FOREIGN KEY (creator_address) REFERENCES public.profiles(wallet_address)
);
CREATE TABLE public.questions (
  id uuid NOT NULL DEFAULT gen_random_uuid(),
  title text NOT NULL,
  description text NOT NULL,
  test_cases jsonb NOT NULL,
  creator_address text NOT NULL,
  created_at timestamp with time zone DEFAULT now(),
  updated_at timestamp with time zone DEFAULT now(),
  has_bounty boolean DEFAULT false,
  CONSTRAINT questions_pkey PRIMARY KEY (id),
  CONSTRAINT questions_creator_address_fkey FOREIGN KEY (creator_address) REFERENCES public.profiles(wallet_address)
);
CREATE TABLE public.solutions (
  id uuid NOT NULL DEFAULT gen_random_uuid(),
  question_id uuid NOT NULL,
  developer_address text NOT NULL,
  code text NOT NULL,
  language text NOT NULL CHECK (language = 'cpp'::text),
  status text DEFAULT 'pending'::text CHECK (status = ANY (ARRAY['pending'::text, 'passed'::text, 'failed'::text])),
  submitted_at timestamp with time zone DEFAULT now(),
  CONSTRAINT solutions_pkey PRIMARY KEY (id),
  CONSTRAINT solutions_question_id_fkey FOREIGN KEY (question_id) REFERENCES public.questions(id),
  CONSTRAINT solutions_developer_address_fkey FOREIGN KEY (developer_address) REFERENCES public.profiles(wallet_address)
);
CREATE TABLE public.user_badges (
  id uuid NOT NULL DEFAULT gen_random_uuid(),
  developer_address text NOT NULL,
  badge_id uuid NOT NULL,
  nft_token_id text,
  nft_contract_address text,
  awarded_at timestamp with time zone DEFAULT now(),
  CONSTRAINT user_badges_pkey PRIMARY KEY (id),
  CONSTRAINT user_badges_badge_id_fkey FOREIGN KEY (badge_id) REFERENCES public.badges(id),
  CONSTRAINT user_badges_developer_address_fkey FOREIGN KEY (developer_address) REFERENCES public.profiles(wallet_address)
);