import { relations } from 'drizzle-orm';
import { int, text, sqliteTable, primaryKey, blob } from 'drizzle-orm/sqlite-core';
import { ABILITIES, MAIN_STATS } from '~/shared/character';

export const usersTable = sqliteTable("users", {
    id: int().primaryKey({ autoIncrement: true }),
    username: text().notNull().unique(),
    email: text().notNull().unique(),
    hash: text().notNull().unique(),
    state: int().notNull().default(0),
});

export const usersDataTable = sqliteTable("users_data", {
    id: int().primaryKey().references(() => usersTable.id, { onDelete: 'cascade', onUpdate: 'cascade' }),
    signin: int({ mode: 'timestamp' }).notNull().$defaultFn(() => new Date()),
    lastTimestamp: int({ mode: 'timestamp' }).notNull().$defaultFn(() => new Date()),
    logCount: int().notNull().default(0),
});

export const userSessionsTable = sqliteTable("user_sessions", {
    id: int().notNull(),
    user_id: int().notNull().references(() => usersTable.id, { onDelete: 'cascade', onUpdate: 'cascade' }),
    timestamp: int({ mode: 'timestamp' }).notNull().$defaultFn(() => new Date()),
}, (table) => [primaryKey({ columns: [table.id, table.user_id] })]);

export const userPermissionsTable = sqliteTable("user_permissions", {
    id: int().notNull().references(() => usersTable.id, { onDelete: 'cascade', onUpdate: 'cascade' }),
    permission: text().notNull(),
}, (table) => [primaryKey({ columns: [table.id, table.permission] })]);

export const explorerContentTable = sqliteTable("explorer_content", {
    path: text().primaryKey(),
    owner: int().notNull().references(() => usersTable.id, { onDelete: 'cascade', onUpdate: 'cascade' }),
    title: text().notNull(),
    type: text({ enum: ['file', 'folder', 'markdown', 'canvas', 'map'] }).notNull(),
    content: blob({ mode: 'buffer' }),
    navigable: int({ mode: 'boolean' }).notNull().default(true),
    private: int({ mode: 'boolean' }).notNull().default(false),
    order: int().notNull(),
    visit: int().notNull().default(0),
    timestamp: int({ mode: 'timestamp' }).notNull().$defaultFn(() => new Date()),
});

export const emailValidationTable = sqliteTable("email_validation", {
    id: text().primaryKey(),
    timestamp: int({ mode: 'timestamp' }).notNull(),
})

export const characterTable = sqliteTable("character", {
    id: int().primaryKey({ autoIncrement: true }),
    name: text().notNull(),
    owner: int().notNull().references(() => usersTable.id, { onDelete: 'cascade', onUpdate: 'cascade' }),
    people: int().notNull(),
    level: int().notNull().default(1),
    aspect: int(),
    notes: text(),
    health: int().notNull().default(0),
    mana: int().notNull().default(0),
    
    visibility: text({ enum: ['private', 'public'] }).notNull().default('private'),
    thumbnail: blob(),
});

export const characterTrainingTable = sqliteTable("character_training", {
    character: int().notNull().references(() => characterTable.id, { onDelete: 'cascade', onUpdate: 'cascade' }),
    stat: text({ enum: MAIN_STATS }).notNull(),
    level: int().notNull(),
    choice: int().notNull(),
}, (table) => [primaryKey({ columns: [table.character, table.stat, table.level] })]);

export const characterLevelingTable = sqliteTable("character_leveling", {
    character: int().notNull().references(() => characterTable.id, { onDelete: 'cascade', onUpdate: 'cascade' }),
    level: int().notNull(),
    choice: int().notNull(),
}, (table) => [primaryKey({ columns: [table.character, table.level] })]);

export const characterAbilitiesTable = sqliteTable("character_abilities", {
    character: int().notNull().references(() => characterTable.id, { onDelete: 'cascade', onUpdate: 'cascade' }),
    ability: text({ enum: ABILITIES }).notNull(),
    value: int().notNull().default(0),
    max: int().notNull().default(0),
}, (table) => [primaryKey({ columns: [table.character, table.ability] })]);

export const characterModifiersTable = sqliteTable("character_modifiers", {
    character: int().notNull().references(() => characterTable.id, { onDelete: 'cascade', onUpdate: 'cascade' }),
    modifier: text({ enum: MAIN_STATS }).notNull(),
    value: int().notNull().default(0),
}, (table) => [primaryKey({ columns: [table.character, table.modifier] })]);

export const characterSpellsTable = sqliteTable("character_spell", {
    character: int().notNull().references(() => characterTable.id, { onDelete: 'cascade', onUpdate: 'cascade' }),
    value: text().notNull(),
}, (table) => [primaryKey({ columns: [table.character, table.value] })]);

export const usersRelation = relations(usersTable, ({ one, many }) => ({
    data: one(usersDataTable, { fields: [usersTable.id], references: [usersDataTable.id], }),
    session: many(userSessionsTable),
    permission: many(userPermissionsTable),
    content: many(explorerContentTable),
}));
export const usersDataRelation = relations(usersDataTable, ({ one }) => ({
    users: one(usersTable, { fields: [usersDataTable.id], references: [usersTable.id], }),
}));
export const userSessionsRelation = relations(userSessionsTable, ({ one }) => ({
    users: one(usersTable, { fields: [userSessionsTable.user_id], references: [usersTable.id], }),
}));
export const userPermissionsRelation = relations(userPermissionsTable, ({ one }) => ({
    users: one(usersTable, { fields: [userPermissionsTable.id], references: [usersTable.id], }),
}));
export const explorerContentRelation = relations(explorerContentTable, ({ one }) => ({
    users: one(usersTable, { fields: [explorerContentTable.owner], references: [usersTable.id], }),
}));
export const characterRelation = relations(characterTable, ({ one, many }) => ({
    user: one(usersTable, { fields: [characterTable.owner], references: [usersTable.id], }),
    training: many(characterTrainingTable),
    levels: many(characterLevelingTable),
    abilities: many(characterAbilitiesTable),
    modifiers: many(characterModifiersTable),
    spells: many(characterSpellsTable)
}));

export const characterTrainingRelation = relations(characterTrainingTable, ({ one }) => ({
    character: one(characterTable, { fields: [characterTrainingTable.character], references: [characterTable.id] })
}));
export const characterLevelingRelation = relations(characterLevelingTable, ({ one }) => ({
    character: one(characterTable, { fields: [characterLevelingTable.character], references: [characterTable.id] })
}));
export const characterAbilitiesRelation = relations(characterAbilitiesTable, ({ one }) => ({
    character: one(characterTable, { fields: [characterAbilitiesTable.character], references: [characterTable.id] })
}));
export const characterModifierRelation = relations(characterModifiersTable, ({ one }) => ({
    character: one(characterTable, { fields: [characterModifiersTable.character], references: [characterTable.id] })
}));
export const characterSpellsRelation = relations(characterSpellsTable, ({ one }) => ({
    character: one(characterTable, { fields: [characterSpellsTable.character], references: [characterTable.id] })
}));