import { Migration } from '../migrate.js'

export const up: Migration['up'] = async (db) => {
  // MFA Methods table - per NIST SP 800-53 IA-2
  await db.query(`
    CREATE TABLE IF NOT EXISTS mfa_methods (
      id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
      user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
      type VARCHAR(50) NOT NULL CHECK (type IN ('totp', 'fido2', 'sms', 'email')),
      name VARCHAR(255) NOT NULL,
      secret TEXT, -- Encrypted TOTP secret or FIDO2 credential ID
      backup_codes_hash TEXT, -- Hashed backup codes
      enabled BOOLEAN NOT NULL DEFAULT false,
      created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
      updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
      last_used TIMESTAMP WITH TIME ZONE,
      UNIQUE(user_id, type)
    )
  `)

  // MFA Challenges table - for challenge-response authentication
  await db.query(`
    CREATE TABLE IF NOT EXISTS mfa_challenges (
      id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
      challenge_id VARCHAR(255) UNIQUE NOT NULL,
      user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
      method VARCHAR(50) NOT NULL,
      expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
      verified BOOLEAN NOT NULL DEFAULT false,
      verified_at TIMESTAMP WITH TIME ZONE,
      created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
    )
  `)

  // Enhanced RBAC - per NIST SP 800-53 AC-2, AC-3
  await db.query(`
    CREATE TABLE IF NOT EXISTS roles (
      id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
      name VARCHAR(255) UNIQUE NOT NULL,
      description TEXT,
      permissions JSONB DEFAULT '[]'::jsonb,
      is_system BOOLEAN NOT NULL DEFAULT false,
      created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
      updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
    )
  `)

  await db.query(`
    CREATE TABLE IF NOT EXISTS user_roles (
      id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
      user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
      role_id UUID NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
      granted_by UUID REFERENCES users(id),
      granted_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
      expires_at TIMESTAMP WITH TIME ZONE,
      metadata JSONB DEFAULT '{}'::jsonb,
      UNIQUE(user_id, role_id)
    )
  `)

  // Permissions table for fine-grained access control (ABAC)
  await db.query(`
    CREATE TABLE IF NOT EXISTS permissions (
      id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
      name VARCHAR(255) UNIQUE NOT NULL,
      resource_type VARCHAR(100) NOT NULL,
      action VARCHAR(100) NOT NULL,
      conditions JSONB DEFAULT '{}'::jsonb,
      description TEXT,
      created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
    )
  `)

  await db.query(`
    CREATE TABLE IF NOT EXISTS role_permissions (
      id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
      role_id UUID NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
      permission_id UUID NOT NULL REFERENCES permissions(id) ON DELETE CASCADE,
      conditions JSONB DEFAULT '{}'::jsonb,
      UNIQUE(role_id, permission_id)
    )
  `)

  // Sessions table - per NIST SP 800-53 AC-12
  await db.query(`
    CREATE TABLE IF NOT EXISTS sessions (
      id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
      user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
      session_token VARCHAR(255) UNIQUE NOT NULL,
      ip_address INET,
      user_agent TEXT,
      classification_level VARCHAR(50) DEFAULT 'UNCLASSIFIED' 
        CHECK (classification_level IN ('UNCLASSIFIED', 'CUI', 'CONFIDENTIAL', 'SECRET', 'TOP_SECRET')),
      created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
      expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
      last_activity TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
      revoked BOOLEAN NOT NULL DEFAULT false,
      revoked_at TIMESTAMP WITH TIME ZONE,
      mfa_verified BOOLEAN NOT NULL DEFAULT false
    )
  `)

  // Session activity log - for audit trail
  await db.query(`
    CREATE TABLE IF NOT EXISTS session_activity (
      id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
      session_id UUID NOT NULL REFERENCES sessions(id) ON DELETE CASCADE,
      action VARCHAR(100) NOT NULL,
      resource_type VARCHAR(100),
      resource_id UUID,
      ip_address INET,
      user_agent TEXT,
      created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
    )
  `)

  // Indexes for performance
  await db.query(`
    CREATE INDEX IF NOT EXISTS idx_mfa_methods_user_id ON mfa_methods(user_id)
  `)

  await db.query(`
    CREATE INDEX IF NOT EXISTS idx_mfa_methods_enabled ON mfa_methods(user_id, enabled) WHERE enabled = true
  `)

  await db.query(`
    CREATE INDEX IF NOT EXISTS idx_mfa_challenges_challenge_id ON mfa_challenges(challenge_id)
  `)

  await db.query(`
    CREATE INDEX IF NOT EXISTS idx_mfa_challenges_user_id ON mfa_challenges(user_id)
  `)

  await db.query(`
    CREATE INDEX IF NOT EXISTS idx_sessions_user_id ON sessions(user_id)
  `)

  await db.query(`
    CREATE INDEX IF NOT EXISTS idx_sessions_token ON sessions(session_token)
  `)

  await db.query(`
    CREATE INDEX IF NOT EXISTS idx_sessions_expires_at ON sessions(expires_at) WHERE revoked = false
  `)

  await db.query(`
    CREATE INDEX IF NOT EXISTS idx_session_activity_session_id ON session_activity(session_id)
  `)

  await db.query(`
    CREATE INDEX IF NOT EXISTS idx_user_roles_user_id ON user_roles(user_id)
  `)

  await db.query(`
    CREATE INDEX IF NOT EXISTS idx_role_permissions_role_id ON role_permissions(role_id)
  `)

  // Create default system roles
  await db.query(`
    INSERT INTO roles (name, description, permissions, is_system) VALUES
    ('SYSTEM_ADMIN', 'System Administrator - Full access to all resources', 
     '["*"]'::jsonb, true),
    ('SECURITY_ADMIN', 'Security Administrator - Security and compliance management',
     '["security:*", "audit:*", "compliance:*"]'::jsonb, true),
    ('TENANT_ADMIN', 'Tenant Administrator - Full access within tenant',
     '["tenant:*"]'::jsonb, true),
    ('USER', 'Standard User - Basic access',
     '["resources:read", "resources:create"]'::jsonb, true),
    ('VIEWER', 'Viewer - Read-only access',
     '["resources:read"]'::jsonb, true)
    ON CONFLICT (name) DO NOTHING
  `)

  // Update timestamp trigger for new tables
  await db.query(`
    CREATE TRIGGER update_mfa_methods_updated_at BEFORE UPDATE ON mfa_methods
      FOR EACH ROW EXECUTE FUNCTION update_updated_at_column()
  `)

  await db.query(`
    CREATE TRIGGER update_roles_updated_at BEFORE UPDATE ON roles
      FOR EACH ROW EXECUTE FUNCTION update_updated_at_column()
  `)
}

export const down: Migration['down'] = async (db) => {
  await db.query('DROP TABLE IF EXISTS session_activity CASCADE')
  await db.query('DROP TABLE IF EXISTS sessions CASCADE')
  await db.query('DROP TABLE IF EXISTS role_permissions CASCADE')
  await db.query('DROP TABLE IF EXISTS permissions CASCADE')
  await db.query('DROP TABLE IF EXISTS user_roles CASCADE')
  await db.query('DROP TABLE IF EXISTS roles CASCADE')
  await db.query('DROP TABLE IF EXISTS mfa_challenges CASCADE')
  await db.query('DROP TABLE IF EXISTS mfa_methods CASCADE')
}

