-- Adtlas PostgreSQL Initialization Script (Fixed Version)

SET default_text_search_config = 'pg_catalog.english';



-- Create extensions
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pg_trgm";
CREATE EXTENSION IF NOT EXISTS "btree_gin";
CREATE EXTENSION IF NOT EXISTS "btree_gist";
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

-- Grant permissions
GRANT CONNECT ON DATABASE adtlas_db TO adtlas_user;
GRANT USAGE ON SCHEMA public TO adtlas_user;
GRANT CREATE ON SCHEMA public TO adtlas_user;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO adtlas_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE, SELECT ON SEQUENCES TO adtlas_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO adtlas_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO adtlas_user;

-- Function to update updated_at timestamp
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Function to generate URL-friendly slugs
CREATE OR REPLACE FUNCTION generate_slug(input_text TEXT)
RETURNS TEXT AS $$
BEGIN
    RETURN lower(
        regexp_replace(
            regexp_replace(
                regexp_replace(input_text, '[^a-zA-Z0-9\s\-_]', '', 'g'),
                '\s+', '-', 'g'
            ),
            '\-+', '-', 'g'
        )
    );
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- Create logs schema
CREATE SCHEMA IF NOT EXISTS logs;
GRANT USAGE ON SCHEMA logs TO adtlas_user;
GRANT CREATE ON SCHEMA logs TO adtlas_user;

-- Analyze tables
ANALYZE;

-- Success message
SELECT 'Adtlas PostgreSQL database initialization completed successfully!' as status;
