export const BTable = pgTable('b', {
    id: serial('id').primaryKey(),

    // Foreign Key
    year: smallint('year').notNull(),
    week: smallint('week').notNull(),
    ownerId: text('ownerId').notNull(),

    created_At: timestamp('createdAt').notNull().default(sql`CURRENT_TIMESTAMP`),
    created_By: text('createdBy').notNull().references(() => UserTable.id),
    modified_At: timestamp('modifiedAt').notNull(),
    modified_By: text('modifiedBy').notNull().references(() => UserTable.id)
}, (table) => {
    return {
        unq: unique().on(table.year, table.week, table.ownerId, table.project_Id)
    }
});

export const BRelations = relations(BTable, ({ one }) => ({
    As: one(ATable, {
        fields: [BTable.year, BTable.week, BTable.ownerId],
        references: [ATable.year, ATable.week, ATable.ownerId]
    }),
}));

export const CTable = pgTable('C', {
    id: serial('id').primaryKey(),
    who_Id: text('whoId').references(() => UserTable.id),
    manager_Id: text('managerId').references(() => UserTable.id),
    status: StatusEnum('status').notNull(),

    // Foreign Key
    year: smallint('year'),
    week: smallint('week'),
    ownerId: text('ownerId'),

    created_At: timestamp('createdAt').notNull().default(sql`CURRENT_TIMESTAMP`),
    created_By: text('createdBy').notNull().references(() => UserTable.id),
    modified_At: timestamp('modifiedAt').notNull(),
    modified_By: text('modifiedBy').notNull().references(() => UserTable.id)
});

export const CRelations = relations(CTable, ({ one }) => ({
    who: one(UserTable, {
        fields: [CTable.who_Id],
        references: [UserTable.id]
    }),
    manager: one(UserTable, {
        fields: [CTable.manager_Id],
        references: [UserTable.id]
    }),
    As: one(ATable, {
        fields: [CTable.year, CTable.week, CTable.ownerId],
        references: [ATable.year, ATable.week, ATable.ownerId]
    })
}))

export const BTable = pgTable('b', {
    id: serial('id').primaryKey(),

    // Foreign Key
    year: smallint('year').notNull(),
    week: smallint('week').notNull(),
    ownerId: text('ownerId').notNull(),

    created_At: timestamp('createdAt').notNull().default(sql`CURRENT_TIMESTAMP`),
    created_By: text('createdBy').notNull().references(() => UserTable.id),
    modified_At: timestamp('modifiedAt').notNull(),
    modified_By: text('modifiedBy').notNull().references(() => UserTable.id)
}, (table) => {
    return {
        unq: unique().on(table.year, table.week, table.ownerId, table.project_Id)
    }
});

export const BRelations = relations(BTable, ({ one }) => ({
    As: one(ATable, {
        fields: [BTable.year, BTable.week, BTable.ownerId],
        references: [ATable.year, ATable.week, ATable.ownerId]
    }),
}));

export const CTable = pgTable('C', {
    id: serial('id').primaryKey(),
    who_Id: text('whoId').references(() => UserTable.id),
    manager_Id: text('managerId').references(() => UserTable.id),
    status: StatusEnum('status').notNull(),

    // Foreign Key
    year: smallint('year'),
    week: smallint('week'),
    ownerId: text('ownerId'),

    created_At: timestamp('createdAt').notNull().default(sql`CURRENT_TIMESTAMP`),
    created_By: text('createdBy').notNull().references(() => UserTable.id),
    modified_At: timestamp('modifiedAt').notNull(),
    modified_By: text('modifiedBy').notNull().references(() => UserTable.id)
});

export const CRelations = relations(CTable, ({ one }) => ({
    who: one(UserTable, {
        fields: [CTable.who_Id],
        references: [UserTable.id]
    }),
    manager: one(UserTable, {
        fields: [CTable.manager_Id],
        references: [UserTable.id]
    }),
    As: one(ATable, {
        fields: [CTable.year, CTable.week, CTable.ownerId],
        references: [ATable.year, ATable.week, ATable.ownerId]
    })
}))

export const ATable = pgTable('a', {
    year: smallint('year').notNull(),
    week: smallint('week').notNull(),
    ownerId: text('ownerId').notNull().references(() => UserTable.id),
    status: StatusEnum('status').notNull(),

    created_At: timestamp('createdAt').notNull().default(sql`CURRENT_TIMESTAMP`),
    created_By: text('createdBy').notNull().references(() => UserTable.id),
    modified_At: timestamp('modifiedAt').notNull(),
    modified_By: text('modifiedBy').notNull().references(() => UserTable.id)
}, (table) => {
    return {
        id: primaryKey(table.year, table.week, table.ownerId),
        getAIndex: index("get_a_idx").on(table.year, table.week, table.ownerId),
    }
});

export const ARelations = relations(ATable, ({ one, many }) => ({
    owner: one(UserTable, {
        fields: [ATable.ownerId],
        references: [UserTable.id]
    }),
    bs: many(BTable),
    cs: many(CTable)
}));

export const ATable = pgTable('a', {
    year: smallint('year').notNull(),
    week: smallint('week').notNull(),
    ownerId: text('ownerId').notNull().references(() => UserTable.id),
    status: StatusEnum('status').notNull(),

    created_At: timestamp('createdAt').notNull().default(sql`CURRENT_TIMESTAMP`),
    created_By: text('createdBy').notNull().references(() => UserTable.id),
    modified_At: timestamp('modifiedAt').notNull(),
    modified_By: text('modifiedBy').notNull().references(() => UserTable.id)
}, (table) => {
    return {
        id: primaryKey(table.year, table.week, table.ownerId),
        getAIndex: index("get_a_idx").on(table.year, table.week, table.ownerId),
    }
});

export const ARelations = relations(ATable, ({ one, many }) => ({
    owner: one(UserTable, {
        fields: [ATable.ownerId],
        references: [UserTable.id]
    }),
    bs: many(BTable),
    cs: many(CTable)
}));