import { Op } from 'sequelize';
import { BaseProvider } from '#templates/base/provider';
import { Post } from '#models/Post';
import { PostTarget } from '#models/PostTarget';
import { User } from '#models/User';
import { File } from '#models/File';
import { Resident } from '#models/Resident';
import { ResidentStay } from '#models/ResidentStay';
import { Bed } from '#models/Bed';
import { Room } from '#models/Room';
import { Apartment } from '#models/Apartment';
import sequelize from '#services/database/sequelize/sequelizeService';

export class PostProvider extends BaseProvider<Post> {
	public static instance: PostProvider;

	public static getInstance(): PostProvider {
		PostProvider.instance ??= new PostProvider();
		return PostProvider.instance;
	}

	public static get model() {
		return Post;
	}

	constructor() {
		super('Post');
	}

	/**
	 * Resolve all target IDs (room_id, apartment_id, building_id) accessible by a user.
	 * Chain: users → residents → resident_stays → beds → rooms → apartments → buildings
	 */
	private async resolveResidentTargetIds(userId: string): Promise<{
		roomIds: string[];
		apartmentIds: string[];
		buildingIds: string[];
	}> {
		const resident = await Resident.findOne({
			where: { user_id: userId, deleted_at: null },
			attributes: ['id'],
		});

		if (!resident) return { roomIds: [], apartmentIds: [], buildingIds: [] };

		const stays = await ResidentStay.findAll({
			where: { resident_id: resident.id },
			attributes: ['bed_id'],
		});

		const bedIds = stays.map((s) => s.bed_id);
		if (!bedIds.length) return { roomIds: [], apartmentIds: [], buildingIds: [] };

		const beds = await Bed.findAll({
			where: { id: { [Op.in]: bedIds }, deleted_at: null },
			attributes: ['room_id'],
		});

		const roomIds = [...new Set(beds.map((b) => b.room_id))];
		if (!roomIds.length) return { roomIds, apartmentIds: [], buildingIds: [] };

		const rooms = await Room.findAll({
			where: { id: { [Op.in]: roomIds }, deleted_at: null },
			attributes: ['id', 'apartment_id'],
		});

		const apartmentIds = [...new Set(rooms.map((r) => r.apartment_id))];

		const apartments = await Apartment.findAll({
			where: { id: { [Op.in]: apartmentIds }, deleted_at: null },
			attributes: ['id', 'building_id'],
		});

		const buildingIds = [...new Set(apartments.map((a) => a.building_id))];

		return { roomIds, apartmentIds, buildingIds };
	}

	/**
	 * Get all posts visible to the given user.
	 * Admins/system_admins see every post; regular users only see:
	 *  - target_type = 'all'
	 *  - target_type in ('building','floor','room') where target_id matches their location chain
	 * 'floor' is treated as apartment-level (apartment_id).
	 */
	public async getAllOptimized(options?: any, userId?: string, isAdmin = false) {
		const limit = Math.min(options?.pageSize || 20, 100);
		const offset = ((options?.page || 1) - 1) * limit;

		const order: any[] = options?.sortField
			? [[options.sortField, options.sortOrder || 'DESC']]
			: [['created_at', 'DESC']];

		// Build visibility WHERE
		let visibilityWhere: any;

		if (isAdmin) {
			visibilityWhere = {};
		} else if (userId) {
			const { roomIds, apartmentIds, buildingIds } = await this.resolveResidentTargetIds(userId);

			const orConditions: any[] = [{ target_type: 'all' }];

			if (buildingIds.length) {
				orConditions.push(
					sequelize.literal(
						`EXISTS (SELECT 1 FROM post_targets pt WHERE pt.post_id = "Post".id AND "Post".target_type = 'building' AND pt.target_id IN (${buildingIds.map((id) => `'${id}'`).join(',')}))`,
					),
				);
			}
			if (apartmentIds.length) {
				orConditions.push(
					sequelize.literal(
						`EXISTS (SELECT 1 FROM post_targets pt WHERE pt.post_id = "Post".id AND ("Post".target_type = 'floor' OR "Post".target_type = 'apartment') AND pt.target_id IN (${apartmentIds.map((id) => `'${id}'`).join(',')}))`,
					),
				);
			}
			if (roomIds.length) {
				orConditions.push(
					sequelize.literal(
						`EXISTS (SELECT 1 FROM post_targets pt WHERE pt.post_id = "Post".id AND "Post".target_type = 'room' AND pt.target_id IN (${roomIds.map((id) => `'${id}'`).join(',')}))`,
					),
				);
			}

			visibilityWhere = { [Op.or]: orConditions };
		} else {
			// Unauthenticated — only 'all' posts
			visibilityWhere = { target_type: 'all' };
		}

		try {
			// Base where: include provided filters but default non-admins to only 'published'
			const baseWhere: any = { deleted_at: null, ...(options?.filters || {}) };
			if (!isAdmin && baseWhere.status === undefined) {
				baseWhere.status = 'published';
			}

			const result = await Post.findAndCountAll({
				where: {
					...baseWhere,
					...visibilityWhere,
				},
				include: [
					{
						model: User,
						as: 'author',
						attributes: [],
						required: false,
					},
				],
				attributes: [
					'id',
					'title',
					'target_type',
					'status',
					'is_featured',
					'author_id',
					'thumbnail_id',
					'published_at',
					'created_at',
					'updated_at',
					[
						sequelize.fn(
							'CONCAT',
							sequelize.fn('COALESCE', sequelize.col('author.first_name'), ''),
							' ',
							sequelize.fn('COALESCE', sequelize.col('author.last_name'), ''),
						),
						'author_name',
					],
				],
				raw: true,
				subQuery: false,
				limit,
				offset,
				order,
				distinct: true,
			});

			return {
				count: result.count,
				rows: result.rows,
				page: options?.page || 1,
				pageSize: limit,
			};
		} catch (error) {
			await this.logError(error as Error);
			throw error;
		}
	}

	/**
	 * Get full detail of a single post including its target IDs.
	 */
	public async getByIdOptimized(id: string) {
		try {
			const post = await Post.findOne({
				where: { id, deleted_at: null },
				include: [
					{
						model: User,
						as: 'author',
						attributes: ['id', 'first_name', 'last_name', 'email'],
						required: false,
					},
					{
						model: PostTarget,
						as: 'post_targets',
						attributes: ['target_id'],
						required: false,
					},
					{
						model: File,
						as: 'thumbnail',
						attributes: [
							'id',
							'original_name',
							'object_key',
							'mime_type',
							'category',
							'size_bytes',
							'title',
							'description',
							'note',
							'is_library',
							'created_at',
							'updated_at',
							'created_by',
							'updated_by',
						],
						where: { deleted_at: null },
						required: false,
					},
				],
				raw: false,
			});

			if (!post) return null;

			return {
				id: post.id,
				title: post.title,
				content: post.content,
				thumbnail_id: (post as any).thumbnail_id ?? null,
				target_type: post.target_type,
				target_ids: (post.post_targets as PostTarget[]).map((t) => t.target_id),
				status: post.status,
				is_featured: post.is_featured,
				author_id: post.author_id,
				author: post.author
					? {
							id: (post.author as any).id,
							name: `${((post.author as any).first_name || '').trim()} ${((post.author as any).last_name || '').trim()}`.trim(),
							email: (post.author as any).email,
						}
					: null,
				published_at: post.published_at,
				thumbnail: (post as any).thumbnail
					? {
							id: (post as any).thumbnail.id,
							path: (post as any).thumbnail.object_key ?? '',
							name: (post as any).thumbnail.original_name ?? '',
							mime: (post as any).thumbnail.mime_type ?? '',
							type: (post as any).thumbnail.category ?? '',
							size: (post as any).thumbnail.size_bytes ?? null,
							compress_info: null,
							title: (post as any).thumbnail.title ?? null,
							description: (post as any).thumbnail.description ?? null,
							note: (post as any).thumbnail.note ?? null,
							is_library: !!(post as any).thumbnail.is_library,
							created_at: (post as any).thumbnail.created_at,
							updated_at: (post as any).thumbnail.updated_at ?? null,
							created_by: (post as any).thumbnail.created_by ?? null,
							updated_by: (post as any).thumbnail.updated_by ?? null,
						}
					: null,
				created_at: post.created_at,
				updated_at: post.updated_at,
			};
		} catch (error) {
			await this.logError(error as Error);
			throw error;
		}
	}

	/**
	 * Create a post and its associated post_targets in a single transaction.
	 */
	public async createWithTargets(
		data: {
			title: string;
			content?: string;
			target_type: string;
			target_ids: string[];
			is_featured?: boolean;
			status?: string;
			published_at?: Date | null;
			thumbnail_id?: string | null;
			author_id: string;
			created_by?: string | null;
		},
		transaction?: any,
	) {
		const t = transaction ?? (await sequelize.transaction());
		const ownTx = !transaction;

		try {
			const post = await Post.create(
				{
					title: data.title,
					content: data.content ?? null,
					target_type: data.target_type,
					status: data.status ?? 'draft',
					is_featured: data.is_featured ?? false,
					author_id: data.author_id,
					thumbnail_id: data.thumbnail_id ?? null,
					published_at: data.published_at ?? null,
					created_by: data.created_by ?? null,
					updated_by: data.created_by ?? null,
				} as any,
				{ transaction: t },
			);

			if (data.target_ids && data.target_ids.length > 0) {
				await PostTarget.bulkCreate(
					data.target_ids.map((target_id) => ({ post_id: post.id, target_id })),
					{ transaction: t, ignoreDuplicates: true },
				);
			}

			if (ownTx) await t.commit();
			return post;
		} catch (error) {
			if (ownTx) await t.rollback();
			throw error;
		}
	}

	/**
	 * Update a post and replace its target IDs.
	 */
	public async updateWithTargets(
		id: string,
		data: {
			title?: string;
			content?: string;
			target_type?: string;
			target_ids?: string[];
			is_featured?: boolean;
			status?: string;
			published_at?: Date | null;
			thumbnail_id?: string | null;
			updated_by?: string | null;
		},
	) {
		const t = await sequelize.transaction();
		try {
			const post = await Post.findOne({ where: { id, deleted_at: null }, transaction: t });
			if (!post) {
				await t.rollback();
				return null;
			}

			await post.update(
				{
					...(data.title !== undefined && { title: data.title }),
					...(data.content !== undefined && { content: data.content }),
					...(data.target_type !== undefined && { target_type: data.target_type }),
					...(data.thumbnail_id !== undefined && { thumbnail_id: data.thumbnail_id }),
					...(data.is_featured !== undefined && { is_featured: data.is_featured }),
					...(data.status !== undefined && { status: data.status }),
					...(data.published_at !== undefined && { published_at: data.published_at }),
					updated_by: data.updated_by ?? null,
					updated_at: new Date(),
				},
				{ transaction: t },
			);

			if (data.target_ids !== undefined) {
				await PostTarget.destroy({ where: { post_id: id }, transaction: t });
				if (data.target_ids.length > 0) {
					await PostTarget.bulkCreate(
						data.target_ids.map((target_id) => ({ post_id: id, target_id })),
						{ transaction: t, ignoreDuplicates: true },
					);
				}
			}

			await t.commit();
			return post;
		} catch (error) {
			await t.rollback();
			throw error;
		}
	}

	/**
	 * Soft-delete a post.
	 */
	public async softDelete(id: string, deletedBy?: string | null) {
		const post = await Post.findOne({ where: { id, deleted_at: null } });
		if (!post) return null;

		await post.update({
			deleted_at: new Date(),
			deleted_by: deletedBy ?? null,
		});

		return post;
	}
}
