-- ============================================================================
-- SQLite Database Schema Generator
-- Generated from RDF ontology via SPARQL transformation
-- ============================================================================

-- SQLite configuration
PRAGMA foreign_keys = ON;
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA temp_store = MEMORY;
PRAGMA mmap_size = 30000000000;

-- ============================================================================
-- Table Definitions
-- ============================================================================

{{#each tables}}
-- {{this.comment}}
CREATE TABLE IF NOT EXISTS {{this.name}} (
  {{#each this.columns}}
  {{this.name}} {{this.type}}{{#unless this.nullable}} NOT NULL{{/unless}}{{#if this.default}} DEFAULT {{this.default}}{{/if}}{{#if this.autoIncrement}} PRIMARY KEY AUTOINCREMENT{{/if}}{{#unless @last}},{{/unless}}
  {{/each}}

  {{#if this.constraints}},
  -- Constraints
  {{#each this.constraints}}
  {{#if this.isPrimaryKey}}
  {{#unless this.autoIncrement}}
  CONSTRAINT {{this.name}} PRIMARY KEY ({{this.columns}})
  {{/unless}}
  {{/if}}
  {{#if this.isForeignKey}}
  CONSTRAINT {{this.name}} FOREIGN KEY ({{this.columns}})
    REFERENCES {{this.referencesTable}}({{this.referencesColumns}})
    {{#if this.onDelete}}ON DELETE {{this.onDelete}}{{/if}}
    {{#if this.onUpdate}}ON UPDATE {{this.onUpdate}}{{/if}}
  {{/if}}
  {{#if this.isUnique}}
  CONSTRAINT {{this.name}} UNIQUE ({{this.columns}})
  {{/if}}
  {{#if this.isCheck}}
  CONSTRAINT {{this.name}} CHECK ({{this.expression}})
  {{/if}}
  {{#unless @last}},{{/unless}}
  {{/each}}
  {{/if}}
);

{{/each}}

-- ============================================================================
-- Indexes
-- ============================================================================

{{#each indexes}}
CREATE {{#if this.isUnique}}UNIQUE {{/if}}INDEX IF NOT EXISTS {{this.name}}
  ON {{this.tableName}} ({{this.columns}})
  {{#if this.whereClause}}WHERE {{this.whereClause}}{{/if}};
{{/each}}

-- ============================================================================
-- Triggers
-- ============================================================================

{{#each triggers}}
-- {{this.comment}}
CREATE TRIGGER IF NOT EXISTS {{this.name}}
  {{this.timing}} {{this.event}} ON {{this.tableName}}
  {{#if this.condition}}WHEN {{this.condition}}{{/if}}
BEGIN
  {{this.functionBody}}
END;
{{/each}}

-- Updated_at trigger (common pattern)
{{#each tablesWithUpdatedAt}}
CREATE TRIGGER IF NOT EXISTS trigger_{{this.name}}_updated_at
  AFTER UPDATE ON {{this.name}}
BEGIN
  UPDATE {{this.name}} SET updated_at = CURRENT_TIMESTAMP
  WHERE rowid = NEW.rowid;
END;
{{/each}}

-- ============================================================================
-- Views
-- ============================================================================

{{#each views}}
CREATE VIEW IF NOT EXISTS {{this.name}} AS
{{this.query}};
{{/each}}

-- ============================================================================
-- Migration Version Tracking
-- ============================================================================

CREATE TABLE IF NOT EXISTS schema_migrations (
  version TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  applied_at TEXT DEFAULT (datetime('now')),
  execution_time_ms INTEGER,
  checksum TEXT
);

CREATE INDEX IF NOT EXISTS idx_schema_migrations_applied_at ON schema_migrations(applied_at);

-- Insert initial migration record
INSERT OR IGNORE INTO schema_migrations (version, name, checksum)
VALUES ('0001', 'initial_schema', '{{checksumValue}}');

-- ============================================================================
-- Performance Optimizations
-- ============================================================================

-- Analyze tables for query optimization
ANALYZE;

-- Vacuum to reclaim space
VACUUM;
