export const repositoriesTableSql = `
CREATE TABLE IF NOT EXISTS repositories (id INTEGER PRIMARY KEY, workspace_id INTEGER NOT NULL, name TEXT NOT NULL, absolute_path TEXT NOT NULL, relative_path TEXT NOT NULL, package_name TEXT, package_version TEXT, dependencies_json TEXT DEFAULT '{}', package_public_surface_json TEXT, environment_declarations_json TEXT DEFAULT '{"schema":"service-flow/environment-declarations@1","allowedKeys":[],"status":"not_applicable","reason":null,"recordCap":32,"total":0,"shown":0,"omitted":0,"declarations":[]}', kind TEXT NOT NULL, is_git_repo INTEGER NOT NULL, last_indexed_at TEXT, index_status TEXT DEFAULT 'pending', error_count INTEGER DEFAULT 0, fingerprint TEXT, fact_generation INTEGER NOT NULL DEFAULT 0, graph_generation INTEGER NOT NULL DEFAULT 0, graph_stale_reason TEXT, graph_stale_at TEXT, fact_analyzer_version TEXT, UNIQUE(workspace_id, absolute_path), FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE);
`;

export const schemaTablesSql = `
CREATE TABLE IF NOT EXISTS workspaces (id INTEGER PRIMARY KEY, root_path TEXT UNIQUE NOT NULL, db_path TEXT NOT NULL, created_at TEXT NOT NULL, updated_at TEXT NOT NULL);
${repositoriesTableSql}
CREATE TABLE IF NOT EXISTS files (id INTEGER PRIMARY KEY, repo_id INTEGER NOT NULL, relative_path TEXT NOT NULL, extension TEXT NOT NULL, sha256 TEXT NOT NULL, size_bytes INTEGER NOT NULL, last_indexed_at TEXT NOT NULL, UNIQUE(repo_id, relative_path), FOREIGN KEY(repo_id) REFERENCES repositories(id) ON DELETE CASCADE);
CREATE TABLE IF NOT EXISTS cds_requires (id INTEGER PRIMARY KEY, repo_id INTEGER NOT NULL, alias TEXT NOT NULL, kind TEXT, model TEXT, destination TEXT, service_path TEXT, request_timeout INTEGER, raw_json TEXT NOT NULL, FOREIGN KEY(repo_id) REFERENCES repositories(id) ON DELETE CASCADE);
CREATE TABLE IF NOT EXISTS cds_services (id INTEGER PRIMARY KEY, repo_id INTEGER NOT NULL, namespace TEXT, service_name TEXT NOT NULL, qualified_name TEXT NOT NULL, service_path TEXT NOT NULL, is_extend INTEGER NOT NULL, source_file TEXT NOT NULL, source_line INTEGER NOT NULL, extension_local_ref TEXT, extension_imported_symbol TEXT, extension_local_alias TEXT, extension_module_specifier TEXT, extension_import_kind TEXT, extension_base_service_id INTEGER, extension_base_status TEXT, FOREIGN KEY(repo_id) REFERENCES repositories(id) ON DELETE CASCADE, FOREIGN KEY(extension_base_service_id) REFERENCES cds_services(id) ON DELETE SET NULL);
CREATE TABLE IF NOT EXISTS cds_operations (id INTEGER PRIMARY KEY, service_id INTEGER NOT NULL, operation_type TEXT NOT NULL, operation_name TEXT NOT NULL, operation_path TEXT NOT NULL, params_json TEXT NOT NULL, return_type TEXT, source_file TEXT NOT NULL, source_line INTEGER NOT NULL, provenance TEXT NOT NULL DEFAULT 'direct', base_operation_id INTEGER, FOREIGN KEY(service_id) REFERENCES cds_services(id) ON DELETE CASCADE, FOREIGN KEY(base_operation_id) REFERENCES cds_operations(id) ON DELETE SET NULL);
CREATE TABLE IF NOT EXISTS symbols (id INTEGER PRIMARY KEY, repo_id INTEGER NOT NULL, file_id INTEGER, kind TEXT NOT NULL, name TEXT NOT NULL, qualified_name TEXT NOT NULL, exported INTEGER NOT NULL, start_line INTEGER NOT NULL, end_line INTEGER NOT NULL, start_offset INTEGER, end_offset INTEGER, source_file TEXT, exported_name TEXT, evidence_json TEXT, FOREIGN KEY(repo_id) REFERENCES repositories(id) ON DELETE CASCADE, FOREIGN KEY(file_id) REFERENCES files(id) ON DELETE CASCADE);
CREATE TABLE IF NOT EXISTS handler_classes (id INTEGER PRIMARY KEY, repo_id INTEGER NOT NULL, symbol_id INTEGER, class_name TEXT NOT NULL, source_file TEXT NOT NULL, source_line INTEGER NOT NULL, FOREIGN KEY(repo_id) REFERENCES repositories(id) ON DELETE CASCADE, FOREIGN KEY(symbol_id) REFERENCES symbols(id) ON DELETE SET NULL);
CREATE TABLE IF NOT EXISTS handler_methods (id INTEGER PRIMARY KEY, handler_class_id INTEGER NOT NULL, method_name TEXT NOT NULL, decorator_kind TEXT NOT NULL, decorator_value TEXT, decorator_raw_expression TEXT NOT NULL, decorator_resolution_json TEXT NOT NULL DEFAULT '{}', source_file TEXT NOT NULL, source_line INTEGER NOT NULL, FOREIGN KEY(handler_class_id) REFERENCES handler_classes(id) ON DELETE CASCADE);
CREATE TABLE IF NOT EXISTS handler_registrations (id INTEGER PRIMARY KEY, repo_id INTEGER NOT NULL, handler_class_id INTEGER, class_name TEXT, import_source TEXT, registration_file TEXT NOT NULL, registration_line INTEGER NOT NULL, registration_kind TEXT NOT NULL, confidence REAL NOT NULL, FOREIGN KEY(repo_id) REFERENCES repositories(id) ON DELETE CASCADE, FOREIGN KEY(handler_class_id) REFERENCES handler_classes(id) ON DELETE SET NULL);
CREATE TABLE IF NOT EXISTS service_bindings (id INTEGER PRIMARY KEY, repo_id INTEGER NOT NULL, symbol_id INTEGER, variable_name TEXT NOT NULL, alias TEXT, alias_expr TEXT, destination_expr TEXT, service_path_expr TEXT, is_dynamic INTEGER NOT NULL, placeholders_json TEXT NOT NULL, source_file TEXT NOT NULL, source_line INTEGER NOT NULL, binding_site_start_offset INTEGER, binding_site_end_offset INTEGER, owner_resolution TEXT NOT NULL DEFAULT 'legacy_unknown' CHECK(owner_resolution IN ('owned_exact','ownerless_file_scope','legacy_unknown')), helper_chain_json TEXT, FOREIGN KEY(repo_id) REFERENCES repositories(id) ON DELETE CASCADE, FOREIGN KEY(symbol_id) REFERENCES symbols(id) ON DELETE SET NULL);
CREATE TABLE IF NOT EXISTS outbound_calls (id INTEGER PRIMARY KEY, repo_id INTEGER NOT NULL, source_symbol_id INTEGER, call_type TEXT NOT NULL, service_binding_id INTEGER, method TEXT, operation_path_expr TEXT, query_entity TEXT, event_name_expr TEXT, event_skeleton_signature TEXT, event_skeleton_json TEXT, payload_summary TEXT, source_file TEXT NOT NULL, source_line INTEGER NOT NULL, call_site_start_offset INTEGER, call_site_end_offset INTEGER, confidence REAL NOT NULL, unresolved_reason TEXT, local_service_name TEXT, local_service_lookup TEXT, alias_chain_json TEXT, evidence_json TEXT, external_target_kind TEXT, external_target_id TEXT, external_target_label TEXT, external_target_dynamic INTEGER NOT NULL DEFAULT 0, FOREIGN KEY(repo_id) REFERENCES repositories(id) ON DELETE CASCADE, FOREIGN KEY(source_symbol_id) REFERENCES symbols(id) ON DELETE SET NULL, FOREIGN KEY(service_binding_id) REFERENCES service_bindings(id) ON DELETE SET NULL);
CREATE TABLE IF NOT EXISTS generated_constants (id INTEGER PRIMARY KEY, repo_id INTEGER NOT NULL, source_file TEXT NOT NULL, source_line INTEGER NOT NULL, name TEXT NOT NULL, container_name TEXT, member_name TEXT, value TEXT, constant_kind TEXT NOT NULL, exported INTEGER NOT NULL, stable INTEGER NOT NULL, resolution_status TEXT NOT NULL CHECK(resolution_status IN ('resolved','refused')), unresolved_reason TEXT, declaration_start_offset INTEGER NOT NULL, declaration_end_offset INTEGER NOT NULL, value_start_offset INTEGER NOT NULL, value_end_offset INTEGER NOT NULL, FOREIGN KEY(repo_id) REFERENCES repositories(id) ON DELETE CASCADE);
CREATE TABLE IF NOT EXISTS symbol_calls (id INTEGER PRIMARY KEY, repo_id INTEGER NOT NULL, caller_symbol_id INTEGER NOT NULL, callee_symbol_id INTEGER, callee_expression TEXT NOT NULL, import_source TEXT, source_file TEXT NOT NULL, source_line INTEGER NOT NULL, call_site_start_offset INTEGER, call_site_end_offset INTEGER, call_role TEXT NOT NULL DEFAULT 'legacy_unknown', status TEXT NOT NULL, confidence REAL NOT NULL, evidence_json TEXT NOT NULL, unresolved_reason TEXT, FOREIGN KEY(repo_id) REFERENCES repositories(id) ON DELETE CASCADE, FOREIGN KEY(caller_symbol_id) REFERENCES symbols(id) ON DELETE CASCADE, FOREIGN KEY(callee_symbol_id) REFERENCES symbols(id) ON DELETE SET NULL);
CREATE TABLE IF NOT EXISTS graph_edges (id INTEGER PRIMARY KEY, workspace_id INTEGER NOT NULL, edge_type TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'unresolved', from_kind TEXT NOT NULL, from_id TEXT NOT NULL, to_kind TEXT NOT NULL, to_id TEXT NOT NULL, confidence REAL NOT NULL, evidence_json TEXT NOT NULL, is_dynamic INTEGER NOT NULL, unresolved_reason TEXT, generation INTEGER NOT NULL DEFAULT 0, FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE);
CREATE TABLE IF NOT EXISTS index_runs (id INTEGER PRIMARY KEY, workspace_id INTEGER NOT NULL, started_at TEXT NOT NULL, finished_at TEXT, status TEXT NOT NULL, repo_count INTEGER NOT NULL, file_count INTEGER NOT NULL, diagnostic_count INTEGER NOT NULL, error_message TEXT, owner_pid INTEGER, FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE);
CREATE TABLE IF NOT EXISTS diagnostics (id INTEGER PRIMARY KEY, repo_id INTEGER, file_id INTEGER, severity TEXT NOT NULL, code TEXT NOT NULL, message TEXT NOT NULL, source_file TEXT, source_line INTEGER, FOREIGN KEY(repo_id) REFERENCES repositories(id) ON DELETE CASCADE, FOREIGN KEY(file_id) REFERENCES files(id) ON DELETE SET NULL);
`;

export const schemaIndexesSql = `
CREATE INDEX IF NOT EXISTS idx_repo_name ON repositories(name);
CREATE INDEX IF NOT EXISTS idx_service_path ON cds_services(service_path);
CREATE INDEX IF NOT EXISTS idx_extension_import ON cds_services(extension_module_specifier, extension_imported_symbol, is_extend);
CREATE INDEX IF NOT EXISTS idx_operation_name ON cds_operations(operation_name, operation_path);
CREATE INDEX IF NOT EXISTS idx_operation_service ON cds_operations(service_id);
CREATE INDEX IF NOT EXISTS idx_operation_base ON cds_operations(base_operation_id);
CREATE INDEX IF NOT EXISTS idx_handler_method_class ON handler_methods(handler_class_id);
CREATE INDEX IF NOT EXISTS idx_calls_repo ON outbound_calls(repo_id, call_type);
CREATE INDEX IF NOT EXISTS idx_outbound_event_skeleton ON outbound_calls(event_skeleton_signature,call_type,repo_id);
CREATE INDEX IF NOT EXISTS idx_generated_constant_name ON generated_constants(repo_id,source_file,name);
CREATE UNIQUE INDEX IF NOT EXISTS uq_generated_constant_site ON generated_constants(repo_id,source_file,name,declaration_start_offset,declaration_end_offset);
CREATE INDEX IF NOT EXISTS idx_symbol_calls_caller ON symbol_calls(repo_id, caller_symbol_id);
CREATE INDEX IF NOT EXISTS idx_outbound_call_site ON outbound_calls(repo_id, source_file, call_site_start_offset, call_site_end_offset, call_type);
CREATE INDEX IF NOT EXISTS idx_symbol_call_site_role ON symbol_calls(repo_id, source_file, call_site_start_offset, call_site_end_offset, call_role);
-- Keep status after the ownership prefix: service ownership leaves it
-- unconstrained, while resolved-implementation lookups still use all columns.
CREATE INDEX IF NOT EXISTS idx_graph_edge_lookup ON graph_edges(edge_type, from_kind, from_id, status, to_id);
CREATE INDEX IF NOT EXISTS idx_graph_edge_from ON graph_edges(from_kind, from_id);
-- Relative symbol resolution uses a three-way name OR. Separate indexes let
-- SQLite use its multi-index OR plan; source_file<>? cannot sit in the prefix.
CREATE INDEX IF NOT EXISTS idx_symbol_repo_qualified ON symbols(repo_id, qualified_name);
CREATE INDEX IF NOT EXISTS idx_symbol_repo_name ON symbols(repo_id, name);
CREATE INDEX IF NOT EXISTS idx_symbol_repo_exported_name ON symbols(repo_id, exported, exported_name);
CREATE INDEX IF NOT EXISTS idx_service_binding_site ON service_bindings(repo_id,source_file,variable_name,binding_site_start_offset,binding_site_end_offset);
CREATE UNIQUE INDEX IF NOT EXISTS uq_service_binding_exact_site ON service_bindings(repo_id,source_file,variable_name,binding_site_start_offset,binding_site_end_offset) WHERE binding_site_start_offset IS NOT NULL AND binding_site_end_offset IS NOT NULL;
CREATE VIRTUAL TABLE IF NOT EXISTS search_index USING fts5(kind, name, path, repo);
`;

export const schemaSql = `${schemaTablesSql}\n${schemaIndexesSql}`;