package org.kar.karusic.migration;

import java.nio.file.Files;
import java.nio.file.NoSuchFileException;
import java.nio.file.Paths;
import java.nio.file.StandardCopyOption;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;

import org.bson.types.ObjectId;
import org.kar.archidata.api.DataResource;
import org.kar.archidata.dataAccess.DBAccess;
import org.kar.archidata.dataAccess.options.AccessDeletedItems;
import org.kar.archidata.dataAccess.options.OverrideTableName;
import org.kar.archidata.migration.MigrationSqlStep;
import org.kar.karusic.migration.model.CoverConversion;
import org.kar.karusic.migration.model.MediaConversion;
import org.kar.karusic.migration.model.OIDConversion;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class Migration20250104 extends MigrationSqlStep {
	private static final Logger LOGGER = LoggerFactory.getLogger(Migration20240226.class);

	public static final int KARSO_INITIALISATION_ID = 1;

	@Override
	public String getName() {
		return "migration-2025-01-04: convert base from UUID to OID";
	}

	@Override
	public void generateStep() throws Exception {
		// Create a simple function to create objectId in the DB (for manual insertion ...)
		// addAction("""
		// DELIMITER //
		//
		// CREATE FUNCTION generate_objectid()
		// RETURNS BINARY(12)
		// DETERMINISTIC
		// BEGIN
		// DECLARE ts BINARY(4);
		// DECLARE random_part BINARY(5);
		// DECLARE counter BINARY(3);
		// SET ts = UNHEX(HEX(UNIX_TIMESTAMP()));
		// SET random_part = UNHEX(HEX(FLOOR(RAND() * POW(2, 40))));
		// SET counter = UNHEX(HEX(FLOOR(RAND() * POW(2, 24))));
		// RETURN CONCAT(ts, random_part, counter);
		// END //
		//
		// DELIMITER ;
		// """);
		addAction("""
				ALTER TABLE `data` ADD `_id` binary(12) AFTER `uuid`;
				""");
		addAction((final DBAccess da) -> {
			final List<OIDConversion> datas = da.gets(OIDConversion.class, new AccessDeletedItems(), new OverrideTableName("data"));
			for (final OIDConversion elem : datas) {
				elem._id = new ObjectId();
			}
			for (final OIDConversion elem : datas) {
				da.update(elem, elem.uuid, List.of("_id"), new OverrideTableName("data"));
			}
		});
		final List<String> tableToTransform = List.of("album", "artist", "gender", "track", "user");
		for (final String tableName : tableToTransform) {
			addAction("ALTER TABLE `" + tableName + "` ADD `covers_oid` text NULL;");
			addAction((final DBAccess da) -> {
				final List<OIDConversion> datas = da.gets(OIDConversion.class, new AccessDeletedItems(), new OverrideTableName("data"));
				final List<CoverConversion> tableCoverTransforms = da.gets(CoverConversion.class, new AccessDeletedItems(), new OverrideTableName(tableName));
				LOGGER.info("Get somes data: {} {}", datas.size(), tableCoverTransforms.size());
				for (final CoverConversion tableTransform : tableCoverTransforms) {
					final List<ObjectId> values = new ArrayList<>();
					if (tableTransform.covers == null) {
						continue;
					}
					for (final UUID link : tableTransform.covers) {
						for (final OIDConversion data : datas) {
							if (data.uuid.equals(link)) {
								values.add(data._id);
								break;
							}
						}
					}
					if (values.size() != 0) {
						tableTransform.covers_oid = values;
						LOGGER.info("    update: {}: {} => {}", tableTransform.id, tableTransform.covers, tableTransform.covers_oid);
						da.update(tableTransform, tableTransform.id, List.of("covers_oid"), new OverrideTableName(tableName));
					}
				}
			});
			addAction("ALTER TABLE `" + tableName + "` DROP `covers`;");
			addAction("ALTER TABLE `" + tableName + "` CHANGE `covers_oid` `covers` text NULL;");
		}
		addAction("""
				ALTER TABLE `track` ADD `dataOid` binary(12) AFTER dataId;
				""");
		addAction((final DBAccess da) -> {
			final List<OIDConversion> datas = da.gets(OIDConversion.class, new AccessDeletedItems(), new OverrideTableName("data"));
			final List<MediaConversion> medias = da.gets(MediaConversion.class, new AccessDeletedItems(), new OverrideTableName("track"));
			for (final MediaConversion media : medias) {
				for (final OIDConversion data : datas) {
					if (data.uuid.equals(media.dataId)) {
						media.dataOid = data._id;
						da.update(media, media.id, List.of("dataOid"), new OverrideTableName("track"));
						break;
					}
				}
			}
		});
		addAction("""
				ALTER TABLE `track` DROP `dataId`;
				""");
		addAction("""
				ALTER TABLE `track` CHANGE `dataOid` `dataId` binary(12) NOT NULL;
				""");
		// Move the files...
		addAction((final DBAccess da) -> {
			final List<OIDConversion> datas = da.gets(OIDConversion.class, new AccessDeletedItems(), new OverrideTableName("data"));
			for (final OIDConversion data : datas) {
				final String origin = DataResource.getFileDataOld(data.uuid);
				final String destination = DataResource.getFileData(data._id);
				LOGGER.info("move file = {}", origin);
				LOGGER.info("        ==> {}", destination);
				try {
					Files.move(Paths.get(origin), Paths.get(destination), StandardCopyOption.ATOMIC_MOVE);
				} catch (final NoSuchFileException ex) {
					LOGGER.warn("Fail to move file : {}", ex.getMessage());
				}
			}
		});
		addAction("""
				ALTER TABLE `data` DROP `uuid`;
				""");
		// addAction("""
		// ALTER TABLE `data` CHANGE `_id` `_id` BINARY(12) DEFAULT (generate_objectid());
		// """);
		addAction("""
				ALTER TABLE `data` ADD PRIMARY KEY `_id` (`_id`);
				""");
	}

}
