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);
          db.Orders = ordersModel(sequelizeInstance); 
          db.Returns = returnsModel(sequelizeInstance); 
          db.Conforms = conformsModel(sequelizeInstance); 
          db.Settings = settingsModel(sequelizeInstance); 
          db.UserAddress = adressesModel(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",
		},
          marketplace: { type: DataTypes.TINYINT(1), allowNull: true, defaultValue: "0" }, 
          marketplace_avatar: { type: DataTypes.CHAR(128), allowNull: true, defaultValue: "https://emagyl.s3.eu-north-1.amazonaws.com/avatar/marketplace.webp" }, 
		firstName: { type: DataTypes.CHAR(64), allowNull: true },
		lastName: { type: DataTypes.CHAR(64), allowNull: true },
          phone: { type: DataTypes.CHAR(32), allowNull: true }, 
          nume_firma: { type: DataTypes.CHAR(64), allowNull: true }, 
          cui_firma: { type: DataTypes.CHAR(32), allowNull: true }, 
          nr_inregistrare_firma: { type: DataTypes.CHAR(64), allowNull: true }, 
          adresa_firma: { type: DataTypes.CHAR(90), 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 },
          name_en: { type: DataTypes.CHAR(255), allowNull: true }, 
          name_de: { type: DataTypes.CHAR(255), allowNull: true },
          name_it: { type: DataTypes.CHAR(255), allowNull: true }, 
          name_fr: { type: DataTypes.CHAR(255), allowNull: true }, 
		desc: { type: DataTypes.TEXT('long'), allowNull: true },
          desc_en: { type: DataTypes.TEXT('long'), allowNull: true },
          desc_fr: { type: DataTypes.TEXT('long'), allowNull: true },
          desc_de: { type: DataTypes.TEXT('long'), allowNull: true },
          desc_it: { 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 }, 
          back_supplier: { type: DataTypes.CHAR(32), allowNull: true, defaultValue: "AVEX" }, 
          sku: { type: DataTypes.CHAR(64), allowNull: true }, 
          ean: { type: DataTypes.CHAR(64), allowNull: true },
          listedBy: { type: DataTypes.INTEGER(11), allowNull: true },
          verified: { type: DataTypes.TINYINT(1), allowNull: true, defaultValue: 0 },
          rejectReason: { type: DataTypes.CHAR(128), allowNull: true },
          visible: { type: DataTypes.TINYINT(1), allowNull: true, defaultValue: 1 }, 
	};

	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 },
          name_en: { type: DataTypes.CHAR(64), allowNull: true }, 
          name_fr: { type: DataTypes.CHAR(64), allowNull: true },
          name_de: { type: DataTypes.CHAR(64), allowNull: true },
          name_it: { 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 },
          margin_avex: { type: DataTypes.DECIMAL(10, 2), allowNull: true },
          margin_depox: { type: DataTypes.DECIMAL(10, 2), 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 function ordersModel(sequelize) {
     const attributes = {
          id: { type: DataTypes.INTEGER, autoIncrement: true, primaryKey: true },
          billingType: { type: DataTypes.TINYINT, allowNull: true, defaultValue: 0 },

          pf_prenume: { type: DataTypes.CHAR(64), allowNull: true },
          pf_nume_familie: { type: DataTypes.CHAR(64), allowNull: true },
          pf_tara: { type: DataTypes.CHAR(40), allowNull: true },
          pf_judet_oras: { type: DataTypes.CHAR(64), allowNull: true },
          pf_adresa: { type: DataTypes.CHAR(90), allowNull: true }, 
          pf_numar_telefon: { type: DataTypes.CHAR(64), allowNull: true },
          pf_email: { type: DataTypes.CHAR(64), allowNull: true },
          pf_mentiuni: { type: DataTypes.CHAR(64), allowNull: true },

          pj_nume_firma: { type: DataTypes.CHAR(64), allowNull: true },
          pj_cui: { type: DataTypes.CHAR(32), allowNull: true },
          pj_nr_reg: { type: DataTypes.CHAR(64), allowNull: true },
          pj_banca: { type: DataTypes.CHAR(64), allowNull: true },
          pj_iban: { type: DataTypes.CHAR(64), allowNull: true },
          pj_judet: { type: DataTypes.CHAR(64), allowNull: true },
          pj_localitate: { type: DataTypes.CHAR(64), allowNull: true },
          pj_adresa: { type: DataTypes.CHAR(90), allowNull: true },
          pj_adresa_livrare: { type: DataTypes.CHAR(90), allowNull: true },
          pj_numar_telefon: { type: DataTypes.CHAR(64), allowNull: true },
          pj_email: { type: DataTypes.CHAR(64), allowNull: true },
          pj_mentiuni: { type: DataTypes.CHAR(64), allowNull: true },

          userID: { type: DataTypes.INTEGER, allowNull: true, defaultValue: 0 },
          sessionID: { type: DataTypes.CHAR(255), allowNull: true },
          status: { type: DataTypes.CHAR(32), allowNull: true, defaultValue: "pending" },  
          items: { type: DataTypes.JSON, allowNull: true }, 
          bill: { type: DataTypes.CHAR(255), allowNull: true }, 
          bill_number: { type: DataTypes.CHAR(32), allowNull: true }, 
          bill_series: { type: DataTypes.CHAR(16), allowNull: true },
          certificate: { type: DataTypes.CHAR(128), allowNull: true }, 
          informations: { type: DataTypes.CHAR(128), allowNull: true }, 

          netopia_ntpID: { type: DataTypes.CHAR(128), allowNull: true },
          netopia_payment_id: { type: DataTypes.CHAR(128), allowNull: true },
          total_amount: { type: DataTypes.DECIMAL(10, 2), allowNull: true },
          currency: { type: DataTypes.CHAR(3), allowNull: true, defaultValue: 'RON' }, 
          awb: { type: DataTypes.CHAR(32), allowNull: true, defaultValue: 'Cargus' },
          awbNumber: { type: DataTypes.CHAR(32), allowNull: true },  
          awbUrl: { type: DataTypes.CHAR(255), allowNull: true }, 
          payment_method: { type: DataTypes.CHAR(64), allowNull: true }, 
     };

     return sequelize.define("orders", attributes, {
          tableName: "orders",
          timestamps: true
     });
}

export function returnsModel(sequelize) {
     const attributes = {
          id: { type: DataTypes.INTEGER, autoIncrement: true, primaryKey: true },
          userID: { type: DataTypes.INTEGER, allowNull: true },
          orderID: { type: DataTypes.CHAR(20), allowNull: true }, 
          selectedItems: { type: DataTypes.JSON, allowNull: true }, 
          reason: { type: DataTypes.CHAR(64), allowNull: true }, 
          details: { type: DataTypes.CHAR(64), allowNull: true }, 
          refundMethod: { type: DataTypes.CHAR(64), allowNull: true },
          iban: { type: DataTypes.CHAR(64), allowNull: true },
          accountName: { type: DataTypes.CHAR(64), allowNull: true },
          returnMethod: { type: DataTypes.CHAR(64), allowNull: true }
     };

     return sequelize.define("returns", attributes, {
          tableName: "returns",
          timestamps: true
     });
}

export function conformsModel(sequelize) {
     const attributes = {
          id: { type: DataTypes.INTEGER, autoIncrement: true, primaryKey: true },
          userID: { type: DataTypes.INTEGER, allowNull: true },
          name: { type: DataTypes.CHAR(64), allowNull: true }, 
          email: { type: DataTypes.CHAR(64), allowNull: true }, 
          phone: { type: DataTypes.CHAR(64), allowNull: true }, 
          orderID: { type: DataTypes.INTEGER, allowNull: true },
          selectedItems: { type: DataTypes.JSON, allowNull: true }, 
     };

     return sequelize.define("conforms", attributes, {
          tableName: "conforms",
          timestamps: true
     });
}

export function settingsModel(sequelize) {
     const attributes = {
          id: { type: DataTypes.INTEGER, autoIncrement: true, primaryKey: true },
          name: { type: DataTypes.CHAR(150), allowNull: true },
          value: { type: DataTypes.TEXT('long'), allowNull: true }, 
     };

     return sequelize.define("settings", attributes, {
          tableName: "settings",
          timestamps: false
     });
}

function adressesModel(sequelize) {
	const attributes = {
		userID: { type: DataTypes.INTEGER, allowNull: true }, 
          label: { type: DataTypes.CHAR(64), allowNull: true }, 
          billingType: { type: DataTypes.TINYINT, allowNull: true, defaultValue: 0 }, 
          pf_prenume: { type: DataTypes.CHAR(64), allowNull: true }, 
          pf_nume_familie: { type: DataTypes.CHAR(64), allowNull: true },
          pf_tara: { type: DataTypes.CHAR(40), allowNull: true },
          pf_judet_oras: { type: DataTypes.CHAR(64), allowNull: true },
          pf_adresa: { type: DataTypes.CHAR(90), allowNull: true },
          pf_numar_telefon: { type: DataTypes.CHAR(64), allowNull: true },
          pf_email: { type: DataTypes.CHAR(64), allowNull: true },
          pj_nume_firma: { type: DataTypes.CHAR(64), allowNull: true },
          pj_cui: { type: DataTypes.CHAR(32), allowNull: true },
          pj_nr_reg: { type: DataTypes.CHAR(64), allowNull: true },
          pj_banca: { type: DataTypes.CHAR(64), allowNull: true },
          pj_iban: { type: DataTypes.CHAR(64), allowNull: true }, 
          pj_judet: { type: DataTypes.CHAR(64), allowNull: true }, 
          pj_localitate: { type: DataTypes.CHAR(64), allowNull: true }, 
          pj_adresa: { type: DataTypes.CHAR(90), allowNull: true }, 
          pj_adresa_livrare: { type:DataTypes.CHAR(90), allowNull: true }, 
          pj_numar_telefon: { type: DataTypes.CHAR(64), allowNull: true }, 
          pj_email: { type: DataTypes.CHAR(64), allowNull: true }, 
          isDefault: { type: DataTypes.TINYINT(1), allowNull: true, defaultValue: 0 }, 
	};

     return sequelize.define("users_addresses", attributes, {
          tableName: "users_addresses",
          timestamps: true
     });
}


export default db;
