// Copyright (c) Abstract Machines
// SPDX-License-Identifier: Apache-2.0

package postgres

import (
	dpostgres "github.com/absmach/magistrala/domains/postgres"
	"github.com/absmach/magistrala/pkg/errors"
	repoerr "github.com/absmach/magistrala/pkg/errors/repository"
	rolesPostgres "github.com/absmach/magistrala/pkg/roles/repo/postgres"
	_ "github.com/jackc/pgx/v5/stdlib" // required for SQL access
	migrate "github.com/rubenv/sql-migrate"
)

func Migration() (*migrate.MemoryMigrationSource, error) {
	rolesMigration, err := rolesPostgres.Migration(rolesTableNamePrefix, entityTableName, entityIDColumnName)
	if err != nil {
		return &migrate.MemoryMigrationSource{}, errors.Wrap(repoerr.ErrRoleMigration, err)
	}
	rulesMigration := &migrate.MemoryMigrationSource{
		Migrations: []*migrate.Migration{
			{
				Id: "rules_01",
				// VARCHAR(36) for colums with IDs as UUIDS have a maximum of 36 characters
				// STATUS 0 to imply enabled and 1 to imply disabled
				Up: []string{
					`CREATE TABLE IF NOT EXISTS rules (
						id                VARCHAR(36) PRIMARY KEY,
						name              VARCHAR(1024),
						domain_id         VARCHAR(36) NOT NULL,
						metadata          JSONB,
						created_by        VARCHAR(254),
						created_at        TIMESTAMP,
						updated_at        TIMESTAMP,
						updated_by        VARCHAR(254),
						input_channel     VARCHAR(36),
						input_topic       TEXT,
						outputs           JSONB,
						status            SMALLINT NOT NULL DEFAULT 0 CHECK (status >= 0),
						logic_type        SMALLINT NOT NULL DEFAULT 0 CHECK (logic_type >= 0),
						logic_value       BYTEA,
						time              TIMESTAMP,
						recurring         SMALLINT,
						recurring_period  SMALLINT,
						start_datetime    TIMESTAMP
					)`,
				},
				Down: []string{
					`DROP TABLE IF EXISTS rules`,
				},
			},
			{
				Id: "rules_02",
				Up: []string{
					`ALTER TABLE rules ADD COLUMN tags TEXT[];`,
				},
				Down: []string{
					`ALTER TABLE rules DROP COLUMN tags;`,
				},
			},
			{
				Id: "rules_03",
				Up: []string{
					`UPDATE rules
						 SET metadata = (COALESCE(metadata, '{}'::jsonb) - 'ui') || jsonb_build_object('flow', metadata->'ui')
						 WHERE metadata ? 'ui' AND jsonb_typeof(metadata->'ui') = 'string'`,
				},
				Down: []string{
					`UPDATE rules
						 SET metadata = (COALESCE(metadata, '{}'::jsonb) - 'flow') || jsonb_build_object('ui', metadata->'flow')
						 WHERE metadata ? 'flow' AND jsonb_typeof(metadata->'flow') = 'string'`,
				},
			},
			{
				Id: "rules_04",
				Up: []string{
					// Canonicalize legacy rule topics from dot/NATS wildcards
					// to slash/MQTT wildcards.
					`UPDATE rules
						 SET input_topic = REPLACE(REPLACE(REPLACE(input_topic, '>', '#'), '*', '+'), '.', '/')
						 WHERE input_topic IS NOT NULL AND input_topic <> ''`,
				},
				Down: []string{
					`UPDATE rules
						 SET input_topic = REPLACE(REPLACE(REPLACE(input_topic, '#', '>'), '+', '*'), '/', '.')
						 WHERE input_topic IS NOT NULL AND input_topic <> ''`,
				},
			},
			{
				Id: "rules_05",
				Up: []string{
					// Canonicalize channel output topics in rule outputs JSON:
					// dot/NATS wildcards -> slash/MQTT wildcards.
					`UPDATE rules AS r
						SET outputs = COALESCE((
							SELECT jsonb_agg(
								CASE
									WHEN out_elem.elem->>'type' = 'channels'
										AND out_elem.elem ? 'topic'
										AND jsonb_typeof(out_elem.elem->'topic') = 'string'
									THEN jsonb_set(
										out_elem.elem,
										'{topic}',
										to_jsonb(REPLACE(REPLACE(REPLACE(out_elem.elem->>'topic', '>', '#'), '*', '+'), '.', '/')),
										false
									)
									ELSE out_elem.elem
								END
								ORDER BY out_elem.ord
							)
							FROM jsonb_array_elements(r.outputs) WITH ORDINALITY AS out_elem(elem, ord)
						), '[]'::jsonb)
						WHERE jsonb_typeof(r.outputs) = 'array'`,
				},
				Down: []string{
					`UPDATE rules AS r
						SET outputs = COALESCE((
							SELECT jsonb_agg(
								CASE
									WHEN out_elem.elem->>'type' = 'channels'
										AND out_elem.elem ? 'topic'
										AND jsonb_typeof(out_elem.elem->'topic') = 'string'
									THEN jsonb_set(
										out_elem.elem,
										'{topic}',
										to_jsonb(REPLACE(REPLACE(REPLACE(out_elem.elem->>'topic', '#', '>'), '+', '*'), '/', '.')),
										false
									)
									ELSE out_elem.elem
								END
								ORDER BY out_elem.ord
							)
							FROM jsonb_array_elements(r.outputs) WITH ORDINALITY AS out_elem(elem, ord)
						), '[]'::jsonb)
						WHERE jsonb_typeof(r.outputs) = 'array'`,
				},
			},
		},
	}

	rulesMigration.Migrations = append(rulesMigration.Migrations, rolesMigration.Migrations...)

	domainsMigration, err := dpostgres.Migration()
	if err != nil {
		return &migrate.MemoryMigrationSource{}, errors.Wrap(repoerr.ErrRoleMigration, err)
	}
	rulesMigration.Migrations = append(rulesMigration.Migrations, domainsMigration.Migrations...)

	return rulesMigration, nil
}
