import {
	type User,
	type InsertUser,
	type Subsidiary,
	type InsertSubsidiary,
	type Inventory,
	type InsertInventory,
	type Sale,
	type InsertSale,
	type ActivityLog,
	type InsertActivityLog,
	type GlobalSetting,
	type InsertGlobalSetting,
	users,
	subsidiaries,
	inventory,
	sales,
	activityLogs,
	globalSettings,
} from "@shared/schema";
import { db, isPostgres, isMysql, sql } from "./db";
import { eq, and, gte, lte } from "drizzle-orm";
import session from "express-session";
import { loadDbConfig } from "./config";
import createMemoryStore from "memorystore";
import connectPg from "connect-pg-simple";
import { scrypt, randomBytes } from "crypto";
import { promisify } from "util";

const scryptAsync = promisify(scrypt);
const config = loadDbConfig();

async function hashPassword(password: string) {
	const salt = randomBytes(16).toString("hex");
	const buf = (await scryptAsync(password, salt, 64)) as Buffer;
	return `${buf.toString("hex")}.${salt}`;
}

const MemoryStore = createMemoryStore(session);

export interface IStorage {
	getUser(id: number): Promise<User | undefined>;
	getUserByUsername(username: string): Promise<User | undefined>;
	createUser(user: InsertUser): Promise<User>;
	updateUser(id: number, user: Partial<InsertUser>): Promise<User>;
	deleteUser(id: number): Promise<void>;
	getSubsidiary(id: number): Promise<Subsidiary | undefined>;
	listSubsidiaries(): Promise<Subsidiary[]>;
	createSubsidiary(subsidiary: InsertSubsidiary): Promise<Subsidiary>;
	updateSubsidiary(id: number, subsidiary: Partial<InsertSubsidiary>): Promise<Subsidiary>;
	deleteSubsidiary(id: number): Promise<void>;
	getInventory(id: number): Promise<Inventory | undefined>;
	listInventoryBySubsidiary(subsidiaryId: number): Promise<Inventory[]>;
	createInventory(inventory: InsertInventory): Promise<Inventory>;
	updateInventory(id: number, inventory: Partial<InsertInventory>): Promise<Inventory>;
	deleteInventory(id: number): Promise<void>;
	createSale(sale: InsertSale): Promise<Sale>;
	listSalesBySubsidiary(subsidiaryId: number): Promise<Sale[]>;
	listSalesByDateRange(subsidiaryId: number, startDate: Date, endDate: Date): Promise<Sale[]>;
	deleteSale(id: number): Promise<void>;
	createActivityLog(log: InsertActivityLog): Promise<ActivityLog>;
	listActivityLogs(subsidiaryId?: number): Promise<ActivityLog[]>;
	getGlobalSetting(key: string): Promise<GlobalSetting | undefined>;
	upsertGlobalSetting(setting: InsertGlobalSetting): Promise<GlobalSetting>;
	sessionStore: session.Store;
	ensureDefaultAdmin(): Promise<void>;
	listUsersBySubsidiary(subsidiaryId: number): Promise<User[]>;
	listUsers(): Promise<User[]>;
}

export class DatabaseStorage implements IStorage {
	sessionStore: session.Store;

	constructor() {
		if (config.engine === 'postgresql' && process.env.DATABASE_URL) {
			const PostgresStore = connectPg(session);
			this.sessionStore = new PostgresStore({
				conString: process.env.DATABASE_URL,
				createTableIfMissing: true,
			});
		} else {
			this.sessionStore = new MemoryStore({
				checkPeriod: 86400000,
			});
		}
	}

	private async executeQuery<T>(operation: () => Promise<T>): Promise<T> {
		// Try up to 3 times with a short delay between attempts
		let retries = 3;
		let lastError: Error | null = null;

		while (retries > 0) {
			try {
				if (!db) {
					// Wait a bit for db to initialize if it's not ready yet
					await new Promise(resolve => setTimeout(resolve, 500));
					if (!db) {
						throw new Error("Database connection not initialized");
					}
				}
				return await operation();
			} catch (error) {
				lastError = error as Error;
				retries--;
				if (retries > 0) {
					console.log(`Database operation failed, retrying... (${retries} attempts remaining)`);
					await new Promise(resolve => setTimeout(resolve, 500));
				}
			}
		}

		console.error("Database operation failed:", lastError);
		throw lastError || new Error("Unknown database error");
	}

	async getUser(id: number): Promise<User | undefined> {
		return this.executeQuery(async () => {
			const result = await db.select().from(users).where(eq(users.id, id));
			return result[0];
		});
	}

	async getUserByUsername(username: string): Promise<User | undefined> {
		return this.executeQuery(async () => {
			const result = await db.select().from(users).where(eq(users.username, username));
			return result[0];
		});
	}

	async createUser(user: InsertUser): Promise<User> {
		return this.executeQuery(async () => {
			if (isPostgres(db)) {
				const [newUser] = await db.insert(users).values(user).returning();
				return newUser;
			} else {
				const result = await db.insert(users).values(user);
				const [createdUser] = await db.select().from(users).where(eq(users.id, result[0].insertId));
				return createdUser;
			}
		});
	}

	async updateUser(id: number, user: Partial<InsertUser>): Promise<User> {
		return this.executeQuery(async () => {
			if (isPostgres(db)) {
				const [updated] = await db
					.update(users)
					.set(user)
					.where(eq(users.id, id))
					.returning();
				if (!updated) throw new Error("User not found");
				return updated;
			} else {
				await db.update(users).set(user).where(eq(users.id, id));
				const [updatedUser] = await db.select().from(users).where(eq(users.id, id));
				if (!updatedUser) throw new Error("User not found");
				return updatedUser;
			}
		});
	}

	async deleteUser(id: number): Promise<void> {
		return this.executeQuery(async () => {
			// First, delete all activity logs associated with this user
			// This prevents foreign key constraint violations
			await db.delete(activityLogs).where(eq(activityLogs.userId, id));

			// Then delete the user
			await db.delete(users).where(eq(users.id, id));
		});
	}

	async getSubsidiary(id: number): Promise<Subsidiary | undefined> {
		return this.executeQuery(async () => {
			const result = await db.select().from(subsidiaries).where(eq(subsidiaries.id, id));
			return result[0];
		});
	}

	async listSubsidiaries(): Promise<Subsidiary[]> {
		return this.executeQuery(async () => {
			return db.select().from(subsidiaries);
		});
	}

	async createSubsidiary(subsidiary: InsertSubsidiary): Promise<Subsidiary> {
		return this.executeQuery(async () => {
			if (!subsidiary.name || !subsidiary.taxId || !subsidiary.email || !subsidiary.phoneNumber) {
				throw new Error('Missing required fields');
			}
			if (isPostgres(db)) {
				const [newSubsidiary] = await db
					.insert(subsidiaries)
					.values({
						name: subsidiary.name,
						taxId: subsidiary.taxId,
						email: subsidiary.email,
						phoneNumber: subsidiary.phoneNumber,
						address: subsidiary.address,
						city: subsidiary.city,
						country: subsidiary.country,
						status: subsidiary.status ?? true,
						logo: subsidiary.logo,
					})
					.returning();
				return newSubsidiary;
			} else {
				const result = await db.insert(subsidiaries).values({
					name: subsidiary.name,
					taxId: subsidiary.taxId,
					email: subsidiary.email,
					phoneNumber: subsidiary.phoneNumber,
					address: subsidiary.address,
					city: subsidiary.city,
					country: subsidiary.country,
					status: subsidiary.status ?? true,
				});
				const [createdSubsidiary] = await db.select().from(subsidiaries).where(eq(subsidiaries.id, result[0].insertId));
				return createdSubsidiary;
			}
		});
	}

	async updateSubsidiary(id: number, subsidiary: Partial<InsertSubsidiary>): Promise<Subsidiary> {
		return this.executeQuery(async () => {
			if (isPostgres(db)) {
				const [updated] = await db
					.update(subsidiaries)
					.set(subsidiary)
					.where(eq(subsidiaries.id, id))
					.returning();
				if (!updated) throw new Error("Subsidiary not found");
				return updated;
			} else {
				await db.update(subsidiaries).set(subsidiary).where(eq(subsidiaries.id, id));
				const [updatedSubsidiary] = await db.select().from(subsidiaries).where(eq(subsidiaries.id, id));
				if (!updatedSubsidiary) throw new Error("Subsidiary not found");
				return updatedSubsidiary;
			}
		});
	}

	async deleteSubsidiary(id: number): Promise<void> {
		return this.executeQuery(async () => {
			await db.transaction(async (tx) => {
				// Delete activity logs related to this subsidiary first
				await tx.delete(activityLogs).where(eq(activityLogs.subsidiaryId, id));
				
				// Then delete the subsidiary
				await tx.delete(subsidiaries).where(eq(subsidiaries.id, id));
			});
		});
	}

	async getInventory(id: number): Promise<Inventory | undefined> {
		return this.executeQuery(async () => {
			const result = await db.select().from(inventory).where(eq(inventory.id, id));
			return result[0];
		});
	}

	async listInventoryBySubsidiary(subsidiaryId: number): Promise<Inventory[]> {
		return this.executeQuery(async () => {
			return db.select().from(inventory).where(eq(inventory.subsidiaryId, subsidiaryId));
		});
	}

	async createInventory(item: InsertInventory): Promise<Inventory> {
		return this.executeQuery(async () => {
			if (isPostgres(db)) {
				const [newItem] = await db.insert(inventory).values(item).returning();
				return newItem;
			} else {
				const result = await db.insert(inventory).values(item);
				const [createdItem] = await db.select().from(inventory).where(eq(inventory.id, result[0].insertId));
				return createdItem;
			}
		});
	}

	async updateInventory(id: number, item: Partial<InsertInventory>): Promise<Inventory> {
		return this.executeQuery(async () => {
			if (isPostgres(db)) {
				const [updated] = await db
					.update(inventory)
					.set(item)
					.where(eq(inventory.id, id))
					.returning();
				if (!updated) throw new Error("Inventory item not found");
				return updated;
			} else {
				await db.update(inventory).set(item).where(eq(inventory.id, id));
				const [updatedItem] = await db.select().from(inventory).where(eq(inventory.id, id));
				if (!updatedItem) throw new Error("Inventory item not found");
				return updatedItem;
			}
		});
	}

	async deleteInventory(id: number): Promise<void> {
		return this.executeQuery(async () => {
			await db.delete(inventory).where(eq(inventory.id, id));
		});
	}

	async createSale(sale: InsertSale): Promise<Sale> {
		return this.executeQuery(async () => {
			const [inventoryItem] = await db
				.select()
				.from(inventory)
				.where(eq(inventory.id, sale.itemId));

			if (!inventoryItem) {
				throw new Error("Inventory item not found");
			}

			// Only check stock for non-service items (physical products)
			if (!inventoryItem.isService && inventoryItem.quantity < sale.quantity) {
				throw new Error("Insufficient stock");
			}

			const [newSale] = await db.transaction(async (tx) => {
				const [createdSale] = await tx
					.insert(sales)
					.values({
						...sale,
						
					})
					.returning();

				// Only update quantity for non-service items
				if (!inventoryItem.isService) {
					await tx
						.update(inventory)
						.set({
							quantity: inventoryItem.quantity - sale.quantity,
						})
						.where(eq(inventory.id, sale.itemId));
				}

				return [createdSale];
			});

			return newSale;
		});
	}

	async listSalesBySubsidiary(subsidiaryId: number): Promise<Sale[]> {
		return this.executeQuery(async () => {
			return db.select().from(sales).where(eq(sales.subsidiaryId, subsidiaryId));
		});
	}

	async listSalesByDateRange(subsidiaryId: number, startDate: Date, endDate: Date): Promise<Sale[]> {
		return this.executeQuery(async () => {
			return db.select({
				id: sales.id,
				subsidiaryId: sales.subsidiaryId,
				userId: sales.userId,
				itemId: sales.itemId,
				quantity: sales.quantity,
				salePrice: sales.salePrice,
				timestamp: sales.timestamp,
				clientName: sales.clientName,
				clientLastName: sales.clientLastName,
				nifNumber: sales.nifNumber,
				ninNumber: sales.ninNumber
			})
				.from(sales)
				.where(
					and(
						eq(sales.subsidiaryId, subsidiaryId),
						gte(sales.timestamp, startDate),
						lte(sales.timestamp, endDate)
					)
				)
				.orderBy(sales.timestamp);
		});
	}

	async deleteSale(id: number): Promise<void> {
		return this.executeQuery(async () => {
			// First, get the sale to check if it exists and to get item details for inventory adjustment
			const [saleToDelete] = await db.select().from(sales).where(eq(sales.id, id));

			if (!saleToDelete) {
				throw new Error(`Sale with ID ${id} not found`);
			}

			// Get the inventory item related to this sale
			const [inventoryItem] = await db.select().from(inventory).where(eq(inventory.id, saleToDelete.itemId));

			if (!inventoryItem) {
				throw new Error(`Inventory item with ID ${saleToDelete.itemId} not found`);
			}

			// Get the user who created the sale (for logging purposes)
			const [saleUser] = await db.select().from(users).where(eq(users.id, saleToDelete.userId));

			await db.transaction(async (tx) => {
				// Delete the sale record
				await tx.delete(sales).where(eq(sales.id, id));

				// Only adjust inventory quantity for non-service items
				if (!inventoryItem.isService) {
					// Return the quantity to inventory
					await tx.update(inventory)
						.set({
							quantity: inventoryItem.quantity + saleToDelete.quantity
						})
						.where(eq(inventory.id, saleToDelete.itemId));
				}

				// Log the activity
				await tx.insert(activityLogs).values({
					action: 'delete_sale',
					details: JSON.stringify({
						saleId: saleToDelete.id,
						itemName: inventoryItem.name,
						quantity: saleToDelete.quantity,
						salePrice: saleToDelete.salePrice,
						totalAmount: saleToDelete.quantity * saleToDelete.salePrice,
						inventoryRestored: !inventoryItem.isService,
						clientInfo: saleToDelete.clientName ? `${saleToDelete.clientName} ${saleToDelete.clientLastName || ''}`.trim() : "No client info"
					}),
					timestamp: saleToDelete.timestamp,
					userId: saleToDelete.userId,
					subsidiaryId: saleToDelete.subsidiaryId
				});
			});
		});
	}

	async createActivityLog(log: InsertActivityLog): Promise<ActivityLog> {
		return this.executeQuery(async () => {
			if (isPostgres(db)) {
				const [newLog] = await db.insert(activityLogs).values(log).returning();
				return newLog;
			} else {
				const result = await db.insert(activityLogs).values(log);
				const [createdLog] = await db.select().from(activityLogs).where(eq(activityLogs.id, result[0].insertId));
				return createdLog;
			}
		});
	}

	async listActivityLogs(subsidiaryId?: number): Promise<ActivityLog[]> {
		return this.executeQuery(async () => {
			if (subsidiaryId) {
				return db.select().from(activityLogs).where(eq(activityLogs.subsidiaryId, subsidiaryId));
			}
			return db.select().from(activityLogs);
		});
	}
	async ensureDefaultAdmin(): Promise<void> {
		try {
			const adminUser = await this.getUserByUsername("admin");
			if (!adminUser) {
				await this.createUser({
					username: "admin",
					password: await hashPassword("admin123"),
					role: "mhc_admin",
					subsidiaryId: null,
				});
				console.log('Default admin user created successfully');
			}
		} catch (error) {
			console.error('Error ensuring default admin:', error);
			throw error;
		}
	}
	async listUsersBySubsidiary(subsidiaryId: number): Promise<User[]> {
		return this.executeQuery(async () => {
			return db.select().from(users).where(eq(users.subsidiaryId, subsidiaryId));
		});
	}
	async listUsers(): Promise<User[]> {
		return this.executeQuery(async () => {
			return db.select().from(users);
		});
	}

	async getGlobalSetting(key: string): Promise<GlobalSetting | undefined> {
		return this.executeQuery(async () => {
			const result = await db.select().from(globalSettings).where(eq(globalSettings.key, key));
			return result[0];
		});
	}

	async upsertGlobalSetting(setting: InsertGlobalSetting): Promise<GlobalSetting> {
		return this.executeQuery(async () => {
			const existingSetting = await this.getGlobalSetting(setting.key);

			if (existingSetting) {
				// Update existing setting
				const updated = await db.update(globalSettings)
					.set({ value: setting.value, description: setting.description })
					.where(eq(globalSettings.key, setting.key))
					.returning();
				return updated[0];
			} else {
				// Insert new setting
				const newSetting = await db.insert(globalSettings)
					.values(setting)
					.returning();
				return newSetting[0];
			}
		});
	}
}

export const storage = new DatabaseStorage();

setTimeout(() => {
	storage.ensureDefaultAdmin().catch(console.error);
}, 2000);