349 lines
9.0 KiB
PL/PgSQL
349 lines
9.0 KiB
PL/PgSQL
-- Extend the empty CONDO schema for the confirmed booking 1:N semantics and
|
|
-- lossless legacy history import. This migration does not touch other schemas.
|
|
|
|
CREATE TABLE condon.bookings (
|
|
confirmation_no varchar(64) PRIMARY KEY,
|
|
created_at timestamptz NOT NULL DEFAULT pg_catalog.clock_timestamp(),
|
|
CONSTRAINT bookings_confirmation_no_check
|
|
CHECK (confirmation_no ~ '^[0-9]+$')
|
|
);
|
|
|
|
COMMENT ON TABLE condon.bookings IS
|
|
'One row per booking/Confirmation; one booking may have many usage records';
|
|
|
|
ALTER TABLE condon.usage_records
|
|
DROP CONSTRAINT usage_records_confirmation_no_key,
|
|
DROP CONSTRAINT usage_records_multiplier_check,
|
|
DROP CONSTRAINT usage_records_use_nights_check,
|
|
DROP CONSTRAINT usage_records_balance_check;
|
|
|
|
ALTER TABLE condon.usage_records
|
|
ALTER COLUMN used_room_type_code DROP NOT NULL,
|
|
ALTER COLUMN applied_multiplier DROP NOT NULL;
|
|
|
|
ALTER TABLE condon.usage_records
|
|
ADD COLUMN raw_used_room_type text NOT NULL DEFAULT '',
|
|
ADD COLUMN room_count integer NOT NULL DEFAULT 1,
|
|
ADD COLUMN source_sheet varchar(128),
|
|
ADD COLUMN source_row integer,
|
|
ADD COLUMN source_sequence integer,
|
|
ADD COLUMN import_batch varchar(128);
|
|
|
|
ALTER TABLE condon.usage_records
|
|
ADD CONSTRAINT usage_records_confirmation_booking_fk
|
|
FOREIGN KEY (confirmation_no)
|
|
REFERENCES condon.bookings(confirmation_no)
|
|
ON UPDATE RESTRICT
|
|
ON DELETE RESTRICT,
|
|
ADD CONSTRAINT usage_records_room_count_check
|
|
CHECK (room_count >= 1),
|
|
ADD CONSTRAINT usage_records_source_location_check
|
|
CHECK (
|
|
(source_sheet IS NULL AND source_row IS NULL AND source_sequence IS NULL)
|
|
OR
|
|
(source_sheet IS NOT NULL AND source_row > 0 AND source_sequence IS NOT NULL)
|
|
),
|
|
ADD CONSTRAINT usage_records_multiplier_check
|
|
CHECK (
|
|
(rule_version = 'legacy-source' AND applied_multiplier IS NULL)
|
|
OR
|
|
(rule_version <> 'legacy-source' AND applied_multiplier BETWEEN 1 AND 3)
|
|
),
|
|
ADD CONSTRAINT usage_records_use_nights_check
|
|
CHECK (
|
|
(
|
|
rule_version = 'legacy-source'
|
|
AND use_nights > 0
|
|
)
|
|
OR
|
|
(
|
|
rule_version <> 'legacy-source'
|
|
AND used_room_type_code IS NOT NULL
|
|
AND applied_multiplier BETWEEN 1 AND 3
|
|
AND use_nights = night_count * applied_multiplier
|
|
AND use_nights > 0
|
|
)
|
|
),
|
|
ADD CONSTRAINT usage_records_balance_check
|
|
CHECK (
|
|
balance_before >= use_nights
|
|
AND balance_after = balance_before - use_nights
|
|
AND balance_after >= 0
|
|
);
|
|
|
|
CREATE INDEX bookings_created_idx
|
|
ON condon.bookings (created_at DESC, confirmation_no);
|
|
|
|
CREATE UNIQUE INDEX usage_records_source_location_key
|
|
ON condon.usage_records (import_batch, source_sheet, source_row)
|
|
WHERE import_batch IS NOT NULL
|
|
AND source_sheet IS NOT NULL
|
|
AND source_row IS NOT NULL;
|
|
|
|
CREATE FUNCTION condon.ensure_booking_for_usage()
|
|
RETURNS trigger
|
|
LANGUAGE plpgsql
|
|
VOLATILE
|
|
SECURITY INVOKER
|
|
SET search_path = pg_catalog
|
|
AS $function$
|
|
BEGIN
|
|
INSERT INTO condon.bookings (confirmation_no)
|
|
VALUES (NEW.confirmation_no)
|
|
ON CONFLICT (confirmation_no) DO NOTHING;
|
|
RETURN NEW;
|
|
END;
|
|
$function$;
|
|
|
|
CREATE TRIGGER usage_records_booking_trg
|
|
BEFORE INSERT ON condon.usage_records
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION condon.ensure_booking_for_usage();
|
|
|
|
-- New-record path: same business contract as v1, but Confirmation is a
|
|
-- booking key and may legitimately appear on multiple usage rows.
|
|
CREATE FUNCTION condon.create_usage_record_v2(
|
|
p_usage_record_id uuid,
|
|
p_ledger_id uuid,
|
|
p_owner_account_id uuid,
|
|
p_confirmation_no varchar,
|
|
p_check_in date,
|
|
p_check_out date,
|
|
p_used_room_type_code varchar,
|
|
p_manual_multiplier smallint,
|
|
p_remark text,
|
|
p_idempotency_key uuid
|
|
)
|
|
RETURNS condon.usage_records
|
|
LANGUAGE plpgsql
|
|
VOLATILE
|
|
SECURITY INVOKER
|
|
SET search_path = pg_catalog
|
|
AS $function$
|
|
DECLARE
|
|
v_purchased_room_type_code varchar(16);
|
|
v_multiplier smallint;
|
|
v_night_count integer;
|
|
v_use_nights integer;
|
|
v_balance_before integer;
|
|
v_balance_after integer;
|
|
v_period condon.entitlement_periods%ROWTYPE;
|
|
v_existing condon.usage_records%ROWTYPE;
|
|
v_usage condon.usage_records%ROWTYPE;
|
|
v_now timestamptz := pg_catalog.clock_timestamp();
|
|
v_updated_count integer;
|
|
BEGIN
|
|
IF p_usage_record_id IS NULL
|
|
OR p_ledger_id IS NULL
|
|
OR p_owner_account_id IS NULL
|
|
OR p_idempotency_key IS NULL
|
|
THEN
|
|
RAISE EXCEPTION USING
|
|
ERRCODE = '22023',
|
|
MESSAGE = 'CONDON_REQUIRED_ID_MISSING';
|
|
END IF;
|
|
|
|
IF p_confirmation_no IS NULL
|
|
OR p_confirmation_no !~ '^[0-9]+$'
|
|
THEN
|
|
RAISE EXCEPTION USING
|
|
ERRCODE = '22023',
|
|
MESSAGE = 'CONDON_INVALID_CONFIRMATION_NO';
|
|
END IF;
|
|
|
|
IF p_check_in IS NULL
|
|
OR p_check_out IS NULL
|
|
OR p_check_out <= p_check_in
|
|
THEN
|
|
RAISE EXCEPTION USING
|
|
ERRCODE = '22023',
|
|
MESSAGE = 'CONDON_INVALID_STAY_DATES';
|
|
END IF;
|
|
|
|
IF EXTRACT(year FROM p_check_in)
|
|
<> EXTRACT(year FROM p_check_out)
|
|
THEN
|
|
RAISE EXCEPTION USING
|
|
ERRCODE = '22023',
|
|
MESSAGE = 'CONDON_CROSS_YEAR_STAY_NOT_SUPPORTED';
|
|
END IF;
|
|
|
|
SELECT purchased_room_type_code
|
|
INTO v_purchased_room_type_code
|
|
FROM condon.owner_accounts
|
|
WHERE id = p_owner_account_id
|
|
FOR SHARE;
|
|
|
|
IF NOT FOUND THEN
|
|
RAISE EXCEPTION USING
|
|
ERRCODE = 'P0002',
|
|
MESSAGE = 'CONDON_OWNER_ACCOUNT_NOT_FOUND';
|
|
END IF;
|
|
|
|
v_multiplier := condon.calculate_multiplier(
|
|
v_purchased_room_type_code,
|
|
p_used_room_type_code,
|
|
p_manual_multiplier
|
|
);
|
|
v_night_count := p_check_out - p_check_in;
|
|
v_use_nights := v_night_count * v_multiplier;
|
|
|
|
PERFORM pg_catalog.pg_advisory_xact_lock(
|
|
pg_catalog.hashtextextended(p_idempotency_key::text, 0)
|
|
);
|
|
|
|
SELECT *
|
|
INTO v_existing
|
|
FROM condon.usage_records
|
|
WHERE idempotency_key = p_idempotency_key;
|
|
|
|
IF FOUND THEN
|
|
IF v_existing.owner_account_id IS DISTINCT FROM p_owner_account_id
|
|
OR v_existing.confirmation_no IS DISTINCT FROM p_confirmation_no
|
|
OR v_existing.check_in IS DISTINCT FROM p_check_in
|
|
OR v_existing.check_out IS DISTINCT FROM p_check_out
|
|
OR v_existing.used_room_type_code IS DISTINCT FROM p_used_room_type_code
|
|
OR v_existing.applied_multiplier IS DISTINCT FROM v_multiplier
|
|
OR v_existing.remark IS DISTINCT FROM COALESCE(p_remark, '')
|
|
THEN
|
|
RAISE EXCEPTION USING
|
|
ERRCODE = '23505',
|
|
MESSAGE = 'CONDON_IDEMPOTENCY_KEY_REUSED';
|
|
END IF;
|
|
RETURN v_existing;
|
|
END IF;
|
|
|
|
PERFORM pg_catalog.pg_advisory_xact_lock(
|
|
pg_catalog.hashtextextended('confirmation:' || p_confirmation_no, 0)
|
|
);
|
|
|
|
INSERT INTO condon.bookings (confirmation_no)
|
|
VALUES (p_confirmation_no)
|
|
ON CONFLICT (confirmation_no) DO NOTHING;
|
|
|
|
SELECT *
|
|
INTO v_period
|
|
FROM condon.entitlement_periods
|
|
WHERE owner_account_id = p_owner_account_id
|
|
AND period_year = EXTRACT(year FROM p_check_in)::smallint
|
|
FOR UPDATE;
|
|
|
|
IF NOT FOUND THEN
|
|
RAISE EXCEPTION USING
|
|
ERRCODE = 'P0002',
|
|
MESSAGE = 'CONDON_ENTITLEMENT_PERIOD_NOT_FOUND';
|
|
END IF;
|
|
|
|
IF p_check_in < v_period.period_start
|
|
OR p_check_out > v_period.period_end
|
|
THEN
|
|
RAISE EXCEPTION USING
|
|
ERRCODE = '22023',
|
|
MESSAGE = 'CONDON_STAY_OUTSIDE_ENTITLEMENT_PERIOD';
|
|
END IF;
|
|
|
|
v_balance_before := v_period.current_balance;
|
|
v_balance_after := v_balance_before - v_use_nights;
|
|
|
|
IF v_balance_after < 0 THEN
|
|
RAISE EXCEPTION USING
|
|
ERRCODE = 'P0001',
|
|
MESSAGE = 'CONDON_INSUFFICIENT_BALANCE';
|
|
END IF;
|
|
|
|
INSERT INTO condon.usage_records (
|
|
id,
|
|
owner_account_id,
|
|
entitlement_period_id,
|
|
confirmation_no,
|
|
check_in,
|
|
check_out,
|
|
night_count,
|
|
used_room_type_code,
|
|
raw_used_room_type,
|
|
applied_multiplier,
|
|
rule_version,
|
|
use_nights,
|
|
balance_before,
|
|
balance_after,
|
|
room_count,
|
|
remark,
|
|
idempotency_key,
|
|
created_at
|
|
)
|
|
VALUES (
|
|
p_usage_record_id,
|
|
p_owner_account_id,
|
|
v_period.id,
|
|
p_confirmation_no,
|
|
p_check_in,
|
|
p_check_out,
|
|
v_night_count,
|
|
p_used_room_type_code,
|
|
p_used_room_type_code,
|
|
v_multiplier,
|
|
'v1',
|
|
v_use_nights,
|
|
v_balance_before,
|
|
v_balance_after,
|
|
1,
|
|
COALESCE(p_remark, ''),
|
|
p_idempotency_key,
|
|
v_now
|
|
)
|
|
RETURNING * INTO v_usage;
|
|
|
|
INSERT INTO condon.entitlement_ledger (
|
|
id,
|
|
entitlement_period_id,
|
|
usage_record_id,
|
|
entry_type,
|
|
delta_nights,
|
|
balance_before,
|
|
balance_after,
|
|
occurred_at
|
|
)
|
|
VALUES (
|
|
p_ledger_id,
|
|
v_period.id,
|
|
p_usage_record_id,
|
|
'usage',
|
|
-v_use_nights,
|
|
v_balance_before,
|
|
v_balance_after,
|
|
v_now
|
|
);
|
|
|
|
UPDATE condon.entitlement_periods
|
|
SET
|
|
current_balance = v_balance_after,
|
|
row_version = row_version + 1,
|
|
updated_at = v_now
|
|
WHERE id = v_period.id
|
|
AND row_version = v_period.row_version;
|
|
|
|
GET DIAGNOSTICS v_updated_count = ROW_COUNT;
|
|
IF v_updated_count <> 1 THEN
|
|
RAISE EXCEPTION USING
|
|
ERRCODE = '40001',
|
|
MESSAGE = 'CONDON_BALANCE_CONCURRENTLY_CHANGED';
|
|
END IF;
|
|
|
|
RETURN v_usage;
|
|
END;
|
|
$function$;
|
|
|
|
REVOKE ALL ON FUNCTION condon.ensure_booking_for_usage() FROM PUBLIC;
|
|
REVOKE ALL ON FUNCTION condon.create_usage_record_v2(
|
|
uuid,
|
|
uuid,
|
|
uuid,
|
|
varchar,
|
|
date,
|
|
date,
|
|
varchar,
|
|
smallint,
|
|
text,
|
|
uuid
|
|
) FROM PUBLIC;
|