import { open, Database } from "sqlite";
import sqlite3 from "sqlite3";
import _ from "lodash";
import Base from "./base";
import { promises as fs } from "fs";
import { Banlist, generateBanlistFromCode, mergeBanlists } from "./utility";
import { CardPool, DeckGenerator } from "./deck";
import moment from "moment";

const textsFields = ["id", "name", "desc"]
for (let i = 1; i <= 16; ++i) {
	textsFields.push(`str${i}`);
}
const datasFields = ["id", "ot", "alias", "setcode", "type", "atk", "def", "level", "race", "attribute", "category"];

export class SQLQuery {
	sql: string;
	values: any[];
	constructor(sql: string, values: any[]) {
		this.sql = sql;
		this.values = values;
	}
	async perform(db: Database) {
		//console.log(db.config.filename, this.sql, this.values);
		await db.run(this.sql, this.values);
	}
}

export class Card {
	code: number;
	datas: any;
	texts: any;
	preDatas: any;
	preTexts: any;
	loadedDatas: any;
	loadedTexts: any;
	constructor(code: number) {
		this.code = code;
		this.datas = {};
		this.texts = {};
	}
	private getDatasArray(): any[] {
		const ret = [];
		for (let field of datasFields) {
			ret.push(this.datas[field]);
		}
		return ret;
	}
	private getTextsArray(): any[] {
		const ret = [];
		for (let field of textsFields) {
			ret.push(this.texts[field]);
		}
		return ret;
	}
	private async createCardFromRelatedCode(code: number, db: Database) {
		const card = new Card(code);
		await card.loadData(db);
		if (this.loadedTexts.name === card.loadedTexts.name) { // is alias
			card.texts = _.clone(this.texts);
			card.datas = _.clone(this.datas);
			card.texts.id = code;
			card.datas.id = code;
			card.datas.alias = this.code;
		}
		return card;
	}
	async getRelatedCards(db: Database) {
		const code = this.code;
		if (code === 5405694) return [];
		const moreCodes: number[] = (await db.all('SELECT id FROM datas WHERE id > ? AND id <= ? AND (alias = ? OR (type & 0x4000) > 0)', [code, code + 20, code])).map(m => m.id);
		const cards = await Promise.all(moreCodes.map(code => this.createCardFromRelatedCode(code, db)));
		return cards;
	}
	isInMainDeck() {
		const cardType: number = this.datas.type;
		if (cardType === undefined) {
			throw `Card data is not loaded`;
		}
		return (cardType & (0x4000000 | 0x800000 | 0x4000 | 0x2000 | 0x40)) == 0;
	}
	isInExtraDeck() {
		const cardType: number = this.datas.type;
		if (cardType === undefined) {
			throw `Card data is not loaded`;
		}
		return (cardType & (0x4000000 | 0x800000 | 0x2000 | 0x40)) > 0;
	}
	async loadData(db: Database) {
		this.preDatas = this.datas;
		this.preTexts = this.texts;
		this.loadedDatas = await db.get("select * from datas where id = ?", [this.code]);
		this.loadedTexts = await db.get("select * from texts where id = ?", [this.code]);
		this.datas = {
			...this.loadedDatas,
			...this.preDatas
		}
		this.texts = {
			...this.loadedTexts,
			...this.preTexts
		}
	}
	getSQLQueries() {
		const datasArray = this.getDatasArray();
		const textsArray = this.getTextsArray();
		return [
			new SQLQuery(`INSERT INTO texts VALUES(${_.range(textsArray.length).map(m => "?")});`, textsArray),
			new SQLQuery(`INSERT INTO datas VALUES(${_.range(datasArray.length).map(m => "?")});`, datasArray)
		]
	}
}

export class DBReader extends Base {
	jpdb: Database;
	cndb: Database;
	outputdb: Database;
	overrideTextDb: Database;
	async openDatabase(path: string) {
		return await open({
			filename: path,
			driver: sqlite3.Database
		});
	}
	async init() {
		await super.init();
		this.log.debug(`Opening databases...`);
		this.cndb = await this.openDatabase(this.config.cnDatabasePath);
		this.jpdb = await this.openDatabase(this.config.jpDatabasePath);
		this.overrideTextDb = this.config.overrideTextDbPath ? await this.openDatabase(this.config.overrideTextDbPath) : null;
	}
	async finalize() {
		await this.cndb.close();
		await this.jpdb.close();
		if (this.outputdb) {
			await this.outputdb.close();
		}
		if (this.overrideTextDb) {
			await this.overrideTextDb.close();
		}
	}
	private async openOutputDatabase() {
		const fullPath = `${this.config.outputPath}/expansions/${this.config.fileSymbol}.cdb`;
		const createDirectoryPaths = [
			`${this.config.outputPath}/deck/${this.config.fileSymbol}`,
			`${this.config.outputPath}/expansions`
		];
		for (let createDirectoryPath of createDirectoryPaths) {
			try {
				await fs.access(createDirectoryPath);
			} catch (e) {
				this.log.debug(`Creating directory ${createDirectoryPath} ...`);
				await fs.mkdir(createDirectoryPath, { recursive: true });
			}
		}
		try {
			await fs.unlink(fullPath);
		} catch (e) { }
		this.log.debug(`Creating database ${fullPath} ...`);
		this.outputdb = await this.openDatabase(fullPath);
		const initSQLs = [
			"PRAGMA foreign_keys=OFF;",
			"BEGIN TRANSACTION;",
			"CREATE TABLE texts(id integer primary key,name text,desc text,str1 text,str2 text,str3 text,str4 text,str5 text,str6 text,str7 text,str8 text,str9 text,str10 text,str11 text,str12 text,str13 text,str14 text,str15 text,str16 text);",
			"CREATE TABLE datas(id integer primary key,ot integer,alias integer,setcode integer,type integer,atk integer,def integer,level integer,race integer,attribute integer,category integer);",
			"COMMIT;"
		];
		for (let sql of initSQLs) {
			await this.outputdb.run(sql);
		}
	}
	async getCardFromJapaneseName(name: string) {
		this.log.debug(`Reading JP database for code of name ${name}.`);
		const output = await this.jpdb.get('SELECT id FROM texts WHERE name = ?', name);
		if (!output) {
			this.log.debug(`Code of ${name} not found.`);
			return null;
		}
		const code: number = output.id;
		this.log.debug(`${name} => ${code}`);
		const card = new Card(code);
		//card.texts.name = name;
		return card;
	}
	async getAllCardsFromJapaneseNames(names: string[]) {
		const cards: Card[] = [];
		for (let name of names) {
			const card = await this.getCardFromJapaneseName(name);
			if (card && _.every(cards, c => c.code !== card.code)) {
				cards.push(card);
			}
		}
		return cards;
	}
	async getOtherCardCodes(cnCodes: number[]) {
		const sql = `SELECT id FROM datas WHERE id not IN (${cnCodes.join(",")})`;
		const otherCodes: number[] = (await this.cndb.all(sql)).map(m => m.id);
		return otherCodes;
	}
	async generateBanlist(codes: number[], extraBanlists: Banlist[] = []) {
		const otherCodes = await this.getOtherCardCodes(codes);
		const generatedBanlist: Banlist = {
			name: moment().format('YYYY.MM') + ' ' + this.config.fileSymbol,
			list: [
				otherCodes
			]
		};
		const banlistString = await generateBanlistFromCode(
			extraBanlists?.length
				? extraBanlists.map(l => mergeBanlists([generatedBanlist, l], l.name))
				: [generatedBanlist]
		);
		await fs.writeFile(`${this.config.outputPath}/expansions/lflist.conf`, banlistString);
	}
	async generatePatch(codes: number[]) {
		const patchString = `update datas set ot = ot | 0x8 where id IN (${codes.join(',')});`;
		await fs.writeFile(`${this.config.outputPath}/patch.sql`, patchString);
	}
	private async categorizeCards(cards: Card[]): Promise<CardPool> {
		const main = cards.filter(card => card.isInMainDeck()).map(card => card.code);
		const extra = cards.filter(card => card.isInExtraDeck()).map(card => card.code);
		return {
			main,
			extra
		}
	}
	private async generateDecks(cards: Card[]) {
		const cardPool = await this.categorizeCards(cards);
		const deckGenerator = new DeckGenerator(cardPool);
		const deckTexts = deckGenerator.getDeckTexts();
		await Promise.all(_.range(deckTexts.length).map(i => fs.writeFile(`${this.config.outputPath}/deck/${this.config.fileSymbol}/${this.config.fileSymbol}_${i}.ydk`, deckTexts[i])));
	}
	async run(cards: Card[], options: {
		extraBanlists?: Banlist[]
	} = {}) {
		await Promise.all(cards.map(card => card.loadData(this.cndb)));
		const extendedCards = (await Promise.all(cards.map(card => card.getRelatedCards(this.cndb)))).flat();
		const allCards = _.uniqBy(cards.concat(extendedCards), (s) => s.code);
		await Promise.all(allCards.map(async(card) => {
			if (this.config.fileSymbol === 'cn') {
				card.datas.ot |= 0x8;
			}
			if (this.overrideTextDb) {
				const overrideTextEntry = await this.overrideTextDb.get('SELECT desc FROM texts WHERE id = ?', [card.code]);
				if (overrideTextEntry) {
					card.texts.desc = overrideTextEntry.desc;
				}
			}
			card.texts.desc += '\r\n\r\n\u2605' + this.config.descSymbol;
		}));
		const queries = allCards.flatMap(card => card.getSQLQueries());
		await this.openOutputDatabase();
		await this.outputdb.run("BEGIN TRANSACTION;");
		for (let query of queries) {
			this.log.debug(`Writing database: ${query.sql} ${query.values.join(",")}`);
			await query.perform(this.outputdb);
		}
		await this.outputdb.run("COMMIT;");
		const allCodes = allCards.map(card => card.code);
		this.log.info(`Database created.`);
		await this.generateBanlist(allCodes, options.extraBanlists || []);
		this.log.info(`LFList created.`);
		if (this.config.fileSymbol === 'cn') { 
			await this.generatePatch(allCodes);
			this.log.info(`Patch created.`);
		}
		await this.generateDecks(cards);
		this.log.info(`Decks generated.`);
	}

	async getAllCardsFromDatabase(dbPath: string) {
		try {
			const db = await this.openDatabase(dbPath);
			const codes: number[] = (await db.all('SELECT id FROM datas where type & 0x4000 = 0 and not exists (select 1 from datas orig where orig.id = datas.alias and orig.id < datas.id and orig.id >= (datas.id - 20))')).map(m => m.id);
			await db.close();
			return codes;
		} catch (e) {
			this.log.error(`Error getting all cards from database: ${e}`);
			return [];
		}
	}
}
