template:
  id: database-schema-template-v2
  name: PostgreSQL Database Schema for Supabase
  version: 2.0
  output:
    format: sql
    filename: schema-{{timestamp}}.sql
    title: "{{project_name}} Database Schema"

# Supabase PostgreSQL Schema Generation Template
# Includes RLS policies, performance indexes, and audit trails

sections:
  - id: header-comment
    title: Schema Header
    template: |
      -- {{project_name}} Database Schema v{{schema_version}}
      -- Generated: {{created_date}}
      -- Platform: PostgreSQL (Supabase)
      -- 
      -- This schema includes:
      -- • Core application tables with proper constraints
      -- • Row Level Security (RLS) policies for data protection
      -- • Performance indexes for common query patterns
      -- • Audit trails with created_at/updated_at timestamps
      -- • Integration with Supabase Auth for user management

  - id: extensions
    title: Required Extensions
    template: |
      -- Enable necessary PostgreSQL extensions
      CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
      CREATE EXTENSION IF NOT EXISTS "pgcrypto";
      {{#if citext_needed}}
      CREATE EXTENSION IF NOT EXISTS "citext";
      {{/if}}
      {{#if postgis_needed}}
      CREATE EXTENSION IF NOT EXISTS "postgis";
      {{/if}}

  - id: core-tables
    title: Core Application Tables
    template: |
      -- Core Tables
      -- ===========

      {{#each entities}}
      -- {{description}}
      CREATE TABLE public.{{table_name}} (
        id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
        {{#each columns}}
        {{column_name}} {{data_type}}{{#if constraints}} {{constraints}}{{/if}}{{#if @last}}{{else}},{{/if}}
        {{/each}}
        {{#if audit_trail}},
        created_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now()),
        updated_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now()){{/if}}
      );

      {{#if comments}}
      -- Table comments
      {{#each comments}}
      COMMENT ON {{type}} public.{{../table_name}}.{{name}} IS '{{description}}';
      {{/each}}
      {{/if}}

      {{/each}}

  - id: relationships
    title: Foreign Key Relationships
    template: |
      -- Foreign Key Relationships
      -- =========================

      {{#each relationships}}
      ALTER TABLE public.{{from_table}} 
        ADD CONSTRAINT fk_{{from_table}}_{{to_table}} 
        FOREIGN KEY ({{from_column}}) 
        REFERENCES public.{{to_table}}({{to_column}}) 
        {{on_delete}};

      {{/each}}

  - id: rls-policies
    title: Row Level Security Policies
    condition: rls_enabled
    template: |
      -- Row Level Security (RLS) Policies
      -- ================================

      {{#each entities}}
      {{#if ../rls_enabled}}
      -- Enable RLS on {{table_name}}
      ALTER TABLE public.{{table_name}} ENABLE ROW LEVEL SECURITY;

      {{#each rls_policies}}
      -- {{description}}
      CREATE POLICY "{{policy_name}}" ON public.{{../table_name}}
        FOR {{operation}} USING ({{condition}}){{#if check_condition}} WITH CHECK ({{check_condition}}){{/if}};

      {{/each}}
      {{/if}}

      {{/each}}

  - id: indexes
    title: Performance Indexes
    template: |
      -- Performance Indexes
      -- ==================

      {{#each indexes}}
      -- {{description}}
      CREATE {{#if unique}}UNIQUE {{/if}}INDEX {{index_name}} 
        ON public.{{table_name}}{{#if method}} USING {{method}}{{/if}} ({{columns}});

      {{/each}}

  - id: functions
    title: Database Functions
    template: |
      -- Database Functions
      -- ==================

      {{#if audit_trail}}
      -- Updated At Trigger Function
      CREATE OR REPLACE FUNCTION update_updated_at_column()
      RETURNS TRIGGER AS $$
      BEGIN
        NEW.updated_at = timezone('utc'::text, now());
        RETURN NEW;
      END;
      $$ language 'plpgsql';
      {{/if}}

      {{#each custom_functions}}
      -- {{description}}
      CREATE OR REPLACE FUNCTION {{function_name}}({{parameters}})
      RETURNS {{return_type}} AS $$
      {{function_body}}
      $$ LANGUAGE {{language}};

      {{/each}}

  - id: triggers
    title: Database Triggers
    template: |
      -- Database Triggers
      -- ================

      {{#if audit_trail}}
      {{#each entities}}
      {{#if audit_trail}}
      -- Apply updated_at trigger to {{table_name}}
      CREATE TRIGGER update_{{table_name}}_updated_at 
        BEFORE UPDATE ON public.{{table_name}} 
        FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

      {{/if}}
      {{/each}}
      {{/if}}

      {{#each custom_triggers}}
      -- {{description}}
      CREATE TRIGGER {{trigger_name}}
        {{timing}} {{event}} ON public.{{table_name}}
        FOR EACH {{level}} EXECUTE FUNCTION {{function_name}}();

      {{/each}}

  - id: sample-data
    title: Sample Data
    condition: include_sample_data
    template: |
      -- Sample Data (Development Only)
      -- =============================

      {{#if include_sample_data}}
      {{#each sample_data}}
      -- Sample data for {{table_name}}
      INSERT INTO public.{{table_name}} ({{columns}}) VALUES 
      {{#each rows}}
        ({{values}}){{#if @last}};{{else}},{{/if}}
      {{/each}}

      {{/each}}
      {{/if}}

  - id: security-setup
    title: Security Configuration
    template: |
      -- Security Configuration
      -- ======================

      {{#if auth_integration}}
      -- Grant appropriate permissions for authenticated users
      {{#each permissions}}
      GRANT {{privileges}} ON public.{{table_name}} TO {{role}};
      {{/each}}
      {{/if}}

      {{#if api_key_restrictions}}
      -- API key usage restrictions
      {{#each restrictions}}
      -- {{description}}
      {{sql_statement}};
      {{/each}}
      {{/if}}

  - id: completion-notes
    title: Deployment Notes
    template: |
      -- Schema Deployment Complete
      -- ==========================
      -- 
      -- Next Steps:
      -- 1. Verify all tables and relationships are created correctly
      -- 2. Test RLS policies with different user contexts
      -- 3. Validate indexes are improving query performance
      -- 4. Set up monitoring for slow queries and performance
      -- 5. Configure backup and disaster recovery procedures
      -- 
      -- Verification Queries:
      -- SELECT * FROM information_schema.tables WHERE table_schema = 'public';
      -- SELECT * FROM pg_policies WHERE schemaname = 'public';
      -- SELECT * FROM pg_indexes WHERE schemaname = 'public';
      -- 
      -- Database URL: postgresql://postgres:[YOUR-PASSWORD]@db.{{project_ref}}.supabase.co:5432/postgres
      -- Dashboard: https://{{project_ref}}.supabase.co

# Template Variables Schema
variables:
  project_name:
    type: string
    description: "Name of the project"
    required: true
  
  schema_version:
    type: string
    description: "Schema version number"
    default: "1.0"
  
  created_date:
    type: string
    description: "Schema generation date"
    default: "{{current_date}}"
  
  project_ref:
    type: string
    description: "Supabase project reference"
    required: false
  
  rls_enabled:
    type: boolean
    description: "Enable Row Level Security"
    default: true
  
  audit_trail:
    type: boolean
    description: "Include created_at/updated_at columns"
    default: true
  
  auth_integration:
    type: boolean
    description: "Integrate with Supabase Auth"
    default: true
  
  include_sample_data:
    type: boolean
    description: "Include sample data for development"
    default: false
  
  entities:
    type: array
    description: "Database entities/tables to create"
    required: true
    schema:
      table_name: string
      description: string
      columns: array
      audit_trail: boolean
      rls_policies: array
      comments: array
  
  relationships:
    type: array
    description: "Foreign key relationships"
    schema:
      from_table: string
      from_column: string
      to_table: string
      to_column: string
      on_delete: string
  
  indexes:
    type: array
    description: "Performance indexes"
    schema:
      index_name: string
      table_name: string
      columns: string
      unique: boolean
      method: string
      description: string
  
  custom_functions:
    type: array
    description: "Custom database functions"
    schema:
      function_name: string
      parameters: string
      return_type: string
      function_body: string
      language: string
      description: string
  
  permissions:
    type: array
    description: "Database permissions"
    schema:
      table_name: string
      role: string
      privileges: string

# Usage Examples
examples:
  basic_saas:
    project_name: "TaskFlow SaaS"
    entities:
      - table_name: "users"
        description: "User profiles integrated with Supabase Auth"
        columns:
          - column_name: "email"
            data_type: "TEXT"
            constraints: "UNIQUE NOT NULL"
          - column_name: "full_name" 
            data_type: "TEXT"
          - column_name: "avatar_url"
            data_type: "TEXT"
        rls_policies:
          - policy_name: "Users can view own profile"
            operation: "SELECT"
            condition: "auth.uid() = id"
          - policy_name: "Users can update own profile"
            operation: "UPDATE"
            condition: "auth.uid() = id"
      
      - table_name: "projects"
        description: "User projects and workspaces"
        columns:
          - column_name: "name"
            data_type: "TEXT"
            constraints: "NOT NULL"
          - column_name: "description"
            data_type: "TEXT"
          - column_name: "owner_id"
            data_type: "UUID"
            constraints: "REFERENCES public.users(id) ON DELETE CASCADE"
        rls_policies:
          - policy_name: "Users can view own projects"
            operation: "SELECT"
            condition: "auth.uid() = owner_id"
    
    relationships:
      - from_table: "projects"
        from_column: "owner_id"
        to_table: "users"
        to_column: "id"
        on_delete: "ON DELETE CASCADE"
    
    indexes:
      - index_name: "idx_projects_owner_id"
        table_name: "projects"
        columns: "owner_id"
        description: "Fast project lookups by owner"