supabase-schema-production.sql

-- CloudPilot AI Production Schema
-- Run this in Supabase SQL Editor
-- All costs in INR, timestamps in UTC (display as IST in UI)

-- Users with 2FA support
CREATE TABLE IF NOT EXISTS users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  email TEXT UNIQUE NOT NULL,
  password_hash TEXT NOT NULL,
  company TEXT,
  role TEXT CHECK (role IN ('devops','finance','admin')) NOT NULL DEFAULT 'devops',
  twofa_enabled BOOLEAN NOT NULL DEFAULT FALSE,
  twofa_secret TEXT, -- store server-side only; do not expose to client
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);

-- Cloud connections (dummy keys, masked)
CREATE TABLE IF NOT EXISTS cloud_connections (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  provider TEXT NOT NULL CHECK (provider IN ('aws','gcp','azure')),
  api_key_masked TEXT NOT NULL,      -- e.g., AKIA************7H2Q
  sandbox BOOLEAN NOT NULL DEFAULT TRUE,
  connected_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_cloud_connections_user ON cloud_connections(user_id);

-- Resource inventory (instances metadata)
CREATE TABLE IF NOT EXISTS resources (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  provider TEXT NOT NULL CHECK (provider IN ('aws','gcp','azure')),
  instance_id TEXT NOT NULL,
  instance_type TEXT NOT NULL,       -- e.g., t3.medium, e2-standard-2
  region TEXT NOT NULL,              -- e.g., ap-south-1, asia-south1
  state TEXT NOT NULL CHECK (state IN ('running','stopped','terminated')) DEFAULT 'running',
  hourly_inr DOUBLE PRECISION NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE UNIQUE INDEX IF NOT EXISTS idx_resources_user_instance ON resources(user_id, instance_id);

-- Time-series metrics (5–10s ticks)
CREATE TABLE IF NOT EXISTS cloud_metrics (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  timestamp TIMESTAMPTZ NOT NULL,
  provider TEXT NOT NULL CHECK (provider IN ('aws','gcp','azure')),
  instance_id TEXT NOT NULL,
  cpu DOUBLE PRECISION NOT NULL,         -- %
  memory DOUBLE PRECISION NOT NULL,      -- %
  network_in DOUBLE PRECISION NOT NULL,  -- MB/s
  network_out DOUBLE PRECISION NOT NULL, -- MB/s
  cost_inr DOUBLE PRECISION NOT NULL,    -- per tick or delta
  state TEXT NOT NULL CHECK (state IN ('running','stopped','terminated')) DEFAULT 'running'
);

CREATE INDEX IF NOT EXISTS idx_cloud_metrics_user_ts ON cloud_metrics(user_id, timestamp);
CREATE INDEX IF NOT EXISTS idx_cloud_metrics_user_instance ON cloud_metrics(user_id, instance_id);

-- AI suggestions persistence
CREATE TABLE IF NOT EXISTS ai_suggestions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  time_range tstzrange NOT NULL,
  context_hash TEXT NOT NULL,
  suggestions JSONB NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_ai_suggestions_user ON ai_suggestions(user_id);
CREATE INDEX IF NOT EXISTS idx_ai_suggestions_time ON ai_suggestions USING GIST (time_range);

-- Actions log (auditable)
CREATE TABLE IF NOT EXISTS actions_log (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  action_type TEXT NOT NULL CHECK (action_type IN ('stop','terminate','downsize')),
  provider TEXT CHECK (provider IN ('aws','gcp','azure')),
  instance_id TEXT NOT NULL,
  previous_type TEXT,
  new_type TEXT,
  otp_verified BOOLEAN NOT NULL DEFAULT FALSE,
  requested_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  executed_at TIMESTAMPTZ
);

CREATE INDEX IF NOT EXISTS idx_actions_log_user_time ON actions_log(user_id, requested_at);

-- Seed users (with bcrypt hashes) + connections + resources + minimal metrics
-- Users (replace password_hash with real bcrypt hashes)
INSERT INTO users (name, email, password_hash, company, role, twofa_enabled)
VALUES
('Ravi Sharma', 'ravi@example.com', '$2b$10$2dO6E9a3PZp3N4GfK2oFeOgV6D7zC3q/ysrBzGkUlbRcbYkYtHkF2', 'TechNova', 'devops', TRUE),
('Ananya Iyer', 'ananya@example.com', '$2b$10$GXum1FkK8yVYIhVRCaQlS.7m6rszM2UO2LQnX0v6sZ1eqNUoWb02y', 'CloudWorks', 'finance', TRUE)
ON CONFLICT (email) DO NOTHING;

-- Cloud connections (assume Ravi already connected AWS+GCP)
INSERT INTO cloud_connections (user_id, provider, api_key_masked, sandbox)
VALUES
((SELECT id FROM users WHERE email='ravi@example.com'), 'aws', 'AKIA************7H2Q', TRUE),
((SELECT id FROM users WHERE email='ravi@example.com'), 'gcp', 'AIza*******************9d3', TRUE)
ON CONFLICT DO NOTHING;

-- Resources (instances for Ravi)
INSERT INTO resources (user_id, provider, instance_id, instance_type, region, state, hourly_inr)
VALUES
((SELECT id FROM users WHERE email='ravi@example.com'), 'aws', 'i-0e4acfa45d3', 't3.medium', 'ap-south-1', 'running', 12.50),
((SELECT id FROM users WHERE email='ravi@example.com'), 'aws', 'i-0b9fdc12aa8', 'm5.large', 'ap-south-1', 'running', 28.00),
((SELECT id FROM users WHERE email='ravi@example.com'), 'gcp', 'gcp-vm-34f2', 'e2-standard-2', 'asia-south1', 'running', 10.80),
((SELECT id FROM users WHERE email='ravi@example.com'), 'gcp', 'gcp-vm-55b1', 'n2-standard-4', 'asia-south1', 'running', 25.60)
ON CONFLICT (user_id, instance_id) DO NOTHING;

-- Minimal metrics sample (2 ticks, 5s apart), real demo should stream/append every 5-10s
INSERT INTO cloud_metrics (user_id, timestamp, provider, instance_id, cpu, memory, network_in, network_out, cost_inr, state)
VALUES
((SELECT id FROM users WHERE email='ravi@example.com'), NOW() - INTERVAL '10 seconds', 'aws', 'i-0e4acfa45d3', 36.2, 58.1, 120.5, 98.3, 0.17, 'running'),
((SELECT id FROM users WHERE email='ravi@example.com'), NOW() - INTERVAL '5 seconds',  'aws', 'i-0e4acfa45d3', 39.1, 60.0, 122.0, 99.1, 0.17, 'running'),

((SELECT id FROM users WHERE email='ravi@example.com'), NOW() - INTERVAL '10 seconds', 'aws', 'i-0b9fdc12aa8', 44.0, 70.2, 130.2, 110.8, 0.39, 'running'),
((SELECT id FROM users WHERE email='ravi@example.com'), NOW() - INTERVAL '5 seconds',  'aws', 'i-0b9fdc12aa8', 46.6, 72.3, 132.5, 112.1, 0.39, 'running'),

((SELECT id FROM users WHERE email='ravi@example.com'), NOW() - INTERVAL '10 seconds', 'gcp', 'gcp-vm-34f2', 29.4, 50.8, 90.5, 85.2, 0.15, 'running'),
((SELECT id FROM users WHERE email='ravi@example.com'), NOW() - INTERVAL '5 seconds',  'gcp', 'gcp-vm-34f2', 31.7, 52.4, 92.1, 87.5, 0.15, 'running')
ON CONFLICT DO NOTHING;

-- Daily aggregate view for faster finance queries
CREATE MATERIALIZED VIEW IF NOT EXISTS cloud_cost_daily AS
SELECT
  user_id,
  provider,
  date_trunc('day', timestamp) AS day,
  SUM(cost_inr) AS cost_inr_day
FROM cloud_metrics
GROUP BY 1,2,3;

CREATE INDEX IF NOT EXISTS idx_cloud_cost_daily_user_day ON cloud_cost_daily(user_id, day);