-- Company-based multi-tenant architecture migration
-- This creates the companies table and updates users to be company-scoped

-- Create companies table
CREATE TABLE IF NOT EXISTS "companies" (
  "id" text PRIMARY KEY NOT NULL,
  "name" text NOT NULL,
  "package" text NOT NULL DEFAULT 'starter',
  "max_users" integer NOT NULL DEFAULT 5,
  "owner_id" text NOT NULL,
  "owned_tokens" bigint NOT NULL DEFAULT 0,
  "financial_data" jsonb DEFAULT '{}'::jsonb,
  "payment_method" jsonb,
  "stripe_customer_id" text,
  "created_at" timestamp DEFAULT now(),
  "updated_at" timestamp DEFAULT now()
);

-- Create unique index for owner (enforces single owner per company via application logic)
CREATE UNIQUE INDEX IF NOT EXISTS "companies_owner_idx" ON "companies" ("owner_id");

-- Add company relationship to users
ALTER TABLE "user" ADD COLUMN IF NOT EXISTS "company_id" text;
ALTER TABLE "user" ADD COLUMN IF NOT EXISTS "is_owner" boolean DEFAULT false NOT NULL;

-- Create foreign key constraint for company_id
DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.table_constraints
    WHERE constraint_name = 'user_company_id_companies_id_fk'
    AND table_name = 'user'
  ) THEN
    ALTER TABLE "user" ADD CONSTRAINT "user_company_id_companies_id_fk" 
      FOREIGN KEY ("company_id") REFERENCES "companies"("id") ON DELETE CASCADE;
  END IF;
END $$;

-- Create index on company_id for performance
CREATE INDEX IF NOT EXISTS "user_company_id_idx" ON "user" ("company_id");

-- Create index on is_owner for quick owner lookups
CREATE INDEX IF NOT EXISTS "user_is_owner_idx" ON "user" ("is_owner");

-- Update existing users to require company_id
-- Note: In production, you'd need to handle existing users appropriately
-- For now, we allow NULL temporarily during migration, then enforce via application logic

-- Note: After migrating existing users, you may want to add:
-- ALTER TABLE "user" ALTER COLUMN "company_id" SET NOT NULL;
