-- -- Database initialization script for Adtlas DAI
-- -- This script sets up the initial database structure and configurations

-- -- Create database if it doesn't exist
-- SELECT 'CREATE DATABASE adtlas_dai'
-- WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 'adtlas_dai')\gexec

-- -- Connect to the adtlas_dai database
-- \c adtlas_dai;

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

-- -- Create custom types for campaign status
-- DO $$ BEGIN
--     CREATE TYPE campaign_status AS ENUM (
--         'Prebooked',
--         'Booked', 
--         'Canceled',
--         'In_progress',
--         'Terminated'
--     );
-- EXCEPTION
--     WHEN duplicate_object THEN null;
-- END $$;

-- -- Create custom types for media types
-- DO $$ BEGIN
--     CREATE TYPE media_type AS ENUM (
--         'Video',
--         'Audio',
--         'Image',
--         'Interactive'
--     );
-- EXCEPTION
--     WHEN duplicate_object THEN null;
-- END $$;

-- -- Create custom types for verification status
-- DO $$ BEGIN
--     CREATE TYPE verification_status AS ENUM (
--         'Pending',
--         'Verified',
--         'Failed',
--         'Rejected'
--     );
-- EXCEPTION
--     WHEN duplicate_object THEN null;
-- END $$;

-- -- Create indexes for better performance
-- -- These will be created after Django migrations

-- -- Create a function for updating timestamps
-- CREATE OR REPLACE FUNCTION update_updated_at_column()
-- RETURNS TRIGGER AS $$
-- BEGIN
--     NEW.updated_at = CURRENT_TIMESTAMP;
--     RETURN NEW;
-- END;
-- $$ language 'plpgsql';

-- -- Create a function for generating slugs
-- CREATE OR REPLACE FUNCTION generate_slug(input_text TEXT)
-- RETURNS TEXT AS $$
-- BEGIN
--     RETURN lower(regexp_replace(regexp_replace(input_text, '[^a-zA-Z0-9\s-]', '', 'g'), '\s+', '-', 'g'));
-- END;
-- $$ LANGUAGE plpgsql;

-- -- Create a function for calculating campaign priority
-- CREATE OR REPLACE FUNCTION calculate_campaign_priority(
--     target_alignment DECIMAL,
--     fulfillment DECIMAL,
--     urgency DECIMAL,
--     position DECIMAL
-- )
-- RETURNS DECIMAL AS $$
-- BEGIN
--     RETURN (
--         (target_alignment * 0.3) +
--         (fulfillment * 0.25) +
--         (urgency * 0.25) +
--         (position * 0.2)
--     );
-- END;
-- $$ LANGUAGE plpgsql;

-- -- Grant permissions to the application user
-- GRANT ALL PRIVILEGES ON DATABASE adtlas_dai TO adtlas_user;
-- GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO adtlas_user;
-- GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO adtlas_user;
-- GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public TO adtlas_user;

-- -- Set default privileges for future objects
-- ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO adtlas_user;
-- ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO adtlas_user;
-- ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON FUNCTIONS TO adtlas_user;

-- -- Create initial admin user (password will be set via Django management command)
-- -- This is just a placeholder for the database structure

-- COMMIT;