Initial Configuration

-- Run this in your Supabase SQL Editor (https://supabase.com/dashboard → SQL Editor)

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(100) NOT NULL UNIQUE,
  firm VARCHAR(100),
  password_hash VARCHAR(255) NOT NULL,
  role TEXT NOT NULL DEFAULT 'investor'
    CHECK (role IN ('investor', 'admin')),
  timezone VARCHAR(100) NOT NULL DEFAULT 'Asia/Qatar',
  active BOOLEAN NOT NULL DEFAULT true,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_users_email ON users(email);

CREATE TABLE login_attempts (
  id SERIAL PRIMARY KEY,
  email VARCHAR(100) NOT NULL,
  attempted_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_login_attempts ON login_attempts(email, attempted_at);

CREATE TABLE demo_days (
  id SERIAL PRIMARY KEY,
  title VARCHAR(200) NOT NULL,
  description TEXT,
  event_date DATE NOT NULL,
  event_time TIMETZ NOT NULL,
  timezone VARCHAR(100) NOT NULL DEFAULT 'Asia/Qatar',
  status TEXT NOT NULL DEFAULT 'draft'
    CHECK (status IN ('draft', 'live', 'closed')),
  voting_open BOOLEAN NOT NULL DEFAULT false,
  results_public BOOLEAN NOT NULL DEFAULT false,
  announcement TEXT DEFAULT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE invitations (
  id SERIAL PRIMARY KEY,
  demo_day_id INTEGER NOT NULL REFERENCES demo_days(id),
  email VARCHAR(100) NOT NULL,
  token VARCHAR(64) NOT NULL UNIQUE,
  expires_at TIMESTAMPTZ NOT NULL,
  used_at TIMESTAMPTZ DEFAULT NULL,
  user_id INTEGER DEFAULT NULL REFERENCES users(id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_invitations_token ON invitations(token);
CREATE INDEX idx_invitations_demo_day ON invitations(demo_day_id);
CREATE INDEX idx_invitations_email ON invitations(email);

CREATE TABLE startups (
  id SERIAL PRIMARY KEY,
  demo_day_id INTEGER NOT NULL REFERENCES demo_days(id),
  name VARCHAR(100) NOT NULL,
  sector VARCHAR(100),
  tagline VARCHAR(200),
  description TEXT,
  how_it_works TEXT,
  investment_ask JSONB,
  revenue_traction TEXT,
  key_metrics TEXT,
  core_team JSONB,
  logo_url VARCHAR(255),
  deck_url VARCHAR(255),
  pitch_time TIMETZ,
  status TEXT NOT NULL DEFAULT 'active'
    CHECK (status IN ('active','due_diligence','passed','on_hold','invested')),
  sort_order INTEGER NOT NULL DEFAULT 0
);
CREATE INDEX idx_startups_demo_day ON startups(demo_day_id);

CREATE TABLE agenda_items (
  id SERIAL PRIMARY KEY,
  demo_day_id INTEGER NOT NULL REFERENCES demo_days(id),
  time_slot TIMETZ NOT NULL,
  label VARCHAR(200),
  startup_id INTEGER DEFAULT NULL
    REFERENCES startups(id) ON DELETE SET NULL,
  sort_order INTEGER NOT NULL DEFAULT 0
);

CREATE TABLE votes (
  id SERIAL PRIMARY KEY,
  demo_day_id INTEGER NOT NULL REFERENCES demo_days(id),
  investor_id INTEGER NOT NULL REFERENCES users(id),
  startup_id INTEGER NOT NULL
    REFERENCES startups(id) ON DELETE CASCADE,
  submitted_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE(investor_id, startup_id, demo_day_id)
);
CREATE INDEX idx_votes_demo_day ON votes(demo_day_id);
CREATE INDEX idx_votes_investor ON votes(investor_id);

CREATE TABLE interest (
  id SERIAL PRIMARY KEY,
  demo_day_id INTEGER NOT NULL REFERENCES demo_days(id),
  investor_id INTEGER NOT NULL REFERENCES users(id),
  startup_id INTEGER NOT NULL
    REFERENCES startups(id) ON DELETE CASCADE,
  flagged_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE(investor_id, startup_id, demo_day_id)
);

CREATE TABLE investor_notes (
  id SERIAL PRIMARY KEY,
  investor_id INTEGER NOT NULL REFERENCES users(id),
  startup_id INTEGER NOT NULL
    REFERENCES startups(id) ON DELETE CASCADE,
  content TEXT,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE(investor_id, startup_id)
);

CREATE TABLE settings (
  setting_key VARCHAR(50) PRIMARY KEY,
  setting_value TEXT
);

INSERT INTO settings VALUES ('active_demo_day_id', NULL);
INSERT INTO settings VALUES ('setup_complete', 'false');