Query Result Types

export interface QueryTab {
  id: string
  name: string
  content: string
  isModified: boolean
  locked: boolean
  serverId?: string
  database?: string
}

const tab: QueryTab = {
  id: "tab-1",
  name: "User Analysis",
  content: "SELECT * FROM users WHERE active = true;",
  isModified: true,
  locked: false,
  serverId: "conn-123",
  database: "myapp_production"
}

export interface SavedQuery {
  id: string
  name: string
  content: string
  createdAt: Date
  lastModified: Date
  category?: "select" | "insert" | "update" | "delete"
}

const savedQuery: SavedQuery = {
  id: "query-456",
  name: "Active Users Report",
  content: "SELECT id, name, email FROM users WHERE active = true ORDER BY created_at DESC;",
  createdAt: new Date("2024-01-15T10:00:00Z"),
  lastModified: new Date("2024-02-20T14:30:00Z"),
  category: "select"
}

export interface QueryResult {
  type: "select" | "insert" | "update" | "delete" | "error" | "success"
  columns?: string[]
  rows?: Record<string, any>[]
  rowCount?: number
  affectedRows?: number
  executionTime: number
  error?: string
  query: string
  fromCache?: boolean
  cacheHits?: number
  isStale?: boolean
  cacheAge?: number
  tableNames?: string[]
  statement?: string
}

const selectResult: QueryResult = {
  type: "select",
  columns: ["id", "name", "email"],
  rows: [
    { id: 1, name: "Alice", email: "alice@example.com" },
    { id: 2, name: "Bob", email: "bob@example.com" }
  ],
  rowCount: 2,
  executionTime: 12,
  query: "SELECT id, name, email FROM users LIMIT 2;",
  fromCache: false,
  tableNames: ["users"]
}

const insertResult: QueryResult = {
  type: "insert",
  rowCount: 1,
  affectedRows: 1,
  executionTime: 8,
  query: "INSERT INTO users (name, email) VALUES ('Charlie', 'charlie@example.com');",
  fromCache: false
}

const errorResult: QueryResult = {
  type: "error",
  error: "relation \"nonexistent_table\" does not exist",
  executionTime: 3,
  query: "SELECT * FROM nonexistent_table;"
}

const cachedResult: QueryResult = {
  type: "select",
  columns: ["count"],
  rows: [{ count: "150" }],
  rowCount: 1,
  executionTime: 2, // Much faster due to cache
  query: "SELECT COUNT(*) FROM users;",
  fromCache: true,
  cacheHits: 5,
  cacheAge: 15000, // 15 seconds old
  isStale: false,
  tableNames: ["users"]
}

export interface QueryExecution {
  id: string
  query: string
  results: QueryResult[]
  executionTime: number
  timestamp: Date
  serverId: string
  database: string
}

const execution: QueryExecution = {
  id: "exec-789",
  query: "SELECT * FROM users WHERE active = true;",
  results: [
    {
      type: "select",
      columns: ["id", "name", "email"],
      rows: [...],
      rowCount: 50,
      executionTime: 15,
      query: "SELECT * FROM users WHERE active = true;"
    }
  ],
  executionTime: 15,
  timestamp: new Date("2024-03-20T10:30:00Z"),
  serverId: "conn-123",
  database: "myapp_production"
}

const multiExecution: QueryExecution = {
  id: "exec-790",
  query: "SELECT COUNT(*) FROM users; SELECT COUNT(*) FROM orders;",
  results: [
    {
      type: "select",
      columns: ["count"],
      rows: [{ count: "150" }],
      rowCount: 1,
      executionTime: 8,
      query: "SELECT COUNT(*) FROM users;"
    },
    {
      type: "select",
      columns: ["count"],
      rows: [{ count: "450" }],
      rowCount: 1,
      executionTime: 6,
      query: "SELECT COUNT(*) FROM orders;"
    }
  ],
  executionTime: 14,
  timestamp: new Date("2024-03-20T10:31:00Z"),
  serverId: "conn-123",
  database: "myapp_production"
}

export interface QueryTemplate {
  id: string
  name: string
  description: string
  content: string
  category: "select" | "insert" | "update" | "delete" | "ddl" | "utility"
}

const templates: QueryTemplate[] = [
  {
    id: "tmpl-1",
    name: "Select All from Table",
    description: "Select all rows from a table with optional LIMIT",
    content: "SELECT * FROM {{table_name}} LIMIT {{limit}};",
    category: "select"
  },
  {
    id: "tmpl-2",
    name: "Create Table",
    description: "Create a new table with basic columns",
    content: `CREATE TABLE {{table_name}} (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);`,
    category: "ddl"
  }
]

function processQueryResult(result: QueryResult) {
  if (result.type === "error") {
    console.error(`Query failed: ${result.error}`);
    return;
  }
  
  if (result.type === "select" && result.rows) {
    console.log(`Retrieved ${result.rowCount} rows in ${result.executionTime}ms`);
    if (result.fromCache) {
      console.log(`Result served from cache (${result.cacheHits} hits)`);
    }
    return result.rows;
  }
  
  if (result.type === "insert" || result.type === "update" || result.type === "delete") {
    console.log(`${result.affectedRows} rows affected in ${result.executionTime}ms`);
  }
}

class QueryHistoryManager {
  private history: QueryExecution[] = [];
  
  addExecution(execution: QueryExecution) {
    this.history.unshift(execution);
    // Keep last 100 executions
    if (this.history.length > 100) {
      this.history = this.history.slice(0, 100);
    }
  }
  
  getRecentQueries(limit: number = 10): QueryExecution[] {
    return this.history.slice(0, limit);
  }
  
  getFailedQueries(): QueryExecution[] {
    return this.history.filter(exec => 
      exec.results.some(result => result.type === "error")
    );
  }
}

function expandTemplate(template: QueryTemplate, vars: Record<string, string>): string {
  let sql = template.content;
  for (const [key, value] of Object.entries(vars)) {
    sql = sql.replace(new RegExp(`{{${key}}}`, 'g'), value);
  }
  return sql;
}

// Usage
const template: QueryTemplate = {
  id: "tmpl-1",
  name: "Select Users",
  description: "Select users by status",
  content: "SELECT * FROM {{table}} WHERE {{column}} = '{{value}}';",
  category: "select"
};

const query = expandTemplate(template, {
  table: "users",
  column: "status",
  value: "active"
});
// Result: "SELECT * FROM users WHERE status = 'active';"

function invalidateCacheForTable(tableName: string, cachedResults: QueryResult[]) {
  return cachedResults.filter(result => 
    !result.tableNames?.includes(tableName)
  );
}