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

-- Database configuration
CREATE DATABASE IF NOT EXISTS {{databaseName}}
  CHARACTER SET {{characterSet}}
  COLLATE {{collation}};

USE {{databaseName}};

-- ============================================================================
-- 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}} AUTO_INCREMENT{{/if}}{{#unless @last}},{{/unless}}
  {{#if this.comment}}  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 KEY ({{this.columns}})
  {{/if}}
  {{#if this.isCheck}}
  CONSTRAINT {{this.name}} CHECK ({{this.expression}})
  {{/if}}
  {{#unless @last}},{{/unless}}
  {{/each}}
  {{/if}}
) ENGINE={{this.engine}} DEFAULT CHARSET={{../characterSet}} COLLATE={{../collation}}{{#if this.tableComment}} COMMENT='{{this.tableComment}}'{{/if}};

{{/each}}

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

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

-- Full-text search indexes (MySQL FULLTEXT)
{{#each fulltextIndexes}}
CREATE FULLTEXT INDEX {{this.name}} ON {{this.tableName}} ({{this.columns}});
{{/each}}

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

{{#each triggers}}
-- {{this.comment}}
DELIMITER $$

CREATE TRIGGER {{this.name}}
  {{this.timing}} {{this.event}} ON {{this.tableName}}
  FOR EACH ROW
BEGIN
  {{this.functionBody}}
END$$

DELIMITER ;
{{/each}}

-- Updated_at trigger (common pattern)
{{#each tablesWithUpdatedAt}}
DELIMITER $$

CREATE TRIGGER trigger_{{this.name}}_updated_at
  BEFORE UPDATE ON {{this.name}}
  FOR EACH ROW
BEGIN
  SET NEW.updated_at = CURRENT_TIMESTAMP;
END$$

DELIMITER ;
{{/each}}

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

{{#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)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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 DUPLICATE KEY UPDATE version = version;

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

-- Create application user
CREATE USER IF NOT EXISTS '{{appUser}}'@'%' IDENTIFIED BY '{{appPassword}}';

-- Grant permissions
{{#each tables}}
GRANT SELECT, INSERT, UPDATE, DELETE ON {{../databaseName}}.{{this.name}} TO '{{../appUser}}'@'%';
{{/each}}

FLUSH PRIVILEGES;
