#!/bin/bash
set -e

ADMIN_LOGIN=${ADMIN_LOGIN:-admin}
ADMIN_PASSWORD=${ADMIN_PASSWORD:-LexP@ssw0rd_1}

# Ждем пока PostgreSQL будет готов принимать подключения
until pg_isready -U "$POSTGRES_USER" -d "$POSTGRES_DB"; do
  echo "Waiting for PostgreSQL to be ready..."
  sleep 2
done

# Создаем базу данных и пользователя если не существуют
psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" <<-EOSQL
    -- Создаем расширение если не существует
    CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

    -- Даем все права пользователю на базу данных
    GRANT ALL PRIVILEGES ON DATABASE $POSTGRES_DB TO $POSTGRES_USER;

    -- Создаем таблицы если их нет (для первоначальной инициализации)
    DO \$\$
    BEGIN
        -- Таблица приложений
        IF NOT EXISTS (SELECT 1 FROM pg_tables WHERE tablename = 'application') THEN
            CREATE TABLE application (
                id SERIAL PRIMARY KEY,
                name VARCHAR(100) NOT NULL,
                host_ip VARCHAR(45) NOT NULL,
                hostname VARCHAR(100) NOT NULL,
                path VARCHAR(500) NOT NULL,
                active BOOLEAN DEFAULT TRUE,
                status VARCHAR(20) DEFAULT 'UNKNOWN',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            );

            -- Индексы для application
            CREATE INDEX idx_application_host_ip ON application(host_ip);
            CREATE INDEX idx_application_active ON application(active);
            CREATE INDEX idx_application_status ON application(status);
            CREATE INDEX idx_application_created_at ON application(created_at);

            RAISE NOTICE 'Table application created successfully';
        ELSE
            RAISE NOTICE 'Table application already exists';
        END IF;

        -- Гарантируем наличие колонки status в application
        IF NOT EXISTS (
            SELECT 1 FROM information_schema.columns
            WHERE table_name = 'application' AND column_name = 'status'
        ) THEN
            ALTER TABLE application ADD COLUMN status VARCHAR(20) NOT NULL DEFAULT 'UNKNOWN';
            UPDATE application SET status = 'UNKNOWN' WHERE status IS NULL;
            CREATE INDEX IF NOT EXISTS idx_application_status ON application(status);
            RAISE NOTICE 'Added status column to application table';
        END IF;

        -- Таблица docker-compose файлов
        IF NOT EXISTS (SELECT 1 FROM pg_tables WHERE tablename = 'compose_file') THEN
            CREATE TABLE compose_file (
                id SERIAL PRIMARY KEY,
                application_id INTEGER NOT NULL REFERENCES application(id) ON DELETE CASCADE,
                content TEXT NOT NULL,
                version INTEGER NOT NULL DEFAULT 1,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                is_current BOOLEAN DEFAULT TRUE
            );

            -- Индексы для compose_file
            CREATE INDEX idx_compose_file_application_id ON compose_file(application_id);
            CREATE INDEX idx_compose_file_is_current ON compose_file(is_current);
            CREATE INDEX idx_compose_file_created_at ON compose_file(created_at);

            RAISE NOTICE 'Table compose_file created successfully';
        ELSE
            RAISE NOTICE 'Table compose_file already exists';
        END IF;

        -- Таблица env файлов (обновленная структура)
        IF NOT EXISTS (SELECT 1 FROM pg_tables WHERE tablename = 'env_file') THEN
            CREATE TABLE env_file (
                id SERIAL PRIMARY KEY,
                application_id INTEGER NOT NULL REFERENCES application(id) ON DELETE CASCADE,
                filename VARCHAR(100) NOT NULL DEFAULT '.env',
                content TEXT NOT NULL,
                version INTEGER NOT NULL DEFAULT 1,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                is_current BOOLEAN DEFAULT TRUE
            );

            -- Уникальный constraint ТОЛЬКО для текущих версий (используем partial unique index)
            CREATE UNIQUE INDEX unique_current_env ON env_file (application_id, filename)
            WHERE is_current = true;

            -- Индексы для env_file
            CREATE INDEX idx_env_file_application_id ON env_file(application_id);
            CREATE INDEX idx_env_file_filename ON env_file(filename);
            CREATE INDEX idx_env_file_is_current ON env_file(is_current);
            CREATE INDEX idx_env_file_created_at ON env_file(created_at);

            RAISE NOTICE 'Table env_file created successfully';
        ELSE
            -- Если таблица уже существует, проверяем наличие поля filename
            IF NOT EXISTS (SELECT 1 FROM information_schema.columns
                          WHERE table_name = 'env_file' AND column_name = 'filename') THEN
                -- Добавляем поле filename
                ALTER TABLE env_file ADD COLUMN filename VARCHAR(100) NOT NULL DEFAULT '.env';

                -- Создаем индексы для нового поля
                CREATE INDEX idx_env_file_filename ON env_file(filename);

                RAISE NOTICE 'Added filename column to env_file table';
            ELSE
                RAISE NOTICE 'Table env_file already exists with filename column';
            END IF;

            -- Удаляем старый constraint если он существует
            IF EXISTS (SELECT 1 FROM information_schema.table_constraints
                      WHERE table_name = 'env_file' AND constraint_name = 'unique_current_env') THEN
                ALTER TABLE env_file DROP CONSTRAINT unique_current_env;
                RAISE NOTICE 'Dropped old unique_current_env constraint';
            END IF;

            -- Удаляем старый index если он существует
            IF EXISTS (SELECT 1 FROM pg_indexes
                      WHERE tablename = 'env_file' AND indexname = 'unique_current_env') THEN
                DROP INDEX unique_current_env;
                RAISE NOTICE 'Dropped old unique_current_env index';
            END IF;

            -- Создаем новый partial unique index
            CREATE UNIQUE INDEX unique_current_env ON env_file (application_id, filename)
            WHERE is_current = true;

            RAISE NOTICE 'Created new partial unique index for current env files';
        END IF;

        -- Таблица логов миграций
        IF NOT EXISTS (SELECT 1 FROM pg_tables WHERE tablename = 'migration_log') THEN
            CREATE TABLE migration_log (
                id SERIAL PRIMARY KEY,
                application_id INTEGER NOT NULL REFERENCES application(id) ON DELETE CASCADE,
                command VARCHAR(500) NOT NULL,
                status VARCHAR(50) NOT NULL,
                output TEXT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            );

            -- Индексы для migration_log
            CREATE INDEX idx_migration_log_application_id ON migration_log(application_id);
            CREATE INDEX idx_migration_log_created_at ON migration_log(created_at);

            RAISE NOTICE 'Table migration_log created successfully';
        ELSE
            RAISE NOTICE 'Table migration_log already exists';
        END IF;

        -- Таблица задач агента
        IF NOT EXISTS (SELECT 1 FROM pg_tables WHERE tablename = 'agent_task') THEN
            CREATE TABLE agent_task (
                id SERIAL PRIMARY KEY,
                task_id VARCHAR(36) UNIQUE NOT NULL,
                application_id INTEGER NOT NULL REFERENCES application(id) ON DELETE CASCADE,
                task_type VARCHAR(50) NOT NULL,
                command VARCHAR(500) NOT NULL,
                status VARCHAR(20) NOT NULL DEFAULT 'pending',
                host_ip VARCHAR(45) NOT NULL,
                payload TEXT,
                output TEXT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                completed_at TIMESTAMP
            );

            -- Создаем индексы для часто используемых полей
            CREATE INDEX idx_agent_task_task_id ON agent_task(task_id);
            CREATE INDEX idx_agent_task_application_id ON agent_task(application_id);
            CREATE INDEX idx_agent_task_status ON agent_task(status);
            CREATE INDEX idx_agent_task_host_ip ON agent_task(host_ip);
            CREATE INDEX idx_agent_task_created_at ON agent_task(created_at);
            CREATE INDEX idx_agent_task_completed_at ON agent_task(completed_at);

            -- Комментарии к таблице и полям
            COMMENT ON TABLE agent_task IS 'Таблица для хранения задач агента развертывания';
            COMMENT ON COLUMN agent_task.task_id IS 'Уникальный идентификатор задачи (UUID)';
            COMMENT ON COLUMN agent_task.task_type IS 'Тип задачи: deploy, migration, update_config';
            COMMENT ON COLUMN agent_task.status IS 'Статус задачи: pending, in_progress, completed, failed';
            COMMENT ON COLUMN agent_task.payload IS 'Дополнительные данные для задачи в формате JSON';
            COMMENT ON COLUMN agent_task.output IS 'Вывод выполнения команды';

            RAISE NOTICE 'Table agent_task created successfully';
        ELSE
            RAISE NOTICE 'Table agent_task already exists';
        END IF;

        -- Таблица пользователей веб-интерфейса
        IF NOT EXISTS (SELECT 1 FROM pg_tables WHERE tablename = 'auth_user') THEN
            CREATE TABLE auth_user (
                id SERIAL PRIMARY KEY,
                username VARCHAR(100) UNIQUE NOT NULL,
                password_hash VARCHAR(128) NOT NULL,
                is_active BOOLEAN DEFAULT TRUE,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            );
            CREATE INDEX idx_auth_user_username ON auth_user(username);
            CREATE INDEX idx_auth_user_active ON auth_user(is_active);
            RAISE NOTICE 'Table auth_user created successfully';
        ELSE
            RAISE NOTICE 'Table auth_user already exists';
        END IF;

        -- Обновляем длину поля path в application если нужно
        IF EXISTS (SELECT 1 FROM information_schema.columns
                  WHERE table_name = 'application' AND column_name = 'path'
                  AND character_maximum_length < 500) THEN
            ALTER TABLE application ALTER COLUMN path TYPE VARCHAR(500);
            RAISE NOTICE 'Updated application.path column length to 500';
        END IF;

        -- Обновляем длину поля command в migration_log если нужно
        IF EXISTS (SELECT 1 FROM information_schema.columns
                  WHERE table_name = 'migration_log' AND column_name = 'command'
                  AND character_maximum_length < 500) THEN
            ALTER TABLE migration_log ALTER COLUMN command TYPE VARCHAR(500);
            RAISE NOTICE 'Updated migration_log.command column length to 500';
        END IF;

        -- Обновляем длину поля command в agent_task если нужно
        IF EXISTS (SELECT 1 FROM information_schema.columns
                  WHERE table_name = 'agent_task' AND column_name = 'command'
                  AND character_maximum_length < 500) THEN
            ALTER TABLE agent_task ALTER COLUMN command TYPE VARCHAR(500);
            RAISE NOTICE 'Updated agent_task.command column length to 500';
        END IF;

    END
    \$\$;

    -- Создаем функцию для автоматического обновления updated_at
    CREATE OR REPLACE FUNCTION update_updated_at_column()
    RETURNS TRIGGER AS \$\$
    BEGIN
        NEW.updated_at = CURRENT_TIMESTAMP;
        RETURN NEW;
    END;
    \$\$ language 'plpgsql';

    -- Создаем триггер для автоматического обновления updated_at в application
    DROP TRIGGER IF EXISTS update_application_updated_at ON application;
    CREATE TRIGGER update_application_updated_at
        BEFORE UPDATE ON application
        FOR EACH ROW
        EXECUTE FUNCTION update_updated_at_column();

    -- Анализируем таблицы для оптимизации
    ANALYZE application;
    ANALYZE compose_file;
    ANALYZE env_file;
    ANALYZE migration_log;
    ANALYZE agent_task;
    ANALYZE auth_user;

    -- Инициализация единственной учетной записи (логин/пароль задаются env ADMIN_LOGIN/ADMIN_PASSWORD)
    INSERT INTO auth_user (username, password_hash, is_active)
    VALUES ('$ADMIN_LOGIN', md5('$ADMIN_PASSWORD'), TRUE)
    ON CONFLICT (username) DO UPDATE
        SET password_hash = EXCLUDED.password_hash,
            is_active = TRUE;
EOSQL

echo "Database initialization completed!"