import { relations } from "drizzle-orm"
import { int, sqliteTable, text, primaryKey } from "drizzle-orm/sqlite-core"

export const usersTable = sqliteTable("users", {
    id: int("id").primaryKey({ autoIncrement: true }),
    name: text("name").notNull(),
    password: text("hash_password").notNull(),
    email: text("email").notNull().unique()
})

export const rolesTable = sqliteTable("roles", {
    id: int("id").primaryKey({ autoIncrement: true }),
    name: text("name").notNull()
})

export const permissionsTable = sqliteTable("permissions", {
    id: int("id").primaryKey({ autoIncrement: true }),
    name: text("name").notNull()
})

export const usersRolesTable = sqliteTable('users_roles', {
    userId: int("user_id").notNull().references(() => usersTable.id),
    roleId: int("role_id").notNull().references(() => rolesTable.id)
}, (t) => [ primaryKey({ columns: [ t.userId, t.roleId ] }) ])

export const rolesPermissionsTable = sqliteTable('roles_permissions', {
    roleId: int("role_id").notNull().references(() => rolesTable.id),
    permissionId: int("permission_id").notNull().references(() => permissionsTable.id)
}, (t) => [primaryKey({columns: [t.roleId, t.permissionId]})])

export const usersRelations = relations(usersTable, ({ many }) => ({
    usersToRoles: many(usersRolesTable),
}))

export const rolesRelations = relations(rolesTable, ({ many }) => ({
    usersToRoles: many(usersRolesTable),
    rolesToPermissions: many(rolesPermissionsTable)
}))

export const permissionsRelations = relations(permissionsTable, ({ many }) => ({
    rolesToPermissions: many(rolesPermissionsTable)
}))

export const usersToRolesRelations = relations(usersRolesTable, ({ one }) => ({
    user: one(usersTable, {
        fields: [usersRolesTable.userId],
        references: [usersTable.id]
    }),
    role: one(rolesTable, {
        fields: [usersRolesTable.roleId],
        references: [rolesTable.id]
    })
}))

export const rolesToPermissionsRelations = relations(rolesPermissionsTable, ({one}) => ({
    role: one(rolesTable, {
        fields: [rolesPermissionsTable.roleId],
        references: [rolesTable.id]
    }),
    permission: one(permissionsTable, {
        fields: [rolesPermissionsTable.permissionId],
        references: [permissionsTable.id]
    })
}))