wyndham-Condon/backend/migrations/001_create_condon_schema.up.sql

CREATE SCHEMA condon;
REVOKE ALL ON SCHEMA condon FROM PUBLIC;
COMMENT ON SCHEMA condon IS 'CONDO owner entitlement backend';

CREATE TABLE condon.schema_migrations (
  version varchar(64) PRIMARY KEY,
  checksum varchar(64) NOT NULL,
  applied_at timestamptz NOT NULL DEFAULT pg_catalog.clock_timestamp(),
  CONSTRAINT schema_migrations_checksum_check
    CHECK (checksum ~ '^[0-9a-f]{64}$')
);

CREATE TABLE condon.room_types (
  code varchar(16) PRIMARY KEY,
  entitlement_tier smallint,
  requires_manual_multiplier boolean NOT NULL DEFAULT false,
  created_at timestamptz NOT NULL DEFAULT pg_catalog.clock_timestamp(),
  updated_at timestamptz NOT NULL DEFAULT pg_catalog.clock_timestamp(),
  CONSTRAINT room_types_code_check
    CHECK (code ~ '^[A-Z0-9]+$'),
  CONSTRAINT room_types_rule_shape_check
    CHECK (
      (requires_manual_multiplier AND entitlement_tier IS NULL)
      OR
      (
        NOT requires_manual_multiplier
        AND entitlement_tier BETWEEN 1 AND 3
      )
    )
);

CREATE TABLE condon.owner_accounts (
  id uuid PRIMARY KEY,
  account_no integer,
  transfer_date date,
  owner_name text NOT NULL,
  room_no varchar(32) NOT NULL,
  purchased_room_type_code varchar(16) NOT NULL,
  unit_no varchar(32) NOT NULL,
  member_no varchar(32) NOT NULL,
  created_at timestamptz NOT NULL DEFAULT pg_catalog.clock_timestamp(),
  updated_at timestamptz NOT NULL DEFAULT pg_catalog.clock_timestamp(),
  CONSTRAINT owner_accounts_account_no_key UNIQUE (account_no),
  CONSTRAINT owner_accounts_room_no_key UNIQUE (room_no),
  CONSTRAINT owner_accounts_account_no_check
    CHECK (account_no IS NULL OR account_no > 0),
  CONSTRAINT owner_accounts_owner_name_check
    CHECK (pg_catalog.btrim(owner_name) <> ''),
  CONSTRAINT owner_accounts_room_no_check
    CHECK (pg_catalog.btrim(room_no) <> ''),
  CONSTRAINT owner_accounts_unit_no_check
    CHECK (pg_catalog.btrim(unit_no) <> ''),
  CONSTRAINT owner_accounts_member_no_check
    CHECK (pg_catalog.btrim(member_no) <> ''),
  CONSTRAINT owner_accounts_purchased_room_type_fk
    FOREIGN KEY (purchased_room_type_code)
    REFERENCES condon.room_types(code)
    ON UPDATE RESTRICT
    ON DELETE RESTRICT
);

CREATE TABLE condon.entitlement_periods (
  id uuid PRIMARY KEY,
  owner_account_id uuid NOT NULL,
  period_year smallint NOT NULL,
  period_start date NOT NULL,
  period_end date NOT NULL,
  annual_grant integer NOT NULL DEFAULT 15,
  carry_forward integer NOT NULL DEFAULT 0,
  current_balance integer NOT NULL,
  row_version integer NOT NULL DEFAULT 1,
  created_at timestamptz NOT NULL DEFAULT pg_catalog.clock_timestamp(),
  updated_at timestamptz NOT NULL DEFAULT pg_catalog.clock_timestamp(),
  CONSTRAINT entitlement_periods_owner_year_key
    UNIQUE (owner_account_id, period_year),
  CONSTRAINT entitlement_periods_id_owner_key
    UNIQUE (id, owner_account_id),
  CONSTRAINT entitlement_periods_owner_fk
    FOREIGN KEY (owner_account_id)
    REFERENCES condon.owner_accounts(id)
    ON UPDATE RESTRICT
    ON DELETE RESTRICT,
  CONSTRAINT entitlement_periods_year_check
    CHECK (period_year BETWEEN 2000 AND 9999),
  CONSTRAINT entitlement_periods_dates_check
    CHECK (
      period_start = pg_catalog.make_date(period_year::integer, 1, 1)
      AND period_end = pg_catalog.make_date(period_year::integer, 12, 31)
    ),
  CONSTRAINT entitlement_periods_annual_grant_check
    CHECK (annual_grant >= 0),
  CONSTRAINT entitlement_periods_carry_forward_check
    CHECK (carry_forward >= 0),
  CONSTRAINT entitlement_periods_current_balance_check
    CHECK (current_balance >= 0),
  CONSTRAINT entitlement_periods_row_version_check
    CHECK (row_version >= 1)
);

CREATE TABLE condon.usage_records (
  id uuid PRIMARY KEY,
  owner_account_id uuid NOT NULL,
  entitlement_period_id uuid NOT NULL,
  confirmation_no varchar(64) NOT NULL,
  check_in date NOT NULL,
  check_out date NOT NULL,
  night_count integer NOT NULL,
  used_room_type_code varchar(16) NOT NULL,
  applied_multiplier smallint NOT NULL,
  rule_version varchar(32) NOT NULL DEFAULT 'v1',
  use_nights integer NOT NULL,
  balance_before integer NOT NULL,
  balance_after integer NOT NULL,
  remark text NOT NULL DEFAULT '',
  idempotency_key uuid NOT NULL,
  created_at timestamptz NOT NULL DEFAULT pg_catalog.clock_timestamp(),
  CONSTRAINT usage_records_confirmation_no_key UNIQUE (confirmation_no),
  CONSTRAINT usage_records_idempotency_key_key UNIQUE (idempotency_key),
  CONSTRAINT usage_records_id_period_key UNIQUE (id, entitlement_period_id),
  CONSTRAINT usage_records_account_fk
    FOREIGN KEY (owner_account_id)
    REFERENCES condon.owner_accounts(id)
    ON UPDATE RESTRICT
    ON DELETE RESTRICT,
  CONSTRAINT usage_records_period_account_fk
    FOREIGN KEY (entitlement_period_id, owner_account_id)
    REFERENCES condon.entitlement_periods(id, owner_account_id)
    ON UPDATE RESTRICT
    ON DELETE RESTRICT,
  CONSTRAINT usage_records_used_room_type_fk
    FOREIGN KEY (used_room_type_code)
    REFERENCES condon.room_types(code)
    ON UPDATE RESTRICT
    ON DELETE RESTRICT,
  CONSTRAINT usage_records_confirmation_no_check
    CHECK (confirmation_no ~ '^[0-9]+$'),
  CONSTRAINT usage_records_dates_check
    CHECK (check_out > check_in),
  CONSTRAINT usage_records_same_year_check
    CHECK (
      EXTRACT(year FROM check_in)
      = EXTRACT(year FROM check_out)
    ),
  CONSTRAINT usage_records_night_count_check
    CHECK (night_count = check_out - check_in AND night_count > 0),
  CONSTRAINT usage_records_multiplier_check
    CHECK (applied_multiplier BETWEEN 1 AND 3),
  CONSTRAINT usage_records_use_nights_check
    CHECK (
      use_nights = night_count * applied_multiplier
      AND use_nights > 0
    ),
  CONSTRAINT usage_records_balance_check
    CHECK (
      balance_before >= use_nights
      AND balance_after = balance_before - use_nights
      AND balance_after >= 0
    )
);

CREATE TABLE condon.entitlement_ledger (
  id uuid PRIMARY KEY,
  entitlement_period_id uuid NOT NULL,
  usage_record_id uuid,
  entry_type varchar(32) NOT NULL,
  delta_nights integer NOT NULL,
  balance_before integer NOT NULL,
  balance_after integer NOT NULL,
  occurred_at timestamptz NOT NULL DEFAULT pg_catalog.clock_timestamp(),
  CONSTRAINT entitlement_ledger_usage_record_key
    UNIQUE (usage_record_id),
  CONSTRAINT entitlement_ledger_period_fk
    FOREIGN KEY (entitlement_period_id)
    REFERENCES condon.entitlement_periods(id)
    ON UPDATE RESTRICT
    ON DELETE RESTRICT,
  CONSTRAINT entitlement_ledger_usage_period_fk
    FOREIGN KEY (usage_record_id, entitlement_period_id)
    REFERENCES condon.usage_records(id, entitlement_period_id)
    ON UPDATE RESTRICT
    ON DELETE RESTRICT,
  CONSTRAINT entitlement_ledger_entry_type_check
    CHECK (entry_type IN ('annual_grant', 'carry_forward', 'usage')),
  CONSTRAINT entitlement_ledger_balance_check
    CHECK (
      balance_after = balance_before + delta_nights
      AND balance_after >= 0
    ),
  CONSTRAINT entitlement_ledger_entry_shape_check
    CHECK (
      (
        entry_type = 'usage'
        AND usage_record_id IS NOT NULL
        AND delta_nights < 0
      )
      OR
      (
        entry_type IN ('annual_grant', 'carry_forward')
        AND usage_record_id IS NULL
        AND delta_nights > 0
      )
    )
);

CREATE INDEX owner_accounts_member_no_idx
  ON condon.owner_accounts (member_no);
CREATE INDEX owner_accounts_purchased_room_type_idx
  ON condon.owner_accounts (purchased_room_type_code);
CREATE INDEX usage_records_owner_created_idx
  ON condon.usage_records (owner_account_id, created_at DESC, id DESC);
CREATE INDEX usage_records_period_idx
  ON condon.usage_records (entitlement_period_id);
CREATE INDEX usage_records_used_type_check_in_idx
  ON condon.usage_records (used_room_type_code, check_in);
CREATE INDEX entitlement_ledger_period_occurred_idx
  ON condon.entitlement_ledger (entitlement_period_id, occurred_at, id);

INSERT INTO condon.room_types (
  code,
  entitlement_tier,
  requires_manual_multiplier
)
VALUES
  ('RM1', 1, false),
  ('RM2', 1, false),
  ('RM3', 1, false),
  ('RM4', 1, false),
  ('UG1', 1, false),
  ('UG2', 1, false),
  ('SU1', 2, false),
  ('SU2', 2, false),
  ('SU6', 2, false),
  ('SU3', 3, false),
  ('AC2', NULL, true);

CREATE FUNCTION condon.calculate_multiplier(
  p_purchased_room_type_code varchar,
  p_used_room_type_code varchar,
  p_manual_multiplier smallint
)
RETURNS smallint
LANGUAGE plpgsql
STABLE
SECURITY INVOKER
SET search_path = pg_catalog
AS $function$
DECLARE
  v_purchased_tier smallint;
  v_used_tier smallint;
  v_purchased_manual boolean;
  v_used_manual boolean;
BEGIN
  SELECT
    entitlement_tier,
    requires_manual_multiplier
  INTO
    v_purchased_tier,
    v_purchased_manual
  FROM condon.room_types
  WHERE code = p_purchased_room_type_code;

  IF NOT FOUND THEN
    RAISE EXCEPTION USING
      ERRCODE = '22023',
      MESSAGE = 'CONDON_PURCHASED_ROOM_TYPE_NOT_FOUND';
  END IF;

  SELECT
    entitlement_tier,
    requires_manual_multiplier
  INTO
    v_used_tier,
    v_used_manual
  FROM condon.room_types
  WHERE code = p_used_room_type_code;

  IF NOT FOUND THEN
    RAISE EXCEPTION USING
      ERRCODE = '22023',
      MESSAGE = 'CONDON_USED_ROOM_TYPE_NOT_FOUND';
  END IF;

  IF v_purchased_manual OR v_used_manual THEN
    IF p_manual_multiplier IS NULL OR p_manual_multiplier NOT BETWEEN 1 AND 3 THEN
      RAISE EXCEPTION USING
        ERRCODE = '22023',
        MESSAGE = 'CONDON_MANUAL_MULTIPLIER_REQUIRED';
    END IF;
    RETURN p_manual_multiplier;
  END IF;

  IF p_manual_multiplier IS NOT NULL THEN
    RAISE EXCEPTION USING
      ERRCODE = '22023',
      MESSAGE = 'CONDON_MANUAL_MULTIPLIER_NOT_ALLOWED';
  END IF;

  RETURN GREATEST(
    1,
    v_used_tier - v_purchased_tier + 1
  )::smallint;
END;
$function$;

CREATE FUNCTION condon.open_entitlement_period(
  p_period_id uuid,
  p_owner_account_id uuid,
  p_period_year smallint,
  p_annual_grant_ledger_id uuid,
  p_carry_forward_ledger_id uuid
)
RETURNS condon.entitlement_periods
LANGUAGE plpgsql
VOLATILE
SECURITY INVOKER
SET search_path = pg_catalog
AS $function$
DECLARE
  v_carry_forward integer := 0;
  v_period condon.entitlement_periods%ROWTYPE;
  v_now timestamptz := pg_catalog.clock_timestamp();
BEGIN
  IF p_period_id IS NULL
    OR p_owner_account_id IS NULL
    OR p_annual_grant_ledger_id IS NULL
  THEN
    RAISE EXCEPTION USING
      ERRCODE = '22023',
      MESSAGE = 'CONDON_REQUIRED_ID_MISSING';
  END IF;

  IF p_period_year NOT BETWEEN 2000 AND 9999 THEN
    RAISE EXCEPTION USING
      ERRCODE = '22023',
      MESSAGE = 'CONDON_INVALID_PERIOD_YEAR';
  END IF;

  PERFORM 1
  FROM condon.owner_accounts
  WHERE id = p_owner_account_id
  FOR UPDATE;

  IF NOT FOUND THEN
    RAISE EXCEPTION USING
      ERRCODE = 'P0002',
      MESSAGE = 'CONDON_OWNER_ACCOUNT_NOT_FOUND';
  END IF;

  IF EXISTS (
    SELECT 1
    FROM condon.entitlement_periods
    WHERE owner_account_id = p_owner_account_id
      AND period_year = p_period_year
  ) THEN
    RAISE EXCEPTION USING
      ERRCODE = '23505',
      MESSAGE = 'CONDON_ENTITLEMENT_PERIOD_EXISTS';
  END IF;

  SELECT current_balance
  INTO v_carry_forward
  FROM condon.entitlement_periods
  WHERE owner_account_id = p_owner_account_id
    AND period_year = p_period_year - 1
  FOR UPDATE;

  v_carry_forward := COALESCE(v_carry_forward, 0);

  IF v_carry_forward > 0 AND p_carry_forward_ledger_id IS NULL THEN
    RAISE EXCEPTION USING
      ERRCODE = '22023',
      MESSAGE = 'CONDON_CARRY_FORWARD_LEDGER_ID_REQUIRED';
  END IF;

  IF v_carry_forward = 0 AND p_carry_forward_ledger_id IS NOT NULL THEN
    RAISE EXCEPTION USING
      ERRCODE = '22023',
      MESSAGE = 'CONDON_CARRY_FORWARD_LEDGER_ID_NOT_ALLOWED';
  END IF;

  INSERT INTO condon.entitlement_periods (
    id,
    owner_account_id,
    period_year,
    period_start,
    period_end,
    annual_grant,
    carry_forward,
    current_balance,
    row_version,
    created_at,
    updated_at
  )
  VALUES (
    p_period_id,
    p_owner_account_id,
    p_period_year,
    pg_catalog.make_date(p_period_year::integer, 1, 1),
    pg_catalog.make_date(p_period_year::integer, 12, 31),
    15,
    v_carry_forward,
    15 + v_carry_forward,
    1,
    v_now,
    v_now
  )
  RETURNING * INTO v_period;

  INSERT INTO condon.entitlement_ledger (
    id,
    entitlement_period_id,
    usage_record_id,
    entry_type,
    delta_nights,
    balance_before,
    balance_after,
    occurred_at
  )
  VALUES (
    p_annual_grant_ledger_id,
    p_period_id,
    NULL,
    'annual_grant',
    15,
    0,
    15,
    v_now
  );

  IF v_carry_forward > 0 THEN
    INSERT INTO condon.entitlement_ledger (
      id,
      entitlement_period_id,
      usage_record_id,
      entry_type,
      delta_nights,
      balance_before,
      balance_after,
      occurred_at
    )
    VALUES (
      p_carry_forward_ledger_id,
      p_period_id,
      NULL,
      'carry_forward',
      v_carry_forward,
      15,
      15 + v_carry_forward,
      v_now
    );
  END IF;

  RETURN v_period;
END;
$function$;

CREATE FUNCTION condon.create_usage_record(
  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)
  );

  IF EXISTS (
    SELECT 1
    FROM condon.usage_records
    WHERE confirmation_no = p_confirmation_no
  ) THEN
    RAISE EXCEPTION USING
      ERRCODE = '23505',
      MESSAGE = 'CONDON_CONFIRMATION_NO_EXISTS';
  END IF;

  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,
    applied_multiplier,
    rule_version,
    use_nights,
    balance_before,
    balance_after,
    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,
    v_multiplier,
    'v1',
    v_use_nights,
    v_balance_before,
    v_balance_after,
    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.calculate_multiplier(
  varchar,
  varchar,
  smallint
) FROM PUBLIC;
REVOKE ALL ON FUNCTION condon.open_entitlement_period(
  uuid,
  uuid,
  smallint,
  uuid,
  uuid
) FROM PUBLIC;
REVOKE ALL ON FUNCTION condon.create_usage_record(
  uuid,
  uuid,
  uuid,
  varchar,
  date,
  date,
  varchar,
  smallint,
  text,
  uuid
) FROM PUBLIC;