CREATE TABLE `audit_logs` (
	`id` bigint AUTO_INCREMENT NOT NULL,
	`user_id` int,
	`action` varchar(60) NOT NULL,
	`entity` varchar(60) NOT NULL,
	`entity_id` varchar(40),
	`before` json,
	`after` json,
	`ip` varchar(64),
	`user_agent` varchar(400),
	`created_at` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
	CONSTRAINT `audit_logs_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `households` (
	`id` int AUTO_INCREMENT NOT NULL,
	`rt_id` int NOT NULL,
	`housing_area_id` int,
	`kk_number_enc` text,
	`kk_number_hash` varchar(64),
	`head_name` varchar(120),
	`block` varchar(20),
	`house_number` varchar(20),
	`created_at` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
	`updated_at` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
	`deleted_at` datetime(3),
	`alive` tinyint GENERATED ALWAYS AS ((case when deleted_at is null then 1 else null end)) STORED,
	CONSTRAINT `households_id` PRIMARY KEY(`id`),
	CONSTRAINT `households_kk_uq` UNIQUE(`kk_number_hash`,`alive`)
);
--> statement-breakpoint
CREATE TABLE `housing_areas` (
	`id` int AUTO_INCREMENT NOT NULL,
	`rt_id` int NOT NULL,
	`name` varchar(120) NOT NULL,
	`type` enum('perumahan','blok','gang','cluster','lainnya') NOT NULL DEFAULT 'perumahan',
	`description` text,
	`is_active` boolean NOT NULL DEFAULT true,
	`created_at` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
	`updated_at` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
	`deleted_at` datetime(3),
	`alive` tinyint GENERATED ALWAYS AS ((case when deleted_at is null then 1 else null end)) STORED,
	CONSTRAINT `housing_areas_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `login_attempts` (
	`id` bigint AUTO_INCREMENT NOT NULL,
	`identifier` varchar(160) NOT NULL,
	`ip` varchar(64),
	`success` boolean NOT NULL,
	`created_at` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
	CONSTRAINT `login_attempts_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `members` (
	`id` int AUTO_INCREMENT NOT NULL,
	`resident_id` int NOT NULL,
	`member_number` varchar(30) NOT NULL,
	`joined_at` date NOT NULL,
	`coordinator_user_id` int,
	`collection_method` enum('antar_sendiri','dijemput','titik_kumpul') NOT NULL DEFAULT 'antar_sendiri',
	`is_active` boolean NOT NULL DEFAULT true,
	`is_demo` boolean NOT NULL DEFAULT false,
	`notes` text,
	`created_at` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
	`updated_at` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
	`deleted_at` datetime(3),
	`alive` tinyint GENERATED ALWAYS AS ((case when deleted_at is null then 1 else null end)) STORED,
	CONSTRAINT `members_id` PRIMARY KEY(`id`),
	CONSTRAINT `members_member_number_unique` UNIQUE(`member_number`),
	CONSTRAINT `members_resident_uq` UNIQUE(`resident_id`,`alive`)
);
--> statement-breakpoint
CREATE TABLE `permissions` (
	`id` int AUTO_INCREMENT NOT NULL,
	`code` varchar(60) NOT NULL,
	`name` varchar(120) NOT NULL,
	`group_name` varchar(40) NOT NULL,
	CONSTRAINT `permissions_id` PRIMARY KEY(`id`),
	CONSTRAINT `permissions_code_unique` UNIQUE(`code`)
);
--> statement-breakpoint
CREATE TABLE `regions` (
	`id` int AUTO_INCREMENT NOT NULL,
	`parent_id` int,
	`level` enum('provinsi','kabupaten','kecamatan','kelurahan') NOT NULL,
	`name` varchar(120) NOT NULL,
	`code` varchar(30),
	`created_at` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
	`updated_at` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
	CONSTRAINT `regions_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `residents` (
	`id` int AUTO_INCREMENT NOT NULL,
	`household_id` int,
	`rt_id` int NOT NULL,
	`housing_area_id` int,
	`nik_enc` text,
	`nik_hash` varchar(64),
	`nik_last4` varchar(4),
	`full_name` varchar(120) NOT NULL,
	`phone_enc` text,
	`phone_last4` varchar(4),
	`email` varchar(160),
	`block` varchar(20),
	`house_number` varchar(20),
	`resident_status` enum('tetap','kontrak','kos','lainnya') NOT NULL DEFAULT 'tetap',
	`photo_url` varchar(500),
	`notes` text,
	`is_active` boolean NOT NULL DEFAULT true,
	`is_demo` boolean NOT NULL DEFAULT false,
	`created_at` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
	`updated_at` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
	`deleted_at` datetime(3),
	`alive` tinyint GENERATED ALWAYS AS ((case when deleted_at is null then 1 else null end)) STORED,
	CONSTRAINT `residents_id` PRIMARY KEY(`id`),
	CONSTRAINT `residents_nik_uq` UNIQUE(`nik_hash`,`alive`)
);
--> statement-breakpoint
CREATE TABLE `role_permissions` (
	`role_id` int NOT NULL,
	`permission_id` int NOT NULL,
	CONSTRAINT `role_permissions_role_id_permission_id_pk` PRIMARY KEY(`role_id`,`permission_id`)
);
--> statement-breakpoint
CREATE TABLE `roles` (
	`id` int AUTO_INCREMENT NOT NULL,
	`code` varchar(40) NOT NULL,
	`name` varchar(80) NOT NULL,
	`description` text,
	`scope` varchar(10) NOT NULL DEFAULT 'self',
	`is_system` boolean NOT NULL DEFAULT true,
	`created_at` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
	`updated_at` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
	CONSTRAINT `roles_id` PRIMARY KEY(`id`),
	CONSTRAINT `roles_code_unique` UNIQUE(`code`)
);
--> statement-breakpoint
CREATE TABLE `rts` (
	`id` int AUTO_INCREMENT NOT NULL,
	`rw_id` int NOT NULL,
	`number` varchar(5) NOT NULL,
	`name` varchar(120) NOT NULL,
	`leader_name` varchar(120),
	`description` text,
	`is_active` boolean NOT NULL DEFAULT true,
	`created_at` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
	`updated_at` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
	`deleted_at` datetime(3),
	`alive` tinyint GENERATED ALWAYS AS ((case when deleted_at is null then 1 else null end)) STORED,
	CONSTRAINT `rts_id` PRIMARY KEY(`id`),
	CONSTRAINT `rts_rw_number_uq` UNIQUE(`rw_id`,`number`,`alive`)
);
--> statement-breakpoint
CREATE TABLE `rws` (
	`id` int AUTO_INCREMENT NOT NULL,
	`region_id` int NOT NULL,
	`number` varchar(5) NOT NULL,
	`name` varchar(120) NOT NULL,
	`is_active` boolean NOT NULL DEFAULT true,
	`created_at` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
	`updated_at` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
	`deleted_at` datetime(3),
	`alive` tinyint GENERATED ALWAYS AS ((case when deleted_at is null then 1 else null end)) STORED,
	CONSTRAINT `rws_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `sessions` (
	`id` varchar(64) NOT NULL,
	`user_id` int NOT NULL,
	`expires_at` datetime(3) NOT NULL,
	`ip` varchar(64),
	`user_agent` varchar(400),
	`created_at` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
	CONSTRAINT `sessions_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `settings` (
	`key` varchar(80) NOT NULL,
	`value` json NOT NULL,
	`updated_by` int,
	`updated_at` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
	CONSTRAINT `settings_key` PRIMARY KEY(`key`)
);
--> statement-breakpoint
CREATE TABLE `user_areas` (
	`user_id` int NOT NULL,
	`housing_area_id` int NOT NULL,
	CONSTRAINT `user_areas_user_id_housing_area_id_pk` PRIMARY KEY(`user_id`,`housing_area_id`)
);
--> statement-breakpoint
CREATE TABLE `users` (
	`id` int AUTO_INCREMENT NOT NULL,
	`name` varchar(120) NOT NULL,
	`username` varchar(60) NOT NULL,
	`email` varchar(160),
	`password_hash` varchar(255) NOT NULL,
	`role_id` int NOT NULL,
	`rw_id` int,
	`rt_id` int,
	`resident_id` int,
	`is_active` boolean NOT NULL DEFAULT true,
	`must_change_password` boolean NOT NULL DEFAULT false,
	`failed_login_count` int NOT NULL DEFAULT 0,
	`locked_until` datetime(3),
	`last_login_at` datetime(3),
	`last_login_ip` varchar(64),
	`created_at` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
	`updated_at` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
	`deleted_at` datetime(3),
	`alive` tinyint GENERATED ALWAYS AS ((case when deleted_at is null then 1 else null end)) STORED,
	CONSTRAINT `users_id` PRIMARY KEY(`id`),
	CONSTRAINT `users_username_uq` UNIQUE(`username`,`alive`),
	CONSTRAINT `users_email_uq` UNIQUE(`email`,`alive`)
);
--> statement-breakpoint
CREATE TABLE `waste_categories` (
	`id` int AUTO_INCREMENT NOT NULL,
	`code` varchar(30) NOT NULL,
	`name` varchar(80) NOT NULL,
	`group_name` enum('anorganik','organik','b3','residu') NOT NULL DEFAULT 'anorganik',
	`unit` varchar(10) NOT NULL DEFAULT 'kg',
	`description` text,
	`sort_order` int NOT NULL DEFAULT 0,
	`is_active` boolean NOT NULL DEFAULT true,
	`created_at` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
	`updated_at` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
	`deleted_at` datetime(3),
	`alive` tinyint GENERATED ALWAYS AS ((case when deleted_at is null then 1 else null end)) STORED,
	CONSTRAINT `waste_categories_id` PRIMARY KEY(`id`),
	CONSTRAINT `waste_categories_code_uq` UNIQUE(`code`,`alive`)
);
--> statement-breakpoint
CREATE TABLE `waste_prices` (
	`id` int AUTO_INCREMENT NOT NULL,
	`category_id` int NOT NULL,
	`price_per_unit` decimal(12,2) NOT NULL,
	`effective_from` date NOT NULL,
	`effective_to` date,
	`source` varchar(120),
	`notes` text,
	`created_by` int,
	`created_at` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
	CONSTRAINT `waste_prices_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
ALTER TABLE `audit_logs` ADD CONSTRAINT `audit_logs_user_id_users_id_fk` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `households` ADD CONSTRAINT `households_rt_id_rts_id_fk` FOREIGN KEY (`rt_id`) REFERENCES `rts`(`id`) ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `households` ADD CONSTRAINT `households_housing_area_id_housing_areas_id_fk` FOREIGN KEY (`housing_area_id`) REFERENCES `housing_areas`(`id`) ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `housing_areas` ADD CONSTRAINT `housing_areas_rt_id_rts_id_fk` FOREIGN KEY (`rt_id`) REFERENCES `rts`(`id`) ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `members` ADD CONSTRAINT `members_resident_id_residents_id_fk` FOREIGN KEY (`resident_id`) REFERENCES `residents`(`id`) ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `members` ADD CONSTRAINT `members_coordinator_user_id_users_id_fk` FOREIGN KEY (`coordinator_user_id`) REFERENCES `users`(`id`) ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `residents` ADD CONSTRAINT `residents_household_id_households_id_fk` FOREIGN KEY (`household_id`) REFERENCES `households`(`id`) ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `residents` ADD CONSTRAINT `residents_rt_id_rts_id_fk` FOREIGN KEY (`rt_id`) REFERENCES `rts`(`id`) ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `residents` ADD CONSTRAINT `residents_housing_area_id_housing_areas_id_fk` FOREIGN KEY (`housing_area_id`) REFERENCES `housing_areas`(`id`) ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `role_permissions` ADD CONSTRAINT `role_permissions_role_id_roles_id_fk` FOREIGN KEY (`role_id`) REFERENCES `roles`(`id`) ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `role_permissions` ADD CONSTRAINT `role_permissions_permission_id_permissions_id_fk` FOREIGN KEY (`permission_id`) REFERENCES `permissions`(`id`) ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `rts` ADD CONSTRAINT `rts_rw_id_rws_id_fk` FOREIGN KEY (`rw_id`) REFERENCES `rws`(`id`) ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `rws` ADD CONSTRAINT `rws_region_id_regions_id_fk` FOREIGN KEY (`region_id`) REFERENCES `regions`(`id`) ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `sessions` ADD CONSTRAINT `sessions_user_id_users_id_fk` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `settings` ADD CONSTRAINT `settings_updated_by_users_id_fk` FOREIGN KEY (`updated_by`) REFERENCES `users`(`id`) ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `user_areas` ADD CONSTRAINT `user_areas_user_id_users_id_fk` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `user_areas` ADD CONSTRAINT `user_areas_housing_area_id_housing_areas_id_fk` FOREIGN KEY (`housing_area_id`) REFERENCES `housing_areas`(`id`) ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `users` ADD CONSTRAINT `users_role_id_roles_id_fk` FOREIGN KEY (`role_id`) REFERENCES `roles`(`id`) ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `users` ADD CONSTRAINT `users_rw_id_rws_id_fk` FOREIGN KEY (`rw_id`) REFERENCES `rws`(`id`) ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `users` ADD CONSTRAINT `users_rt_id_rts_id_fk` FOREIGN KEY (`rt_id`) REFERENCES `rts`(`id`) ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `waste_prices` ADD CONSTRAINT `waste_prices_category_id_waste_categories_id_fk` FOREIGN KEY (`category_id`) REFERENCES `waste_categories`(`id`) ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `waste_prices` ADD CONSTRAINT `waste_prices_created_by_users_id_fk` FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE no action ON UPDATE no action;--> statement-breakpoint
CREATE INDEX `audit_entity_idx` ON `audit_logs` (`entity`,`entity_id`);--> statement-breakpoint
CREATE INDEX `audit_user_idx` ON `audit_logs` (`user_id`);--> statement-breakpoint
CREATE INDEX `audit_created_idx` ON `audit_logs` (`created_at`);--> statement-breakpoint
CREATE INDEX `households_rt_idx` ON `households` (`rt_id`);--> statement-breakpoint
CREATE INDEX `housing_areas_rt_idx` ON `housing_areas` (`rt_id`);--> statement-breakpoint
CREATE INDEX `login_attempts_ident_idx` ON `login_attempts` (`identifier`,`created_at`);--> statement-breakpoint
CREATE INDEX `login_attempts_ip_idx` ON `login_attempts` (`ip`,`created_at`);--> statement-breakpoint
CREATE INDEX `regions_parent_idx` ON `regions` (`parent_id`);--> statement-breakpoint
CREATE INDEX `residents_rt_idx` ON `residents` (`rt_id`);--> statement-breakpoint
CREATE INDEX `residents_area_idx` ON `residents` (`housing_area_id`);--> statement-breakpoint
CREATE INDEX `residents_name_idx` ON `residents` (`full_name`);--> statement-breakpoint
CREATE INDEX `sessions_user_idx` ON `sessions` (`user_id`);--> statement-breakpoint
CREATE INDEX `users_role_idx` ON `users` (`role_id`);--> statement-breakpoint
CREATE INDEX `waste_prices_cat_from_idx` ON `waste_prices` (`category_id`,`effective_from`);