


-- Application Sessions table (stores multiple concurrent sessions per user)
-- Note: this is separate from the express-session store table named "session"
CREATE TABLE IF NOT EXISTS "Sessions" (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(), -- requires pgcrypto or uuid-ossp
  "UserName" VARCHAR(50) NOT NULL REFERENCES "Users"("UserName") ON DELETE CASCADE,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  expires_at TIMESTAMP WITH TIME ZONE,
    meta JSONB
);

-- Indexes optimized by username instead of numeric user id
CREATE INDEX IF NOT EXISTS idx_sessions_username ON "Sessions" ("UserName");
CREATE INDEX IF NOT EXISTS idx_sessions_user_created ON "Sessions" ("UserName", created_at);

-- Support expiry-based cleanup and validity checks
CREATE INDEX IF NOT EXISTS idx_sessions_username_expires
ON "Sessions" ("UserName", expires_at);

CREATE INDEX IF NOT EXISTS idx_sessions_expires
ON "Sessions" (expires_at)
WHERE expires_at IS NOT NULL;

-- Optional (Postgres 11+): covering indexes for hot-path lookups (validateSession)
-- These can enable index-only scans for the exact columns used in auth middleware.
CREATE INDEX IF NOT EXISTS idx_sessions_id_cover
ON "Sessions" (id)
INCLUDE ("UserName", expires_at);


CREATE TABLE IF NOT EXISTS "session" (
    sid VARCHAR(33) PRIMARY KEY NOT NULL,
    sess JSONB NOT NULL,
    expire TimeStamp WITH TIME ZONE NOT NULL,
    username TEXT,
    last_activity TimeStamp WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Add indexes for performance optimization
CREATE INDEX IF NOT EXISTS idx_session_expire ON "session" ("expire");
CREATE INDEX IF NOT EXISTS idx_session_user_id ON "session" ((sess->'user'->>'id'));













-- WebPortal user table

-- todos table for user tasks, with indexes for performance optimization
CREATE TABLE IF NOT EXISTS "todos" (
    "id" SERIAL PRIMARY KEY,
    "username" VARCHAR(50) NOT NULL REFERENCES "Users"("UserName"),
    "title" VARCHAR(255) NOT NULL,
    "description" TEXT,
    "completed" BOOLEAN DEFAULT false,
    "type" VARCHAR(20) DEFAULT 'personal' CHECK ("type" IN ('personal', 'admin')),
    "assigned" BOOLEAN DEFAULT false,
    "assigneduser" VARCHAR(50) DEFAULT 'none',
    "created_at" TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_todos_username ON "todos" USING BTREE ("username");
CREATE INDEX IF NOT EXISTS idx_todos_type ON "todos" USING BTREE ("type");
CREATE INDEX IF NOT EXISTS idx_todos_completed ON "todos" USING BTREE ("completed");
CREATE INDEX IF NOT EXISTS idx_todos_type_completed ON "todos" USING BTREE ("type", "completed");
CREATE INDEX IF NOT EXISTS idx_todos_assigned ON "todos" USING BTREE ("assigned");
CREATE INDEX IF NOT EXISTS idx_todos_assigneduser ON "todos" USING BTREE ("assigneduser");
CREATE INDEX IF NOT EXISTS idx_todos_username_type ON "todos" USING BTREE ("username", "type");
CREATE INDEX IF NOT EXISTS idx_todos_title ON "todos" USING BTREE ("title");
CREATE INDEX IF NOT EXISTS idx_todos_description ON "todos" USING BTREE ("description");





-- Plan upgrade requests table
CREATE TABLE IF NOT EXISTS plan_upgrade_requests (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) REFERENCES "Users"("UserName"),
    curr_role VARCHAR(50) NOT NULL,
    requested_plan VARCHAR(50) NOT NULL,
    req_role VARCHAR(50) NOT NULL,
    reason TEXT NOT NULL,
    experience TEXT,
    portfolio VARCHAR(500),
    linkedin VARCHAR(500),
    github VARCHAR(500),
    additional_info TEXT,
    status VARCHAR(20) DEFAULT 'pending' CHECK (status IN ('pending', 'approved', 'rejected')),
    admin_notes TEXT,
    reviewed_by VARCHAR(255),
    created_at TimeStamp WITH TIME ZONE DEFAULT NOW(),
    updated_at TimeStamp WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_username ON plan_upgrade_requests("username");
CREATE INDEX IF NOT EXISTS idx_status ON plan_upgrade_requests(status);
CREATE INDEX IF NOT EXISTS idx_created_at ON plan_upgrade_requests(created_at);
CREATE INDEX IF NOT EXISTS idx_requested_plan ON plan_upgrade_requests(requested_plan);
CREATE INDEX IF NOT EXISTS idx_status_created_at ON plan_upgrade_requests(status, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_username_status ON plan_upgrade_requests("username", status);
CREATE INDEX IF NOT EXISTS idx_req_role_status ON plan_upgrade_requests("req_role", status);