/*
 Navicat Premium Data Transfer

 Source Server         : anjabbawah
 Source Server Type    : PostgreSQL
 Source Server Version : 150003 (150003)
 Source Host           : localhost:5432
 Source Catalog        : sppai
 Source Schema         : public

 Target Server Type    : PostgreSQL
 Target Server Version : 150003 (150003)
 File Encoding         : 65001

 Date: 03/12/2025 22:49:10
*/


-- ----------------------------
-- Sequence structure for abc_activities_id_seq
-- ----------------------------
DROP SEQUENCE IF EXISTS "public"."abc_activities_id_seq";
CREATE SEQUENCE "public"."abc_activities_id_seq" 
INCREMENT 1
MINVALUE  1
MAXVALUE 2147483647
START 1
CACHE 1;

-- ----------------------------
-- Sequence structure for abc_activity_allocations_id_seq
-- ----------------------------
DROP SEQUENCE IF EXISTS "public"."abc_activity_allocations_id_seq";
CREATE SEQUENCE "public"."abc_activity_allocations_id_seq" 
INCREMENT 1
MINVALUE  1
MAXVALUE 9223372036854775807
START 1
CACHE 1;

-- ----------------------------
-- Sequence structure for abc_cost_categories_id_seq
-- ----------------------------
DROP SEQUENCE IF EXISTS "public"."abc_cost_categories_id_seq";
CREATE SEQUENCE "public"."abc_cost_categories_id_seq" 
INCREMENT 1
MINVALUE  1
MAXVALUE 2147483647
START 1
CACHE 1;

-- ----------------------------
-- Sequence structure for abc_drivers_id_seq
-- ----------------------------
DROP SEQUENCE IF EXISTS "public"."abc_drivers_id_seq";
CREATE SEQUENCE "public"."abc_drivers_id_seq" 
INCREMENT 1
MINVALUE  1
MAXVALUE 2147483647
START 1
CACHE 1;

-- ----------------------------
-- Sequence structure for abc_resource_assignments_id_seq
-- ----------------------------
DROP SEQUENCE IF EXISTS "public"."abc_resource_assignments_id_seq";
CREATE SEQUENCE "public"."abc_resource_assignments_id_seq" 
INCREMENT 1
MINVALUE  1
MAXVALUE 9223372036854775807
START 1
CACHE 1;

-- ----------------------------
-- Sequence structure for migrations_id_seq
-- ----------------------------
DROP SEQUENCE IF EXISTS "public"."migrations_id_seq";
CREATE SEQUENCE "public"."migrations_id_seq" 
INCREMENT 1
MINVALUE  1
MAXVALUE 9223372036854775807
START 1
CACHE 1;

-- ----------------------------
-- Sequence structure for ms_academic_years_id_seq
-- ----------------------------
DROP SEQUENCE IF EXISTS "public"."ms_academic_years_id_seq";
CREATE SEQUENCE "public"."ms_academic_years_id_seq" 
INCREMENT 1
MINVALUE  1
MAXVALUE 2147483647
START 1
CACHE 1;

-- ----------------------------
-- Sequence structure for ms_departments_id_seq
-- ----------------------------
DROP SEQUENCE IF EXISTS "public"."ms_departments_id_seq";
CREATE SEQUENCE "public"."ms_departments_id_seq" 
INCREMENT 1
MINVALUE  1
MAXVALUE 2147483647
START 1
CACHE 1;

-- ----------------------------
-- Sequence structure for ms_faculties_id_seq
-- ----------------------------
DROP SEQUENCE IF EXISTS "public"."ms_faculties_id_seq";
CREATE SEQUENCE "public"."ms_faculties_id_seq" 
INCREMENT 1
MINVALUE  1
MAXVALUE 2147483647
START 1
CACHE 1;

-- ----------------------------
-- Sequence structure for ms_menus_id_seq
-- ----------------------------
DROP SEQUENCE IF EXISTS "public"."ms_menus_id_seq";
CREATE SEQUENCE "public"."ms_menus_id_seq" 
INCREMENT 1
MINVALUE  1
MAXVALUE 2147483647
START 1
CACHE 1;

-- ----------------------------
-- Sequence structure for ms_study_programs_id_seq
-- ----------------------------
DROP SEQUENCE IF EXISTS "public"."ms_study_programs_id_seq";
CREATE SEQUENCE "public"."ms_study_programs_id_seq" 
INCREMENT 1
MINVALUE  1
MAXVALUE 2147483647
START 1
CACHE 1;

-- ----------------------------
-- Sequence structure for rpt_unit_costs_id_seq
-- ----------------------------
DROP SEQUENCE IF EXISTS "public"."rpt_unit_costs_id_seq";
CREATE SEQUENCE "public"."rpt_unit_costs_id_seq" 
INCREMENT 1
MINVALUE  1
MAXVALUE 9223372036854775807
START 1
CACHE 1;

-- ----------------------------
-- Sequence structure for trans_driver_values_id_seq
-- ----------------------------
DROP SEQUENCE IF EXISTS "public"."trans_driver_values_id_seq";
CREATE SEQUENCE "public"."trans_driver_values_id_seq" 
INCREMENT 1
MINVALUE  1
MAXVALUE 9223372036854775807
START 1
CACHE 1;

-- ----------------------------
-- Sequence structure for trans_resources_id_seq
-- ----------------------------
DROP SEQUENCE IF EXISTS "public"."trans_resources_id_seq";
CREATE SEQUENCE "public"."trans_resources_id_seq" 
INCREMENT 1
MINVALUE  1
MAXVALUE 9223372036854775807
START 1
CACHE 1;

-- ----------------------------
-- Sequence structure for users_id_seq
-- ----------------------------
DROP SEQUENCE IF EXISTS "public"."users_id_seq";
CREATE SEQUENCE "public"."users_id_seq" 
INCREMENT 1
MINVALUE  1
MAXVALUE 2147483647
START 1
CACHE 1;

-- ----------------------------
-- Table structure for abc_activities
-- ----------------------------
CREATE TABLE "public"."abc_activities" (
  "id" int4 NOT NULL DEFAULT nextval('abc_activities_id_seq'::regclass),
  "name" varchar(150) COLLATE "pg_catalog"."default" NOT NULL,
  "description" text COLLATE "pg_catalog"."default",
  "level_scope" varchar(20) COLLATE "pg_catalog"."default" NOT NULL DEFAULT 'PRODI'::character varying,
  "created_at" timestamp(6),
  "updated_at" timestamp(6)
)
;

-- ----------------------------
-- Records of abc_activities
-- ----------------------------
INSERT INTO "public"."abc_activities" VALUES (1, 'Perkuliahan Teori (Kelas)', 'Kegiatan tatap muka di kelas', 'PRODI', NULL, NULL);
INSERT INTO "public"."abc_activities" VALUES (2, 'Praktikum Laboratorium', 'Kegiatan praktek di lab', 'PRODI', NULL, NULL);
INSERT INTO "public"."abc_activities" VALUES (3, 'Bimbingan Tugas Akhir/Skripsi', 'Konsultasi mahasiswa tingkat akhir', 'PRODI', NULL, NULL);
INSERT INTO "public"."abc_activities" VALUES (4, 'Pengelolaan Akademik Fakultas', 'Layanan administrasi dekanat', 'FACULTY', NULL, NULL);
INSERT INTO "public"."abc_activities" VALUES (5, 'Penjaminan Mutu Fakultas', 'Audit internal, akreditasi', 'FACULTY', NULL, NULL);
INSERT INTO "public"."abc_activities" VALUES (6, 'Layanan Perpustakaan Pusat', 'Peminjaman buku dan jurnal', 'UNIV', NULL, NULL);
INSERT INTO "public"."abc_activities" VALUES (7, 'Layanan Teknologi Informasi (TI)', 'Server, Wi-Fi, SIAKAD', 'UNIV', NULL, NULL);
INSERT INTO "public"."abc_activities" VALUES (8, 'Administrasi Umum & Keuangan', 'Rektorat, HRD, Keuangan', 'UNIV', NULL, NULL);

-- ----------------------------
-- Table structure for abc_activity_allocations
-- ----------------------------
CREATE TABLE "public"."abc_activity_allocations" (
  "id" int8 NOT NULL DEFAULT nextval('abc_activity_allocations_id_seq'::regclass),
  "year_id" int4 NOT NULL,
  "activity_id" int4 NOT NULL,
  "driver_id" int4 NOT NULL,
  "study_program_id" int4 NOT NULL,
  "driver_value" numeric(10,2) NOT NULL DEFAULT 0,
  "cost_amount" numeric(15,2) NOT NULL DEFAULT 0,
  "created_at" timestamp(6),
  "updated_at" timestamp(6)
)
;

-- ----------------------------
-- Records of abc_activity_allocations
-- ----------------------------

-- ----------------------------
-- Table structure for abc_cost_categories
-- ----------------------------
CREATE TABLE "public"."abc_cost_categories" (
  "id" int4 NOT NULL DEFAULT nextval('abc_cost_categories_id_seq'::regclass),
  "code" varchar(20) COLLATE "pg_catalog"."default" NOT NULL,
  "name" varchar(100) COLLATE "pg_catalog"."default" NOT NULL,
  "cost_type" varchar(20) COLLATE "pg_catalog"."default" NOT NULL,
  "description" text COLLATE "pg_catalog"."default",
  "created_at" timestamp(6),
  "updated_at" timestamp(6)
)
;

-- ----------------------------
-- Records of abc_cost_categories
-- ----------------------------
INSERT INTO "public"."abc_cost_categories" VALUES (1, '5101', 'Gaji Dosen Tetap (PNS/Non-PNS)', 'Direct', 'Gaji pokok dan tunjangan dosen', NULL, NULL);
INSERT INTO "public"."abc_cost_categories" VALUES (2, '5102', 'Honor Dosen Tamu/LB', 'Direct', 'Honorarium per kehadiran', NULL, NULL);
INSERT INTO "public"."abc_cost_categories" VALUES (3, '5201', 'Bahan Habis Pakai (BHP) Lab', 'Direct', 'Bahan praktikum mahasiswa', NULL, NULL);
INSERT INTO "public"."abc_cost_categories" VALUES (4, '5202', 'Biaya Perjalanan Dinas Akademik', 'Direct', 'Seminar, Konferensi Dosen', NULL, NULL);
INSERT INTO "public"."abc_cost_categories" VALUES (5, '5301', 'Gaji Tenaga Kependidikan (Tendik)', 'Indirect', 'Gaji admin, satpam, staff', NULL, NULL);
INSERT INTO "public"."abc_cost_categories" VALUES (6, '5302', 'Biaya Listrik, Air, Internet', 'Indirect', 'Utilities kampus', NULL, NULL);
INSERT INTO "public"."abc_cost_categories" VALUES (7, '5303', 'Penyusutan Gedung & Aset', 'Indirect', 'Depresiasi tahunan', NULL, NULL);
INSERT INTO "public"."abc_cost_categories" VALUES (8, '5304', 'Biaya Pemeliharaan Gedung', 'Indirect', 'Service AC, Kebersihan', NULL, NULL);

-- ----------------------------
-- Table structure for abc_drivers
-- ----------------------------
CREATE TABLE "public"."abc_drivers" (
  "id" int4 NOT NULL DEFAULT nextval('abc_drivers_id_seq'::regclass),
  "name" varchar(150) COLLATE "pg_catalog"."default" NOT NULL,
  "unit" varchar(50) COLLATE "pg_catalog"."default" NOT NULL,
  "driver_type" varchar(20) COLLATE "pg_catalog"."default" NOT NULL DEFAULT 'ACTIVITY_DRIVER'::character varying,
  "description" text COLLATE "pg_catalog"."default",
  "created_at" timestamp(6),
  "updated_at" timestamp(6)
)
;

-- ----------------------------
-- Records of abc_drivers
-- ----------------------------
INSERT INTO "public"."abc_drivers" VALUES (1, 'Estimasi Persentase Waktu', '%', 'RESOURCE_DRIVER', 'Digunakan untuk membagi gaji staff berdasarkan waktu kerja', NULL, NULL);
INSERT INTO "public"."abc_drivers" VALUES (2, 'Luas Lantai Ruangan', 'M2', 'RESOURCE_DRIVER', 'Membagi biaya kebersihan/listrik ke tiap ruangan', NULL, NULL);
INSERT INTO "public"."abc_drivers" VALUES (3, 'KWh Listrik (Meteran)', 'KWh', 'RESOURCE_DRIVER', 'Jika ada meteran terpisah', NULL, NULL);
INSERT INTO "public"."abc_drivers" VALUES (4, 'Jumlah Mahasiswa Aktif', 'Orang', 'ACTIVITY_DRIVER', 'Pemicu biaya administrasi umum', NULL, NULL);
INSERT INTO "public"."abc_drivers" VALUES (5, 'Jumlah SKS Ditawarkan', 'SKS', 'ACTIVITY_DRIVER', 'Pemicu biaya perkuliahan', NULL, NULL);
INSERT INTO "public"."abc_drivers" VALUES (6, 'Jumlah Mahasiswa Bimbingan', 'Orang', 'ACTIVITY_DRIVER', 'Pemicu biaya bimbingan skripsi', NULL, NULL);
INSERT INTO "public"."abc_drivers" VALUES (7, 'Jumlah Kunjungan/Transaksi', 'Tiket', 'ACTIVITY_DRIVER', 'Pemicu biaya perpustakaan/admin', NULL, NULL);

-- ----------------------------
-- Table structure for abc_resource_assignments
-- ----------------------------
CREATE TABLE "public"."abc_resource_assignments" (
  "id" int8 NOT NULL DEFAULT nextval('abc_resource_assignments_id_seq'::regclass),
  "resource_id" int8 NOT NULL,
  "activity_id" int4 NOT NULL,
  "driver_id" int4,
  "percentage" numeric(5,2) NOT NULL DEFAULT 0,
  "allocated_amount" numeric(15,2) NOT NULL DEFAULT 0,
  "created_at" timestamp(6),
  "updated_at" timestamp(6)
)
;

-- ----------------------------
-- Records of abc_resource_assignments
-- ----------------------------
INSERT INTO "public"."abc_resource_assignments" VALUES (1, 1, 8, NULL, 1.01, 10100.00, '2025-12-03 12:04:33', '2025-12-03 12:04:33');

-- ----------------------------
-- Table structure for migrations
-- ----------------------------
CREATE TABLE "public"."migrations" (
  "id" int8 NOT NULL DEFAULT nextval('migrations_id_seq'::regclass),
  "version" varchar(255) COLLATE "pg_catalog"."default" NOT NULL,
  "class" varchar(255) COLLATE "pg_catalog"."default" NOT NULL,
  "group" varchar(255) COLLATE "pg_catalog"."default" NOT NULL,
  "namespace" varchar(255) COLLATE "pg_catalog"."default" NOT NULL,
  "time" int4 NOT NULL,
  "batch" int4 NOT NULL
)
;

-- ----------------------------
-- Records of migrations
-- ----------------------------
INSERT INTO "public"."migrations" VALUES (1, '20240101000001', 'App\Database\Migrations\Users', 'default', 'App', 1764736361, 1);
INSERT INTO "public"."migrations" VALUES (2, '20240101000002', 'App\Database\Migrations\Menus', 'default', 'App', 1764736361, 1);
INSERT INTO "public"."migrations" VALUES (3, '20240101000003', 'App\Database\Migrations\Faculties', 'default', 'App', 1764736361, 1);
INSERT INTO "public"."migrations" VALUES (4, '20240101000004', 'App\Database\Migrations\Departments', 'default', 'App', 1764736361, 1);
INSERT INTO "public"."migrations" VALUES (5, '20240101000005', 'App\Database\Migrations\StudyPrograms', 'default', 'App', 1764736361, 1);
INSERT INTO "public"."migrations" VALUES (6, '20240101000006', 'App\Database\Migrations\AddUserRelations', 'default', 'App', 1764736361, 1);
INSERT INTO "public"."migrations" VALUES (7, '20240101000006', 'App\Database\Migrations\CostCategories', 'default', 'App', 1764736361, 1);
INSERT INTO "public"."migrations" VALUES (8, '20240101000008', 'App\Database\Migrations\Activities', 'default', 'App', 1764736361, 1);
INSERT INTO "public"."migrations" VALUES (9, '20240101000009', 'App\Database\Migrations\Drivers', 'default', 'App', 1764736361, 1);
INSERT INTO "public"."migrations" VALUES (10, '20240101000010', 'App\Database\Migrations\AcademicYears', 'default', 'App', 1764736361, 1);
INSERT INTO "public"."migrations" VALUES (11, '20240101000011', 'App\Database\Migrations\TransResources', 'default', 'App', 1764745861, 2);
INSERT INTO "public"."migrations" VALUES (12, '20240101000012', 'App\Database\Migrations\TransDriverValues', 'default', 'App', 1764750617, 3);
INSERT INTO "public"."migrations" VALUES (13, '20240101000013', 'App\Database\Migrations\ResourceAssignments', 'default', 'App', 1764759150, 4);
INSERT INTO "public"."migrations" VALUES (14, '20240101000014', 'App\Database\Migrations\ActivityAllocations', 'default', 'App', 1764763954, 5);
INSERT INTO "public"."migrations" VALUES (15, '20240101000015', 'App\Database\Migrations\ReportUnitCosts', 'default', 'App', 1764776292, 6);

-- ----------------------------
-- Table structure for ms_academic_years
-- ----------------------------
CREATE TABLE "public"."ms_academic_years" (
  "id" int4 NOT NULL DEFAULT nextval('ms_academic_years_id_seq'::regclass),
  "year_name" varchar(20) COLLATE "pg_catalog"."default" NOT NULL,
  "is_active" bool NOT NULL DEFAULT false,
  "description" text COLLATE "pg_catalog"."default",
  "created_at" timestamp(6),
  "updated_at" timestamp(6)
)
;

-- ----------------------------
-- Records of ms_academic_years
-- ----------------------------
INSERT INTO "public"."ms_academic_years" VALUES (1, '2024/2025', 'f', '', '2025-12-03 04:34:07', '2025-12-03 06:33:39');
INSERT INTO "public"."ms_academic_years" VALUES (2, '2025/2026', 't', '', '2025-12-03 04:34:18', '2025-12-03 06:33:53');

-- ----------------------------
-- Table structure for ms_departments
-- ----------------------------
CREATE TABLE "public"."ms_departments" (
  "id" int4 NOT NULL DEFAULT nextval('ms_departments_id_seq'::regclass),
  "faculty_id" int4 NOT NULL,
  "code" varchar(20) COLLATE "pg_catalog"."default" NOT NULL,
  "name" varchar(100) COLLATE "pg_catalog"."default" NOT NULL,
  "description" text COLLATE "pg_catalog"."default",
  "created_at" timestamp(6),
  "updated_at" timestamp(6)
)
;

-- ----------------------------
-- Records of ms_departments
-- ----------------------------
INSERT INTO "public"."ms_departments" VALUES (1, 1, 'PJSD', 'Pendidikan Jasmani Sekolah Dasar', '', '2025-12-03 04:43:02', '2025-12-03 04:43:02');

-- ----------------------------
-- Table structure for ms_faculties
-- ----------------------------
CREATE TABLE "public"."ms_faculties" (
  "id" int4 NOT NULL DEFAULT nextval('ms_faculties_id_seq'::regclass),
  "code" varchar(10) COLLATE "pg_catalog"."default" NOT NULL,
  "name" varchar(100) COLLATE "pg_catalog"."default" NOT NULL,
  "description" text COLLATE "pg_catalog"."default",
  "created_at" timestamp(6),
  "updated_at" timestamp(6)
)
;

-- ----------------------------
-- Records of ms_faculties
-- ----------------------------
INSERT INTO "public"."ms_faculties" VALUES (1, 'FIKK', 'Fakultas Ilmu Keolahragaan dan Kesehatan', '', '2025-12-03 04:37:56', '2025-12-03 04:37:56');

-- ----------------------------
-- Table structure for ms_menus
-- ----------------------------
CREATE TABLE "public"."ms_menus" (
  "id" int4 NOT NULL DEFAULT nextval('ms_menus_id_seq'::regclass),
  "parent_id" int4 DEFAULT 0,
  "title" varchar(100) COLLATE "pg_catalog"."default" NOT NULL,
  "url" varchar(255) COLLATE "pg_catalog"."default" NOT NULL,
  "icon" varchar(50) COLLATE "pg_catalog"."default" NOT NULL,
  "sequence" int4 NOT NULL DEFAULT 0,
  "is_active" bool NOT NULL DEFAULT true,
  "allowed_roles" text COLLATE "pg_catalog"."default"
)
;

-- ----------------------------
-- Records of ms_menus
-- ----------------------------
INSERT INTO "public"."ms_menus" VALUES (1, 0, 'Dashboard', 'dashboard', 'fa fa-home', 1, 't', NULL);
INSERT INTO "public"."ms_menus" VALUES (2, 0, 'Master Data', '#', 'fa fa-database', 2, 't', 'superadmin,admin_univ');
INSERT INTO "public"."ms_menus" VALUES (3, 0, 'Manajemen Menu', 'menu-management', 'fa fa-bars', 99, 't', 'superadmin');
INSERT INTO "public"."ms_menus" VALUES (4, 2, 'Data Fakultas', 'master/fakultas', 'fa fa-university', 1, 't', NULL);
INSERT INTO "public"."ms_menus" VALUES (7, 2, 'Data Departemen', 'master/departemen', 'fa fa-circle', 2, 't', '');
INSERT INTO "public"."ms_menus" VALUES (5, 2, 'Data Prodi', 'master/prodi', 'fa fa-graduation-cap', 3, 't', '');
INSERT INTO "public"."ms_menus" VALUES (8, 2, 'Data Kategori', 'master/biaya', 'fa fa-circle', 5, 't', '');
INSERT INTO "public"."ms_menus" VALUES (9, 2, 'Data Aktivitas', 'master/aktivitas', 'fa fa-circle', 6, 't', '');
INSERT INTO "public"."ms_menus" VALUES (10, 2, 'Data Driver', 'master/driver', 'fa fa-circle', 7, 't', '');
INSERT INTO "public"."ms_menus" VALUES (11, 2, 'Tahun Anggaran', 'master/tahun', 'fa fa-circle', 8, 't', '');
INSERT INTO "public"."ms_menus" VALUES (6, 2, 'Data User', 'master/user', 'fa fa-users', 4, 't', '');
INSERT INTO "public"."ms_menus" VALUES (12, 0, 'Transaksi', '#', 'fa fa-circle', 3, 't', '');
INSERT INTO "public"."ms_menus" VALUES (13, 12, 'Input Biaya', 'transaksi/biaya', 'fa fa-circle', 1, 't', '');
INSERT INTO "public"."ms_menus" VALUES (14, 12, 'Volume Driver', 'transaksi/volume', 'fa fa-circle', 2, 't', '');
INSERT INTO "public"."ms_menus" VALUES (15, 12, 'Alokasi', 'transaksi/alokasi1', 'fa fa-circle', 3, 't', '');
INSERT INTO "public"."ms_menus" VALUES (16, 12, 'Alokasi 2', 'transaksi/alokasi2', 'fa fa-circle', 4, 't', '');
INSERT INTO "public"."ms_menus" VALUES (17, 0, 'Laporan', '#', 'fa fa-circle', 4, 't', '');
INSERT INTO "public"."ms_menus" VALUES (18, 17, 'Biaya Pendidikan', 'laporan/unitcost', 'fa fa-circle', 1, 't', '');

-- ----------------------------
-- Table structure for ms_study_programs
-- ----------------------------
CREATE TABLE "public"."ms_study_programs" (
  "id" int4 NOT NULL DEFAULT nextval('ms_study_programs_id_seq'::regclass),
  "department_id" int4 NOT NULL,
  "code" varchar(20) COLLATE "pg_catalog"."default" NOT NULL,
  "name" varchar(100) COLLATE "pg_catalog"."default" NOT NULL,
  "degree" varchar(20) COLLATE "pg_catalog"."default" NOT NULL,
  "description" text COLLATE "pg_catalog"."default",
  "created_at" timestamp(6),
  "updated_at" timestamp(6)
)
;

-- ----------------------------
-- Records of ms_study_programs
-- ----------------------------
INSERT INTO "public"."ms_study_programs" VALUES (1, 1, 'PJSD', 'Pendidikan Jasmani Sekolah Dasar', 'S1', '', '2025-12-03 04:45:49', '2025-12-03 04:45:49');

-- ----------------------------
-- Table structure for rpt_unit_costs
-- ----------------------------
CREATE TABLE "public"."rpt_unit_costs" (
  "id" int8 NOT NULL DEFAULT nextval('rpt_unit_costs_id_seq'::regclass),
  "year_id" int4 NOT NULL,
  "study_program_id" int4 NOT NULL,
  "total_cost" numeric(20,2) NOT NULL DEFAULT 0,
  "total_students" int4 NOT NULL DEFAULT 0,
  "unit_cost" numeric(20,2) NOT NULL DEFAULT 0,
  "calculated_at" timestamp(6)
)
;

-- ----------------------------
-- Records of rpt_unit_costs
-- ----------------------------

-- ----------------------------
-- Table structure for trans_driver_values
-- ----------------------------
CREATE TABLE "public"."trans_driver_values" (
  "id" int8 NOT NULL DEFAULT nextval('trans_driver_values_id_seq'::regclass),
  "year_id" int4 NOT NULL,
  "driver_id" int4 NOT NULL,
  "study_program_id" int4 NOT NULL,
  "value_amount" numeric(10,2) NOT NULL DEFAULT 0,
  "description" text COLLATE "pg_catalog"."default",
  "created_at" timestamp(6),
  "updated_at" timestamp(6)
)
;

-- ----------------------------
-- Records of trans_driver_values
-- ----------------------------

-- ----------------------------
-- Table structure for trans_resources
-- ----------------------------
CREATE TABLE "public"."trans_resources" (
  "id" int8 NOT NULL DEFAULT nextval('trans_resources_id_seq'::regclass),
  "year_id" int4 NOT NULL,
  "cost_category_id" int4 NOT NULL,
  "amount" numeric(15,2) NOT NULL DEFAULT 0,
  "level_scope" varchar(20) COLLATE "pg_catalog"."default" NOT NULL,
  "faculty_id" int4,
  "department_id" int4,
  "study_program_id" int4,
  "description" text COLLATE "pg_catalog"."default",
  "created_by" int4,
  "created_at" timestamp(6),
  "updated_at" timestamp(6)
)
;

-- ----------------------------
-- Records of trans_resources
-- ----------------------------
INSERT INTO "public"."trans_resources" VALUES (1, 2, 1, 1000000.00, 'PRODI', 1, 1, 1, '', 5, '2025-12-03 07:54:42', '2025-12-03 07:54:42');

-- ----------------------------
-- Alter sequences owned by
-- ----------------------------
ALTER SEQUENCE "public"."abc_activities_id_seq"
OWNED BY "public"."abc_activities"."id";

-- ----------------------------
-- Alter sequences owned by
-- ----------------------------
ALTER SEQUENCE "public"."abc_activity_allocations_id_seq"
OWNED BY "public"."abc_activity_allocations"."id";

-- ----------------------------
-- Alter sequences owned by
-- ----------------------------
ALTER SEQUENCE "public"."abc_cost_categories_id_seq"
OWNED BY "public"."abc_cost_categories"."id";

-- ----------------------------
-- Alter sequences owned by
-- ----------------------------
ALTER SEQUENCE "public"."abc_drivers_id_seq"
OWNED BY "public"."abc_drivers"."id";

-- ----------------------------
-- Alter sequences owned by
-- ----------------------------
ALTER SEQUENCE "public"."abc_resource_assignments_id_seq"
OWNED BY "public"."abc_resource_assignments"."id";

-- ----------------------------
-- Alter sequences owned by
-- ----------------------------
ALTER SEQUENCE "public"."migrations_id_seq"
OWNED BY "public"."migrations"."id";

-- ----------------------------
-- Alter sequences owned by
-- ----------------------------
ALTER SEQUENCE "public"."ms_academic_years_id_seq"
OWNED BY "public"."ms_academic_years"."id";

-- ----------------------------
-- Alter sequences owned by
-- ----------------------------
ALTER SEQUENCE "public"."ms_departments_id_seq"
OWNED BY "public"."ms_departments"."id";

-- ----------------------------
-- Alter sequences owned by
-- ----------------------------
ALTER SEQUENCE "public"."ms_faculties_id_seq"
OWNED BY "public"."ms_faculties"."id";

-- ----------------------------
-- Alter sequences owned by
-- ----------------------------
ALTER SEQUENCE "public"."ms_menus_id_seq"
OWNED BY "public"."ms_menus"."id";

-- ----------------------------
-- Alter sequences owned by
-- ----------------------------
ALTER SEQUENCE "public"."ms_study_programs_id_seq"
OWNED BY "public"."ms_study_programs"."id";

-- ----------------------------
-- Alter sequences owned by
-- ----------------------------
ALTER SEQUENCE "public"."rpt_unit_costs_id_seq"
OWNED BY "public"."rpt_unit_costs"."id";

-- ----------------------------
-- Alter sequences owned by
-- ----------------------------
ALTER SEQUENCE "public"."trans_driver_values_id_seq"
OWNED BY "public"."trans_driver_values"."id";

-- ----------------------------
-- Alter sequences owned by
-- ----------------------------
ALTER SEQUENCE "public"."trans_resources_id_seq"
OWNED BY "public"."trans_resources"."id";

-- ----------------------------
-- Alter sequences owned by
-- ----------------------------
ALTER SEQUENCE "public"."users_id_seq"
OWNED BY "public"."users"."id";

-- ----------------------------
-- Primary Key structure for table abc_activities
-- ----------------------------
ALTER TABLE "public"."abc_activities" ADD CONSTRAINT "pk_abc_activities" PRIMARY KEY ("id");

-- ----------------------------
-- Primary Key structure for table abc_activity_allocations
-- ----------------------------
ALTER TABLE "public"."abc_activity_allocations" ADD CONSTRAINT "pk_abc_activity_allocations" PRIMARY KEY ("id");

-- ----------------------------
-- Primary Key structure for table abc_cost_categories
-- ----------------------------
ALTER TABLE "public"."abc_cost_categories" ADD CONSTRAINT "pk_abc_cost_categories" PRIMARY KEY ("id");

-- ----------------------------
-- Primary Key structure for table abc_drivers
-- ----------------------------
ALTER TABLE "public"."abc_drivers" ADD CONSTRAINT "pk_abc_drivers" PRIMARY KEY ("id");

-- ----------------------------
-- Primary Key structure for table abc_resource_assignments
-- ----------------------------
ALTER TABLE "public"."abc_resource_assignments" ADD CONSTRAINT "pk_abc_resource_assignments" PRIMARY KEY ("id");

-- ----------------------------
-- Primary Key structure for table migrations
-- ----------------------------
ALTER TABLE "public"."migrations" ADD CONSTRAINT "pk_migrations" PRIMARY KEY ("id");

-- ----------------------------
-- Primary Key structure for table ms_academic_years
-- ----------------------------
ALTER TABLE "public"."ms_academic_years" ADD CONSTRAINT "pk_ms_academic_years" PRIMARY KEY ("id");

-- ----------------------------
-- Primary Key structure for table ms_departments
-- ----------------------------
ALTER TABLE "public"."ms_departments" ADD CONSTRAINT "pk_ms_departments" PRIMARY KEY ("id");

-- ----------------------------
-- Uniques structure for table ms_faculties
-- ----------------------------
ALTER TABLE "public"."ms_faculties" ADD CONSTRAINT "ms_faculties_code_key" UNIQUE ("code");

-- ----------------------------
-- Primary Key structure for table ms_faculties
-- ----------------------------
ALTER TABLE "public"."ms_faculties" ADD CONSTRAINT "pk_ms_faculties" PRIMARY KEY ("id");

-- ----------------------------
-- Primary Key structure for table ms_menus
-- ----------------------------
ALTER TABLE "public"."ms_menus" ADD CONSTRAINT "pk_ms_menus" PRIMARY KEY ("id");

-- ----------------------------
-- Primary Key structure for table ms_study_programs
-- ----------------------------
ALTER TABLE "public"."ms_study_programs" ADD CONSTRAINT "pk_ms_study_programs" PRIMARY KEY ("id");

-- ----------------------------
-- Primary Key structure for table rpt_unit_costs
-- ----------------------------
ALTER TABLE "public"."rpt_unit_costs" ADD CONSTRAINT "pk_rpt_unit_costs" PRIMARY KEY ("id");

-- ----------------------------
-- Primary Key structure for table trans_driver_values
-- ----------------------------
ALTER TABLE "public"."trans_driver_values" ADD CONSTRAINT "pk_trans_driver_values" PRIMARY KEY ("id");

-- ----------------------------
-- Primary Key structure for table trans_resources
-- ----------------------------
ALTER TABLE "public"."trans_resources" ADD CONSTRAINT "pk_trans_resources" PRIMARY KEY ("id");

-- ----------------------------
-- Foreign Keys structure for table abc_activity_allocations
-- ----------------------------
ALTER TABLE "public"."abc_activity_allocations" ADD CONSTRAINT "abc_activity_allocations_activity_id_foreign" FOREIGN KEY ("activity_id") REFERENCES "public"."abc_activities" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "public"."abc_activity_allocations" ADD CONSTRAINT "abc_activity_allocations_driver_id_foreign" FOREIGN KEY ("driver_id") REFERENCES "public"."abc_drivers" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "public"."abc_activity_allocations" ADD CONSTRAINT "abc_activity_allocations_study_program_id_foreign" FOREIGN KEY ("study_program_id") REFERENCES "public"."ms_study_programs" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "public"."abc_activity_allocations" ADD CONSTRAINT "abc_activity_allocations_year_id_foreign" FOREIGN KEY ("year_id") REFERENCES "public"."ms_academic_years" ("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- ----------------------------
-- Foreign Keys structure for table abc_resource_assignments
-- ----------------------------
ALTER TABLE "public"."abc_resource_assignments" ADD CONSTRAINT "abc_resource_assignments_activity_id_foreign" FOREIGN KEY ("activity_id") REFERENCES "public"."abc_activities" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "public"."abc_resource_assignments" ADD CONSTRAINT "abc_resource_assignments_driver_id_foreign" FOREIGN KEY ("driver_id") REFERENCES "public"."abc_drivers" ("id") ON DELETE CASCADE ON UPDATE SET NULL;
ALTER TABLE "public"."abc_resource_assignments" ADD CONSTRAINT "abc_resource_assignments_resource_id_foreign" FOREIGN KEY ("resource_id") REFERENCES "public"."trans_resources" ("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- ----------------------------
-- Foreign Keys structure for table ms_departments
-- ----------------------------
ALTER TABLE "public"."ms_departments" ADD CONSTRAINT "ms_departments_faculty_id_foreign" FOREIGN KEY ("faculty_id") REFERENCES "public"."ms_faculties" ("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- ----------------------------
-- Foreign Keys structure for table ms_study_programs
-- ----------------------------
ALTER TABLE "public"."ms_study_programs" ADD CONSTRAINT "ms_study_programs_department_id_foreign" FOREIGN KEY ("department_id") REFERENCES "public"."ms_departments" ("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- ----------------------------
-- Foreign Keys structure for table rpt_unit_costs
-- ----------------------------
ALTER TABLE "public"."rpt_unit_costs" ADD CONSTRAINT "rpt_unit_costs_study_program_id_foreign" FOREIGN KEY ("study_program_id") REFERENCES "public"."ms_study_programs" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "public"."rpt_unit_costs" ADD CONSTRAINT "rpt_unit_costs_year_id_foreign" FOREIGN KEY ("year_id") REFERENCES "public"."ms_academic_years" ("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- ----------------------------
-- Foreign Keys structure for table trans_driver_values
-- ----------------------------
ALTER TABLE "public"."trans_driver_values" ADD CONSTRAINT "trans_driver_values_driver_id_foreign" FOREIGN KEY ("driver_id") REFERENCES "public"."abc_drivers" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "public"."trans_driver_values" ADD CONSTRAINT "trans_driver_values_study_program_id_foreign" FOREIGN KEY ("study_program_id") REFERENCES "public"."ms_study_programs" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "public"."trans_driver_values" ADD CONSTRAINT "trans_driver_values_year_id_foreign" FOREIGN KEY ("year_id") REFERENCES "public"."ms_academic_years" ("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- ----------------------------
-- Foreign Keys structure for table trans_resources
-- ----------------------------
ALTER TABLE "public"."trans_resources" ADD CONSTRAINT "trans_resources_cost_category_id_foreign" FOREIGN KEY ("cost_category_id") REFERENCES "public"."abc_cost_categories" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "public"."trans_resources" ADD CONSTRAINT "trans_resources_department_id_foreign" FOREIGN KEY ("department_id") REFERENCES "public"."ms_departments" ("id") ON DELETE CASCADE ON UPDATE SET NULL;
ALTER TABLE "public"."trans_resources" ADD CONSTRAINT "trans_resources_faculty_id_foreign" FOREIGN KEY ("faculty_id") REFERENCES "public"."ms_faculties" ("id") ON DELETE CASCADE ON UPDATE SET NULL;
ALTER TABLE "public"."trans_resources" ADD CONSTRAINT "trans_resources_study_program_id_foreign" FOREIGN KEY ("study_program_id") REFERENCES "public"."ms_study_programs" ("id") ON DELETE CASCADE ON UPDATE SET NULL;
ALTER TABLE "public"."trans_resources" ADD CONSTRAINT "trans_resources_year_id_foreign" FOREIGN KEY ("year_id") REFERENCES "public"."ms_academic_years" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
