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

-- Database configuration
CREATE DATABASE IF NOT EXISTS {{databaseName}}
  WITH ENCODING '{{characterSet}}'
  LC_COLLATE = '{{collation}}'
  LC_CTYPE = '{{collation}}';

\c {{databaseName}}

-- Enable extensions
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pg_trgm";  -- Trigram matching for text search
CREATE EXTENSION IF NOT EXISTS "btree_gin"; -- GIN indexes on B-tree types

-- Schema creation
{{#each schemas}}
CREATE SCHEMA IF NOT EXISTS {{this.name}};
{{/each}}

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

{{#each tables}}
-- {{this.comment}}
CREATE TABLE {{this.name}} (
  {{#each this.columns}}
  {{this.name}} {{this.type}}{{#if this.maxLength}}({{this.maxLength}}){{/if}}{{#if this.precision}}({{this.precision}},{{this.scale}}){{/if}}{{#unless this.nullable}} NOT NULL{{/unless}}{{#if this.default}} DEFAULT {{this.default}}{{/if}}{{#if this.autoIncrement}} GENERATED ALWAYS AS IDENTITY{{/if}}{{#unless @last}},{{/unless}}
  {{#if this.comment}}  -- {{this.comment}}{{/if}}
  {{/each}}

  {{#if this.constraints}},
  -- Constraints
  {{#each this.constraints}}
  {{#if this.isPrimaryKey}}
  CONSTRAINT {{this.name}} PRIMARY KEY ({{this.columns}})
  {{/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}}
);

{{#if this.tableComment}}
COMMENT ON TABLE {{this.name}} IS '{{this.tableComment}}';
{{/if}}

{{#each this.columns}}
{{#if this.comment}}
COMMENT ON COLUMN {{../this.name}}.{{this.name}} IS '{{this.comment}}';
{{/if}}
{{/each}}

{{/each}}

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

{{#each indexes}}
CREATE {{#if this.isUnique}}UNIQUE {{/if}}INDEX {{#if this.isConcurrent}}CONCURRENTLY {{/if}}{{this.name}}
  ON {{this.tableName}} USING {{this.method}} ({{this.columns}})
  {{#if this.whereClause}}WHERE {{this.whereClause}}{{/if}};
{{/each}}

-- Full-text search indexes (PostgreSQL GIN)
{{#each fulltextIndexes}}
CREATE INDEX {{this.name}} ON {{this.tableName}} USING GIN (to_tsvector('{{this.config}}', {{this.columns}}));
{{/each}}

-- JSONB indexes (PostgreSQL GIN)
{{#each jsonbColumns}}
CREATE INDEX idx_{{this.tableName}}_{{this.columnName}}_gin ON {{this.tableName}} USING GIN ({{this.columnName}});
{{/each}}

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

{{#each triggers}}
-- {{this.comment}}
CREATE OR REPLACE FUNCTION {{this.functionName}}()
RETURNS TRIGGER AS $$
BEGIN
  {{this.functionBody}}
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER {{this.name}}
  {{this.timing}} {{this.event}} ON {{this.tableName}}
  FOR EACH {{this.level}}
  {{#if this.condition}}WHEN ({{this.condition}}){{/if}}
  EXECUTE FUNCTION {{this.functionName}}();
{{/each}}

-- Updated_at trigger (common pattern)
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = CURRENT_TIMESTAMP;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

{{#each tablesWithUpdatedAt}}
CREATE TRIGGER trigger_{{this.name}}_updated_at
  BEFORE UPDATE ON {{this.name}}
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at_column();
{{/each}}

-- ============================================================================
-- Views (Common queries)
-- ============================================================================

{{#each views}}
CREATE OR REPLACE VIEW {{this.name}} AS
{{this.query}};
{{/each}}

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

CREATE TABLE IF NOT EXISTS schema_migrations (
  version VARCHAR(255) PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  applied_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  execution_time_ms INTEGER,
  checksum VARCHAR(64)
);

CREATE INDEX idx_schema_migrations_applied_at ON schema_migrations(applied_at);

-- Insert initial migration record
INSERT INTO schema_migrations (version, name, checksum)
VALUES ('0001', 'initial_schema', '{{checksumValue}}')
ON CONFLICT (version) DO NOTHING;

-- ============================================================================
-- Security & Permissions
-- ============================================================================

-- Create application user
CREATE USER {{appUser}} WITH PASSWORD '{{appPassword}}';

-- Grant permissions
{{#each tables}}
GRANT SELECT, INSERT, UPDATE, DELETE ON {{this.name}} TO {{../appUser}};
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO {{../appUser}};
{{/each}}

-- Row-level security (RLS) example
{{#each tablesWithRLS}}
ALTER TABLE {{this.name}} ENABLE ROW LEVEL SECURITY;

CREATE POLICY {{this.policyName}} ON {{this.name}}
  FOR {{this.policyOperation}}
  USING ({{this.policyCondition}});
{{/each}}
