import { Sequelize, DataTypes } from "sequelize";
import mysql from "mysql2/promise";
import getConfig from "next/config";

const { serverRuntimeConfig } = getConfig();
let sequelizeInstance = null;

export const db = {
	initialized: false,
	initialize,
	sequelize: null,
	Sequelize,
};

async function initialize() {
	if (!sequelizeInstance) {
		const { host, port, user, password, database } =
			serverRuntimeConfig.dbConfig;
		const connection = await mysql.createConnection({
			host,
			port,
			user,
			password,
		});
		await connection.query(
			`CREATE DATABASE IF NOT EXISTS \`${database}\`;`
		);

		sequelizeInstance = new Sequelize(database, user, password, {
			dialect: "mysql",
			collate: "utf8mb4_unicode_ci",
               dialectModule: require('mysql2')
		});

		db.User = userModel(sequelizeInstance);
		db.Products = productModel(sequelizeInstance);
		db.ProductReviews = productReviews(sequelizeInstance);
		db.Categories = categoryModel(sequelizeInstance);

		await sequelizeInstance.sync({ alter: false, force: false });

		db.sequelize = sequelizeInstance;
		db.initialized = true;
	}
}

function userModel(sequelize) {
	const attributes = {
		email: { type: DataTypes.STRING, allowNull: false },
		hash: { type: DataTypes.STRING, allowNull: false },
		admin: {
			type: DataTypes.TINYINT(1),
			allowNull: true,
			defaultValue: "0",
		},
		firstName: { type: DataTypes.CHAR(64), allowNull: true },
		lastName: { type: DataTypes.CHAR(64), allowNull: true },
	};

	const options = {
		defaultScope: {
			attributes: { exclude: ["hash"] },
		},
		scopes: {
			withHash: { attributes: {} },
		},
	};

	return sequelize.define("users", attributes, options);
}

function productModel(sequelize) {
	const attributes = {
		name: { type: DataTypes.CHAR(255), allowNull: false },
		desc: { type: DataTypes.TEXT('long'), allowNull: true },
		keywords: { type: DataTypes.STRING, allowNull: true },
		price: { type: DataTypes.DECIMAL(10, 2), allowNull: false },
		priceReduced: { type: DataTypes.DECIMAL(10, 2), allowNull: true },
		stock: { type: DataTypes.SMALLINT.UNSIGNED, allowNull: true },
		images: { type: DataTypes.TEXT('long'), allowNull: true },
		variations: { type: DataTypes.JSON, allowNull: true },
          is_variable: { type: DataTypes.BOOLEAN, defaultValue: false ,allowNull: true }, 
		specifications: { type: DataTypes.JSON, allowNull: true },
		categoryName: { type: DataTypes.CHAR(84), allowNull: true },
		categorySlug: { type: DataTypes.CHAR(84), allowNull: true },
		subcategoryName: { type: DataTypes.CHAR(84), allowNull: true },
		subcategorySlug: { type: DataTypes.CHAR(84), allowNull: true },
		thirdCategoryName: { type: DataTypes.CHAR(84), allowNull: true },
		thirdCategorySlug: { type: DataTypes.CHAR(84), allowNull: true },
          url: { type: DataTypes.CHAR(255), allowNull: true }, 
          supplier: { type: DataTypes.CHAR(64), allowNull: true }, 
          sku: { type: DataTypes.CHAR(64), allowNull: true }, 
          ean: { type: DataTypes.CHAR(64), allowNull: true }, 
	};

	return sequelize.define("products", attributes);
}

function productReviews(sequelize) {
	const attributes = {
		userID: { type: DataTypes.INTEGER, allowNull: false },
		productID: { type: DataTypes.INTEGER, allowNull: false },
		userName: { type: DataTypes.CHAR(64), allowNull: true },
		userReview: { type: DataTypes.CHAR(255), allowNull: true },
		reviewStars: { type: DataTypes.TINYINT(1), allowNull: true },
	};

	return sequelize.define("products_reviews", attributes);
}

function categoryModel(sequelize) {
	const attributes = {
		name: { type: DataTypes.CHAR(64), allowNull: true },
		slug: { type: DataTypes.CHAR(64), allowNull: true },
		parentID: {
			type: DataTypes.TINYINT(2),
			allowNull: true,
			references: {
				model: "categories",
				key: "id",
			},
		},
		title: { type: DataTypes.CHAR(64), allowNull: true },
		description: { type: DataTypes.CHAR(255), allowNull: true },
		keywords: { type: DataTypes.CHAR(128), allowNull: true },
	};

	const Categories = sequelize.define("categories", attributes, {
		tableName: "categories",
		timestamps: false,
	});

	Categories.hasMany(Categories, {
		as: "children",
		foreignKey: "parentID",
	});

	Categories.belongsTo(Categories, {
		as: "parent",
		foreignKey: "parentID",
	});

	Categories.hasMany(Categories, {
		as: "subChildren",
		foreignKey: "parentID",
	});

	Categories.belongsTo(Categories, {
		as: "subParent",
		foreignKey: "parentID",
	});

	return Categories;
}

export default db;
