xiaomai
4e40bfd804
Introduce pic_sort_order to persist custom user ordering Replace data table with a custom draggable grid layout Add API endpoint to handle bulk order updates
719 lines
17 KiB
TypeScript
719 lines
17 KiB
TypeScript
import { randomUUID } from 'node:crypto'
|
|
|
|
import { DEFAULT_USER_PASSWORD } from '~~/shared/auth'
|
|
|
|
import { randomToken } from './base64url'
|
|
import { hashPassword } from './password'
|
|
import { getSqlClient } from './postgres'
|
|
|
|
let databaseReadyPromise: Promise<void> | null = null
|
|
|
|
export async function ensureDatabaseReady() {
|
|
if (!databaseReadyPromise) {
|
|
databaseReadyPromise = initializeDatabase()
|
|
}
|
|
|
|
return databaseReadyPromise
|
|
}
|
|
|
|
async function initializeDatabase() {
|
|
const sql = getSqlClient()
|
|
|
|
await sql`
|
|
create table if not exists users (
|
|
id text primary key,
|
|
username text not null unique,
|
|
full_name text not null,
|
|
phone_number text,
|
|
role text not null check (role in ('super_admin', 'staff')),
|
|
password_hash text not null,
|
|
must_change_password boolean not null default true,
|
|
is_active boolean not null default true,
|
|
created_by text references users(id) on delete set null,
|
|
created_at timestamptz not null default now(),
|
|
updated_at timestamptz not null default now(),
|
|
last_login_at timestamptz
|
|
)
|
|
`
|
|
|
|
await sql`
|
|
alter table users
|
|
add column if not exists phone_number text
|
|
`
|
|
|
|
await sql`
|
|
alter table users
|
|
add column if not exists pic_sort_order integer not null default 0
|
|
`
|
|
|
|
await sql`
|
|
update users
|
|
set pic_sort_order = seed.sort_order
|
|
from (
|
|
select
|
|
id,
|
|
row_number() over (
|
|
order by
|
|
case when role = 'super_admin' then 0 else 1 end,
|
|
created_at asc,
|
|
full_name asc
|
|
) as sort_order
|
|
from users
|
|
) as seed
|
|
where users.id = seed.id
|
|
and users.pic_sort_order = 0
|
|
`
|
|
|
|
await sql`
|
|
create table if not exists user_passkeys (
|
|
id text primary key,
|
|
user_id text not null references users(id) on delete cascade,
|
|
credential_id text not null unique,
|
|
public_key text not null,
|
|
counter bigint not null default 0,
|
|
device_type text not null check (device_type in ('singleDevice', 'multiDevice')),
|
|
backed_up boolean not null default false,
|
|
transports jsonb not null default '[]'::jsonb,
|
|
label text not null,
|
|
created_at timestamptz not null default now(),
|
|
last_used_at timestamptz
|
|
)
|
|
`
|
|
|
|
await sql`
|
|
create index if not exists user_passkeys_user_id_idx
|
|
on user_passkeys (user_id)
|
|
`
|
|
|
|
await sql`
|
|
create table if not exists dinner_events (
|
|
id text primary key,
|
|
title text not null,
|
|
date_label text not null,
|
|
time_label text not null,
|
|
venue text not null,
|
|
is_active boolean not null default false,
|
|
sort_order integer not null default 0,
|
|
created_at timestamptz not null default now(),
|
|
updated_at timestamptz not null default now()
|
|
)
|
|
`
|
|
|
|
await sql`
|
|
create unique index if not exists dinner_events_single_active_idx
|
|
on dinner_events (is_active)
|
|
where is_active = true
|
|
`
|
|
|
|
await sql`
|
|
insert into dinner_events (
|
|
id,
|
|
title,
|
|
date_label,
|
|
time_label,
|
|
venue,
|
|
is_active,
|
|
sort_order
|
|
)
|
|
values (
|
|
'dap-johor-60',
|
|
'DAP JOHOR 60th Anniversary Celebration',
|
|
'Saturday, 30 May 2026',
|
|
'6:30 PM',
|
|
'Yong Peng''s Chee Ann Kor',
|
|
true,
|
|
1
|
|
)
|
|
on conflict (id) do nothing
|
|
`
|
|
|
|
await sql`
|
|
update dinner_events
|
|
set
|
|
is_active = true,
|
|
updated_at = now()
|
|
where id = 'dap-johor-60'
|
|
and not exists (
|
|
select 1
|
|
from dinner_events
|
|
where is_active = true
|
|
)
|
|
`
|
|
|
|
await sql`
|
|
create table if not exists booking_modes (
|
|
id text primary key,
|
|
event_id text not null references dinner_events(id) on delete cascade,
|
|
code text not null,
|
|
label text not null,
|
|
quantity_label text not null,
|
|
seats_per_unit integer not null check (seats_per_unit >= 1),
|
|
is_active boolean not null default true,
|
|
sort_order integer not null default 0,
|
|
created_at timestamptz not null default now(),
|
|
updated_at timestamptz not null default now(),
|
|
unique (event_id, code)
|
|
)
|
|
`
|
|
|
|
await sql`
|
|
insert into booking_modes (
|
|
id,
|
|
event_id,
|
|
code,
|
|
label,
|
|
quantity_label,
|
|
seats_per_unit,
|
|
is_active,
|
|
sort_order
|
|
)
|
|
values
|
|
(
|
|
'dap-johor-60-table',
|
|
'dap-johor-60',
|
|
'table',
|
|
'Table (10 seats)',
|
|
'Number of Tables',
|
|
10,
|
|
true,
|
|
1
|
|
),
|
|
(
|
|
'dap-johor-60-seat',
|
|
'dap-johor-60',
|
|
'seat',
|
|
'Seat',
|
|
'Number of Seats',
|
|
1,
|
|
true,
|
|
2
|
|
)
|
|
on conflict (event_id, code) do nothing
|
|
`
|
|
|
|
await sql`
|
|
create table if not exists ticket_types (
|
|
id text primary key,
|
|
event_id text not null references dinner_events(id) on delete cascade,
|
|
code text not null,
|
|
label text not null,
|
|
description text not null,
|
|
price integer not null check (price >= 0),
|
|
is_active boolean not null default true,
|
|
sort_order integer not null default 0,
|
|
created_at timestamptz not null default now(),
|
|
updated_at timestamptz not null default now(),
|
|
unique (event_id, code)
|
|
)
|
|
`
|
|
|
|
await sql`
|
|
insert into ticket_types (
|
|
id,
|
|
event_id,
|
|
code,
|
|
label,
|
|
description,
|
|
price,
|
|
is_active,
|
|
sort_order
|
|
)
|
|
values
|
|
(
|
|
'dap-johor-60-vip',
|
|
'dap-johor-60',
|
|
'vip',
|
|
'VIP',
|
|
'RM150 / seat',
|
|
150,
|
|
true,
|
|
1
|
|
),
|
|
(
|
|
'dap-johor-60-supporter',
|
|
'dap-johor-60',
|
|
'supporter',
|
|
'Supporter',
|
|
'RM60 / seat',
|
|
60,
|
|
true,
|
|
2
|
|
)
|
|
on conflict (event_id, code) do nothing
|
|
`
|
|
|
|
await sql`
|
|
create table if not exists booking_statuses (
|
|
code text primary key,
|
|
label text not null,
|
|
sort_order integer not null default 0,
|
|
created_at timestamptz not null default now(),
|
|
updated_at timestamptz not null default now()
|
|
)
|
|
`
|
|
|
|
await sql`
|
|
insert into booking_statuses (
|
|
code,
|
|
label,
|
|
sort_order
|
|
)
|
|
values
|
|
('pending', 'Pending PIC confirmation', 1),
|
|
('confirmed', 'Confirmed', 2)
|
|
on conflict (code) do nothing
|
|
`
|
|
|
|
await sql`
|
|
create table if not exists bookings (
|
|
id text primary key,
|
|
confirmation_token text not null unique,
|
|
receipt_token text not null unique,
|
|
event_id text references dinner_events(id) on delete restrict,
|
|
customer_name text not null,
|
|
customer_phone text not null,
|
|
booking_mode_id text references booking_modes(id) on delete restrict,
|
|
booking_mode text not null,
|
|
quantity integer not null check (quantity >= 1),
|
|
seat_count integer not null check (seat_count >= 1),
|
|
ticket_type_id text references ticket_types(id) on delete restrict,
|
|
ticket_type text not null,
|
|
unit_price integer not null check (unit_price >= 0),
|
|
total_price integer not null check (total_price >= 0),
|
|
person_in_charge_id text not null references users(id) on delete restrict,
|
|
person_in_charge_name text not null,
|
|
person_in_charge_phone_number text not null,
|
|
remark text,
|
|
status text not null default 'pending',
|
|
confirmed_at timestamptz,
|
|
created_at timestamptz not null default now(),
|
|
updated_at timestamptz not null default now()
|
|
)
|
|
`
|
|
|
|
await sql`
|
|
alter table bookings
|
|
add column if not exists receipt_token text
|
|
`
|
|
|
|
await sql`
|
|
alter table bookings
|
|
add column if not exists event_id text
|
|
`
|
|
|
|
await sql`
|
|
alter table bookings
|
|
add column if not exists booking_mode_id text
|
|
`
|
|
|
|
await sql`
|
|
alter table bookings
|
|
add column if not exists ticket_type_id text
|
|
`
|
|
|
|
await sql`
|
|
alter table bookings
|
|
add column if not exists remark text
|
|
`
|
|
|
|
await sql`
|
|
create unique index if not exists bookings_receipt_token_idx
|
|
on bookings (receipt_token)
|
|
`
|
|
|
|
await sql`
|
|
create index if not exists bookings_event_id_idx
|
|
on bookings (event_id)
|
|
`
|
|
|
|
await sql`
|
|
create index if not exists bookings_booking_mode_id_idx
|
|
on bookings (booking_mode_id)
|
|
`
|
|
|
|
await sql`
|
|
create index if not exists bookings_ticket_type_id_idx
|
|
on bookings (ticket_type_id)
|
|
`
|
|
|
|
await sql`
|
|
create table if not exists booking_seats (
|
|
id text primary key,
|
|
booking_id text not null references bookings(id) on delete cascade,
|
|
seat_number integer not null check (seat_number >= 1),
|
|
seat_token text not null unique,
|
|
recipient_name text,
|
|
recipient_phone text,
|
|
shared_at timestamptz,
|
|
created_at timestamptz not null default now(),
|
|
updated_at timestamptz not null default now(),
|
|
unique (booking_id, seat_number)
|
|
)
|
|
`
|
|
|
|
await sql`
|
|
create index if not exists booking_seats_booking_id_idx
|
|
on booking_seats (booking_id)
|
|
`
|
|
|
|
await sql`
|
|
create table if not exists booking_settings (
|
|
id text primary key,
|
|
event_id text references dinner_events(id) on delete cascade,
|
|
total_tables integer,
|
|
total_seats integer,
|
|
updated_at timestamptz not null default now()
|
|
)
|
|
`
|
|
|
|
await sql`
|
|
alter table booking_settings
|
|
add column if not exists event_id text
|
|
`
|
|
|
|
await sql`
|
|
alter table booking_settings
|
|
add column if not exists total_seats integer
|
|
`
|
|
|
|
await sql`
|
|
insert into booking_settings (id, event_id)
|
|
values ('default', 'dap-johor-60')
|
|
on conflict (id) do update
|
|
set event_id = coalesce(booking_settings.event_id, excluded.event_id)
|
|
`
|
|
|
|
const bookingsMissingReceiptTokens = await sql<{ id: string }[]>`
|
|
select id
|
|
from bookings
|
|
where receipt_token is null or receipt_token = ''
|
|
`
|
|
|
|
for (const booking of bookingsMissingReceiptTokens) {
|
|
await sql`
|
|
update bookings
|
|
set
|
|
receipt_token = ${randomToken(24)},
|
|
updated_at = now()
|
|
where id = ${booking.id}
|
|
`
|
|
}
|
|
|
|
await sql`
|
|
alter table bookings
|
|
drop constraint if exists bookings_booking_mode_check
|
|
`
|
|
|
|
await sql`
|
|
update bookings
|
|
set
|
|
booking_mode = 'seat',
|
|
updated_at = now()
|
|
where booking_mode = 'pax'
|
|
`
|
|
|
|
await sql`
|
|
alter table bookings
|
|
drop constraint if exists bookings_booking_mode_check
|
|
`
|
|
|
|
await sql`
|
|
alter table bookings
|
|
drop constraint if exists bookings_ticket_type_check
|
|
`
|
|
|
|
await sql`
|
|
alter table bookings
|
|
drop constraint if exists bookings_status_check
|
|
`
|
|
|
|
const [activeEvent] = await sql<{ id: string }[]>`
|
|
select id
|
|
from dinner_events
|
|
where is_active = true
|
|
order by sort_order asc, created_at asc
|
|
limit 1
|
|
`
|
|
|
|
if (activeEvent) {
|
|
await sql`
|
|
update booking_settings
|
|
set
|
|
event_id = ${activeEvent.id},
|
|
updated_at = now()
|
|
where event_id is null
|
|
`
|
|
|
|
await sql`
|
|
update bookings
|
|
set
|
|
event_id = ${activeEvent.id},
|
|
updated_at = now()
|
|
where event_id is null
|
|
`
|
|
|
|
await sql`
|
|
update bookings
|
|
set
|
|
booking_mode_id = booking_modes.id,
|
|
updated_at = now()
|
|
from booking_modes
|
|
where bookings.booking_mode_id is null
|
|
and booking_modes.event_id = bookings.event_id
|
|
and booking_modes.code = bookings.booking_mode
|
|
`
|
|
|
|
await sql`
|
|
update bookings
|
|
set
|
|
ticket_type_id = ticket_types.id,
|
|
updated_at = now()
|
|
from ticket_types
|
|
where bookings.ticket_type_id is null
|
|
and ticket_types.event_id = bookings.event_id
|
|
and ticket_types.code = bookings.ticket_type
|
|
`
|
|
|
|
const [fallbackBookingMode] = await sql<{ id: string, code: string }[]>`
|
|
select id, code
|
|
from booking_modes
|
|
where event_id = ${activeEvent.id}
|
|
and is_active = true
|
|
order by sort_order asc, created_at asc
|
|
limit 1
|
|
`
|
|
|
|
if (fallbackBookingMode) {
|
|
await sql`
|
|
update bookings
|
|
set
|
|
booking_mode_id = ${fallbackBookingMode.id},
|
|
booking_mode = ${fallbackBookingMode.code},
|
|
updated_at = now()
|
|
where booking_mode_id is null
|
|
`
|
|
}
|
|
|
|
const [fallbackTicketType] = await sql<{ id: string, code: string }[]>`
|
|
select id, code
|
|
from ticket_types
|
|
where event_id = ${activeEvent.id}
|
|
and is_active = true
|
|
order by sort_order asc, created_at asc
|
|
limit 1
|
|
`
|
|
|
|
if (fallbackTicketType) {
|
|
await sql`
|
|
update bookings
|
|
set
|
|
ticket_type_id = ${fallbackTicketType.id},
|
|
ticket_type = ${fallbackTicketType.code},
|
|
updated_at = now()
|
|
where ticket_type_id is null
|
|
`
|
|
}
|
|
}
|
|
|
|
await sql`
|
|
create unique index if not exists booking_settings_event_id_idx
|
|
on booking_settings (event_id)
|
|
`
|
|
|
|
await sql`
|
|
alter table bookings
|
|
alter column person_in_charge_name drop not null
|
|
`
|
|
|
|
await sql`
|
|
alter table bookings
|
|
alter column person_in_charge_phone_number drop not null
|
|
`
|
|
|
|
await sql`
|
|
alter table bookings
|
|
alter column event_id set not null
|
|
`
|
|
|
|
await sql`
|
|
alter table bookings
|
|
alter column booking_mode_id set not null
|
|
`
|
|
|
|
await sql`
|
|
alter table bookings
|
|
alter column ticket_type_id set not null
|
|
`
|
|
|
|
await sql`
|
|
alter table booking_settings
|
|
alter column event_id set not null
|
|
`
|
|
|
|
await sql`
|
|
alter table bookings
|
|
drop constraint if exists bookings_event_id_fkey
|
|
`
|
|
|
|
await sql`
|
|
alter table bookings
|
|
add constraint bookings_event_id_fkey
|
|
foreign key (event_id) references dinner_events(id) on delete restrict
|
|
`
|
|
|
|
await sql`
|
|
alter table bookings
|
|
drop constraint if exists bookings_booking_mode_id_fkey
|
|
`
|
|
|
|
await sql`
|
|
alter table bookings
|
|
add constraint bookings_booking_mode_id_fkey
|
|
foreign key (booking_mode_id) references booking_modes(id) on delete restrict
|
|
`
|
|
|
|
await sql`
|
|
alter table bookings
|
|
drop constraint if exists bookings_ticket_type_id_fkey
|
|
`
|
|
|
|
await sql`
|
|
alter table bookings
|
|
add constraint bookings_ticket_type_id_fkey
|
|
foreign key (ticket_type_id) references ticket_types(id) on delete restrict
|
|
`
|
|
|
|
await sql`
|
|
alter table bookings
|
|
drop constraint if exists bookings_status_fkey
|
|
`
|
|
|
|
await sql`
|
|
alter table bookings
|
|
add constraint bookings_status_fkey
|
|
foreign key (status) references booking_statuses(code) on delete restrict
|
|
`
|
|
|
|
await sql`
|
|
alter table booking_settings
|
|
drop constraint if exists booking_settings_event_id_fkey
|
|
`
|
|
|
|
await sql`
|
|
alter table booking_settings
|
|
add constraint booking_settings_event_id_fkey
|
|
foreign key (event_id) references dinner_events(id) on delete cascade
|
|
`
|
|
|
|
await sql`
|
|
update booking_settings
|
|
set
|
|
total_seats = total_tables * coalesce((
|
|
select booking_modes.seats_per_unit
|
|
from booking_modes
|
|
where booking_modes.event_id = booking_settings.event_id
|
|
and booking_modes.code = 'table'
|
|
order by booking_modes.sort_order asc
|
|
limit 1
|
|
), 1),
|
|
updated_at = now()
|
|
where total_seats is null
|
|
and total_tables is not null
|
|
`
|
|
|
|
const existingBookings = await sql<{ id: string, seat_count: number | string }[]>`
|
|
select
|
|
id,
|
|
seat_count
|
|
from bookings
|
|
`
|
|
|
|
for (const booking of existingBookings) {
|
|
const seatCount = typeof booking.seat_count === 'number'
|
|
? booking.seat_count
|
|
: Number.parseInt(booking.seat_count, 10)
|
|
|
|
const existingSeatRows = await sql<{ seat_number: number | string }[]>`
|
|
select seat_number
|
|
from booking_seats
|
|
where booking_id = ${booking.id}
|
|
`
|
|
|
|
const existingSeatNumbers = new Set(
|
|
existingSeatRows.map((seat) => typeof seat.seat_number === 'number'
|
|
? seat.seat_number
|
|
: Number.parseInt(seat.seat_number, 10))
|
|
)
|
|
|
|
for (let seatNumber = 1; seatNumber <= seatCount; seatNumber += 1) {
|
|
if (existingSeatNumbers.has(seatNumber)) {
|
|
continue
|
|
}
|
|
|
|
await sql`
|
|
insert into booking_seats (
|
|
id,
|
|
booking_id,
|
|
seat_number,
|
|
seat_token
|
|
)
|
|
values (
|
|
${randomUUID()},
|
|
${booking.id},
|
|
${seatNumber},
|
|
${randomToken(24)}
|
|
)
|
|
on conflict (booking_id, seat_number) do nothing
|
|
`
|
|
}
|
|
}
|
|
|
|
await sql`
|
|
alter table bookings
|
|
alter column receipt_token set not null
|
|
`
|
|
|
|
const [existingSuperAdmin] = await sql<{ id: string }[]>`
|
|
select id
|
|
from users
|
|
where username = 'xiaomai'
|
|
limit 1
|
|
`
|
|
|
|
if (!existingSuperAdmin) {
|
|
const passwordHash = await hashPassword(DEFAULT_USER_PASSWORD)
|
|
|
|
await sql`
|
|
insert into users (
|
|
id,
|
|
username,
|
|
full_name,
|
|
role,
|
|
password_hash,
|
|
must_change_password,
|
|
is_active,
|
|
created_by
|
|
)
|
|
values (
|
|
${randomUUID()},
|
|
'xiaomai',
|
|
'Xiaomai',
|
|
'super_admin',
|
|
${passwordHash},
|
|
true,
|
|
true,
|
|
null
|
|
)
|
|
`
|
|
}
|
|
|
|
await sql`
|
|
update users
|
|
set
|
|
phone_number = '+601157753558',
|
|
updated_at = now()
|
|
where username = 'xiaomai'
|
|
and (phone_number is null or phone_number = '')
|
|
`
|
|
}
|