-- MCP Server ROI Database Schema
-- Run this in your Supabase SQL editor to set up the database

-- Enable UUID extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Projects table
CREATE TABLE IF NOT EXISTS projects (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id UUID, -- Added for user isolation (set by trigger)
  client_name TEXT NOT NULL,
  project_name TEXT NOT NULL,
  industry TEXT NOT NULL CHECK (industry IN (
    'financial_services', 'healthcare', 'retail', 'manufacturing',
    'technology', 'education', 'government', 'other'
  )),
  description TEXT,
  status TEXT DEFAULT 'draft' CHECK (status IN (
    'draft', 'active', 'completed', 'archived'
  )),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Use cases table
CREATE TABLE IF NOT EXISTS use_cases (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id UUID, -- Added for user isolation (set by trigger)
  project_id UUID REFERENCES projects(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  category TEXT NOT NULL CHECK (category IN (
    'automation', 'analytics', 'customer_service', 'operations',
    'sales_marketing', 'hr_recruiting', 'finance_accounting', 'custom'
  )),
  current_state JSONB NOT NULL CHECK (
    current_state ? 'process_time_hours' AND
    current_state ? 'cost_per_transaction' AND
    current_state ? 'error_rate' AND
    current_state ? 'volume_per_month' AND
    current_state ? 'fte_required'
  ),
  future_state JSONB NOT NULL CHECK (
    future_state ? 'automation_percentage' AND
    future_state ? 'time_reduction_percentage' AND
    future_state ? 'error_reduction_percentage' AND
    future_state ? 'scalability_factor'
  ),
  implementation JSONB NOT NULL CHECK (
    implementation ? 'development_hours' AND
    implementation ? 'complexity_score' AND
    implementation ? 'dependencies' AND
    implementation ? 'risk_factors'
  ),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Projections table
CREATE TABLE IF NOT EXISTS projections (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id UUID, -- Added for user isolation (set by trigger)
  project_id UUID REFERENCES projects(id) ON DELETE CASCADE,
  scenario_name TEXT DEFAULT 'Base Case',
  metadata JSONB NOT NULL CHECK (
    metadata ? 'confidence_level' AND
    metadata ? 'assumptions'
  ),
  implementation_costs JSONB NOT NULL CHECK (
    implementation_costs ? 'software_licenses' AND
    implementation_costs ? 'development_hours' AND
    implementation_costs ? 'training_costs' AND
    implementation_costs ? 'infrastructure'
  ),
  timeline_months INTEGER NOT NULL CHECK (timeline_months > 0 AND timeline_months <= 120),
  financial_metrics JSONB NOT NULL CHECK (
    financial_metrics ? 'conservative' AND
    financial_metrics ? 'expected' AND
    financial_metrics ? 'optimistic'
  ),
  calculations JSONB NOT NULL CHECK (
    calculations ? 'total_investment' AND
    calculations ? 'net_present_value' AND
    calculations ? 'internal_rate_of_return' AND
    calculations ? 'payback_period_months' AND
    calculations ? 'five_year_roi' AND
    calculations ? 'break_even_date'
  ),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Simulation results table
CREATE TABLE IF NOT EXISTS simulation_results (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id UUID, -- Added for user isolation (set by trigger)
  projection_id UUID REFERENCES projections(id) ON DELETE CASCADE,
  simulation_type TEXT NOT NULL DEFAULT 'monte_carlo',
  results JSONB NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Actual metrics tracking table
CREATE TABLE IF NOT EXISTS actual_metrics (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id UUID, -- Added for user isolation (set by trigger)
  projection_id UUID REFERENCES projections(id) ON DELETE CASCADE,
  period TEXT NOT NULL, -- e.g., '2024-01', '2024-Q1'
  metrics JSONB NOT NULL CHECK (
    metrics ? 'cost_savings' AND
    metrics ? 'time_savings_hours' AND
    metrics ? 'revenue_increase' AND
    metrics ? 'quality_improvements' AND
    metrics ? 'user_adoption'
  ),
  evidence JSONB DEFAULT '[]'::jsonb,
  tracked_at TIMESTAMPTZ DEFAULT NOW()
);

-- Create indexes for performance
CREATE INDEX idx_projects_client ON projects(client_name);
CREATE INDEX idx_projects_status ON projects(status);
CREATE INDEX idx_projects_industry ON projects(industry);
CREATE INDEX idx_use_cases_project ON use_cases(project_id);
CREATE INDEX idx_use_cases_category ON use_cases(category);
CREATE INDEX idx_projections_project ON projections(project_id);
CREATE INDEX idx_projections_created ON projections(created_at DESC);
CREATE INDEX idx_simulation_results_projection ON simulation_results(projection_id);
CREATE INDEX idx_actual_metrics_projection ON actual_metrics(projection_id);
CREATE INDEX idx_actual_metrics_period ON actual_metrics(period);

-- Create updated_at trigger function
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ language 'plpgsql';

-- Apply updated_at triggers
CREATE TRIGGER update_projects_updated_at BEFORE UPDATE ON projects
  FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_use_cases_updated_at BEFORE UPDATE ON use_cases
  FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_projections_updated_at BEFORE UPDATE ON projections
  FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- Create views for common queries

-- Active projects with latest projection
CREATE VIEW active_projects_summary AS
SELECT 
  p.*,
  latest_proj.id as latest_projection_id,
  latest_proj.calculations->>'five_year_roi' as latest_roi,
  latest_proj.calculations->>'payback_period_months' as latest_payback_months,
  latest_proj.created_at as projection_date,
  COUNT(DISTINCT uc.id) as use_case_count
FROM projects p
LEFT JOIN LATERAL (
  SELECT * FROM projections 
  WHERE project_id = p.id 
  ORDER BY created_at DESC 
  LIMIT 1
) latest_proj ON true
LEFT JOIN use_cases uc ON uc.project_id = p.id
WHERE p.status = 'active'
GROUP BY p.id, latest_proj.id, latest_proj.calculations, latest_proj.created_at;

-- Project performance tracking
CREATE VIEW project_performance AS
SELECT 
  p.id as project_id,
  p.project_name,
  p.client_name,
  proj.id as projection_id,
  proj.calculations->>'five_year_roi' as projected_roi,
  proj.calculations->>'payback_period_months' as projected_payback,
  am.period,
  am.metrics->>'cost_savings' as actual_cost_savings,
  am.metrics->>'user_adoption' as adoption_rate,
  am.tracked_at
FROM projects p
JOIN projections proj ON proj.project_id = p.id
LEFT JOIN actual_metrics am ON am.projection_id = proj.id
ORDER BY p.id, am.period;

-- SECURITY NOTICE:
-- This schema now includes user_id columns for proper data isolation.
-- Row Level Security (RLS) is REQUIRED for production use.
-- 
-- To enable security:
-- 1. Run the migration file: database/001_security_update.sql
-- 2. Ensure Supabase Auth is configured
-- 3. All data access will be automatically filtered by user
--
-- The dangerous GRANT statements have been removed.
-- Users can only access their own data through RLS policies.