-- 28절의 물리 스키마. 금액 계산과 다른 표와 비교하는 규칙은 앱에 둔다.

create table customers (
  id bigint generated always as identity primary key,
  login_id text not null,
  password_hash text not null,
  email text not null,
  name text not null,
  phone text,
  mobile text not null,
  postal_code text,
  address text,
  address_detail text,
  terms_version text not null,
  terms_accepted_at timestamptz not null,
  created_at timestamptz not null default now(),
  check (char_length(login_id) between 4 and 20 and login_id = lower(login_id) and login_id ~ '^[a-z]' and position('@' in login_id) = 0),
  check (email = lower(email)),
  check (char_length(name) between 1 and 100 and name = btrim(name)),
  check (password_hash like '$argon2id$v=19$m=19456,t=2,p=1$%'),
  check (phone is null or phone ~ '^v1:'),
  check (mobile ~ '^v1:'),
  check (postal_code is null or postal_code ~ '^v1:'),
  check (address is null or address ~ '^v1:'),
  check (address_detail is null or address_detail ~ '^v1:')
);

create table tlds (
  id bigint generated always as identity primary key,
  label text not null unique,
  identity_required boolean not null,
  created_at timestamptz not null default now(),
  check (label ~ '^[a-z0-9]([a-z0-9-]{0,61}[a-z0-9])?$')
);

create table products (
  id bigint generated always as identity primary key,
  code text not null unique,
  product_type text not null,
  name text not null,
  billing_model text not null,
  status text not null,
  tld_id bigint references tlds (id) on delete restrict,
  created_at timestamptz not null default now(),
  check (product_type in ('domain', 'hosting', 'ssl', 'addon', 'mail', 'server', 'dbms', 'security', 'dns')),
  check (billing_model in ('one_time', 'recurring')),
  check (status in ('active', 'retired'))
);

create table prices (
  id bigint generated always as identity primary key,
  product_id bigint not null references products (id) on delete restrict,
  action text not null,
  period_unit text not null,
  period_count integer not null,
  currency char(3) not null default 'KRW',
  cost_amount bigint not null,
  sell_amount bigint not null,
  effective_from timestamptz not null,
  effective_to timestamptz,
  created_at timestamptz not null default now(),
  check (action in ('register', 'renew', 'transfer', 'restore', 'upgrade', 'addon_add', 'owner_change', 'usage')),
  check (period_unit in ('year', 'month', 'once'))
);

create table services (
  id bigint generated always as identity primary key,
  customer_id bigint not null references customers (id) on delete restrict,
  product_id bigint not null references products (id) on delete restrict,
  parent_service_id bigint references services (id) on delete restrict,
  kind text not null,
  status text not null,
  quantity integer not null default 1,
  auto_renew boolean not null,
  cancel_at timestamptz,
  suspend_reasons text[] not null default '{}',
  recurring_amount bigint,
  discount_percent integer,
  discount_until timestamptz,
  currency char(3) not null default 'KRW',
  started_at timestamptz,
  next_due_at timestamptz,
  expires_at timestamptz,
  included_traffic_bytes bigint,
  suspended_at timestamptz,
  terminated_at timestamptz,
  created_at timestamptz not null default now(),
  unique (id, kind),
  check (kind in ('domain', 'hosting', 'ssl', 'addon', 'mail', 'server', 'dbms', 'security', 'dns')),
  check (status in ('pending', 'active', 'past_due', 'suspended', 'expired', 'terminated', 'failed')),
  check (kind <> 'addon' or parent_service_id is not null),
  check ((status = 'suspended') = (suspend_reasons <> '{}')),
  check (discount_percent is null or (discount_percent >= 1 and discount_percent <= 99)),
  check (discount_percent is null or recurring_amount is null),
  check (discount_until is null or discount_percent is not null or recurring_amount is not null),
  check (included_traffic_bytes is null or (kind in ('hosting', 'server') and included_traffic_bytes >= 0)),
  check (suspend_reasons <@ array['unpaid', 'abuse', 'legal', 'customer']::text[])
);

create table payment_methods (
  id bigint generated always as identity primary key,
  customer_id bigint not null references customers (id) on delete restrict,
  method text not null,
  status text not null,
  billing_key text not null,
  billing_key_digest text not null,
  card_last4 text,
  card_issuer text,
  is_default boolean not null,
  created_at timestamptz not null default now(),
  check (method = 'card'),
  check (status in ('active', 'revoked')),
  check (is_default = false or status = 'active'),
  check (card_last4 is null or card_last4 ~ '^[0-9]{4}$'),
  check (billing_key_digest ~ '^[0-9a-f]{64}$'),
  check (billing_key ~ '^v1:')
);

create table orders (
  id bigint generated always as identity primary key,
  order_no text not null unique,
  customer_id bigint not null references customers (id) on delete restrict,
  source text not null,
  currency char(3) not null default 'KRW',
  status text not null,
  ordered_at timestamptz not null,
  idempotency_key text unique,
  created_at timestamptz not null default now(),
  check (source in ('customer', 'staff', 'system')),
  check (status in ('awaiting_payment', 'paid', 'partially_fulfilled', 'fulfilled', 'cancelled'))
);

create table service_usage (
  id bigint generated always as identity primary key,
  service_id bigint not null references services (id) on delete restrict,
  meter text not null,
  period_start timestamptz not null,
  period_end timestamptz not null,
  included_bytes bigint not null,
  used_bytes bigint not null,
  billed_bytes bigint not null,
  created_at timestamptz not null default now(),
  check (meter = 'traffic_bytes'),
  check (included_bytes >= 0 and used_bytes >= 0 and billed_bytes >= 0),
  check (period_end > period_start)
);

create table order_items (
  id bigint generated always as identity primary key,
  order_id bigint not null references orders (id) on delete restrict,
  parent_order_item_id bigint references order_items (id) on delete restrict,
  product_id bigint not null references products (id) on delete restrict,
  service_id bigint references services (id) on delete restrict,
  action text not null,
  status text not null,
  product_name text not null,
  period_unit text not null,
  period_count integer not null,
  period_start timestamptz,
  period_end timestamptz,
  quantity integer not null,
  unit_price bigint not null,
  supply_amount bigint not null,
  vat_amount bigint not null,
  cost_amount bigint not null,
  cost_currency char(3) not null default 'KRW',
  amount bigint not null,
  settled_amount bigint not null default 0,
  currency char(3) not null default 'KRW',
  requested_name text,
  from_product_id bigint references products (id) on delete restrict,
  from_service_id bigint references services (id) on delete restrict,
  to_customer_id bigint references customers (id) on delete restrict,
  auth_code text,
  usage_id bigint references service_usage (id) on delete restrict,
  config jsonb,
  price_id bigint references prices (id) on delete restrict,
  created_at timestamptz not null default now(),
  check (action in ('register', 'transfer_in', 'renew', 'restore', 'add', 'change', 'owner_change', 'credit', 'usage')),
  check (status in ('pending', 'paid', 'fulfilled', 'failed', 'cancelled', 'refunded')),
  check (period_unit in ('year', 'month', 'day', 'once')),
  check ((action = 'owner_change') = (to_customer_id is not null)),
  check ((action = 'owner_change') = (from_service_id is not null)),
  check ((action = 'usage') = (usage_id is not null)),
  check (auth_code is null or action = 'transfer_in'),
  check (requested_name is null or requested_name ~ '^[a-z0-9]([a-z0-9-]{0,61}[a-z0-9])?(\.[a-z0-9]([a-z0-9-]{0,61}[a-z0-9])?)+$')
);

create table payments (
  id bigint generated always as identity primary key,
  customer_id bigint not null references customers (id) on delete restrict,
  method text not null,
  status text not null,
  amount bigint not null,
  currency char(3) not null default 'KRW',
  pg_tid text unique,
  payment_method_id bigint references payment_methods (id) on delete restrict,
  paid_at timestamptz,
  created_at timestamptz not null default now(),
  check (method in ('card', 'bank_transfer')),
  check (status in ('pending', 'confirmed', 'failed')),
  check (payment_method_id is null or method = 'card')
);

create table payment_allocations (
  id bigint generated always as identity primary key,
  payment_id bigint not null references payments (id) on delete restrict,
  order_item_id bigint not null references order_items (id) on delete restrict,
  amount bigint not null,
  created_at timestamptz not null default now()
);

create table refunds (
  id bigint generated always as identity primary key,
  customer_id bigint not null references customers (id) on delete restrict,
  payment_id bigint references payments (id) on delete restrict,
  status text not null,
  method text not null,
  amount bigint not null,
  reason text not null,
  requested_by text not null,
  approved_by text,
  created_at timestamptz not null default now(),
  check (status in ('requested', 'approved', 'rejected', 'completed')),
  check (method in ('balance', 'bank', 'card'))
);

create table refund_lines (
  id bigint generated always as identity primary key,
  refund_id bigint not null references refunds (id) on delete restrict,
  order_item_id bigint not null references order_items (id) on delete restrict,
  amount bigint not null,
  created_at timestamptz not null default now()
);

create table customer_balance_entries (
  id bigint generated always as identity primary key,
  customer_id bigint not null references customers (id) on delete restrict,
  amount bigint not null,
  payment_id bigint references payments (id) on delete restrict,
  order_item_id bigint references order_items (id) on delete restrict,
  refund_id bigint references refunds (id) on delete restrict,
  reason text not null,
  created_at timestamptz not null default now(),
  check (num_nonnulls(payment_id, order_item_id, refund_id) = 1)
);

create table domains (
  id bigint generated always as identity primary key,
  domain_name text not null,
  tld_id bigint not null references tlds (id) on delete restrict,
  roid text,
  registry_account_id text not null,
  lifecycle_status text not null,
  epp_statuses text[] not null default '{}',
  registrant_lock_until timestamptz,
  registered_at timestamptz,
  expires_at timestamptz not null,
  registrar_renew_policy text not null,
  registry_autorenewed_at timestamptz,
  predecessor_domain_id bigint references domains (id) on delete restrict,
  created_at timestamptz not null default now(),
  check (lifecycle_status in ('active', 'expired_grace', 'redemption', 'pending_delete', 'pending_transfer', 'transferred_out', 'deleted')),
  check (registrar_renew_policy in ('follow_service', 'always', 'never')),
  check (domain_name ~ '^[a-z0-9]([a-z0-9-]{0,61}[a-z0-9])?(\.[a-z0-9]([a-z0-9-]{0,61}[a-z0-9])?)+$')
);

create table domain_services (
  service_id bigint primary key references services (id) on delete restrict,
  kind text not null,
  domain_id bigint not null references domains (id) on delete restrict,
  linked_at timestamptz not null,
  unlinked_at timestamptz,
  created_at timestamptz not null default now(),
  check (kind = 'domain'),
  foreign key (service_id, kind) references services (id, kind) on delete restrict
);

create table domain_reservations (
  id bigint generated always as identity primary key,
  requested_name text not null,
  order_item_id bigint not null references order_items (id) on delete restrict,
  expires_at timestamptz not null,
  released_at timestamptz,
  created_at timestamptz not null default now(),
  check (requested_name ~ '^[a-z0-9]([a-z0-9-]{0,61}[a-z0-9])?(\.[a-z0-9]([a-z0-9-]{0,61}[a-z0-9])?)+$')
);

create table hosting_servers (
  id bigint generated always as identity primary key,
  hostname text not null,
  panel_type text not null,
  status text not null,
  created_at timestamptz not null default now()
);

create table hosting_accounts (
  id bigint generated always as identity primary key,
  server_id bigint not null references hosting_servers (id) on delete restrict,
  panel_account_id text not null,
  username text not null,
  status text not null,
  os_template text,
  created_at timestamptz not null default now()
);

create table hosting_services (
  service_id bigint primary key references services (id) on delete restrict,
  kind text not null,
  hosting_account_id bigint not null references hosting_accounts (id) on delete restrict,
  created_at timestamptz not null default now(),
  check (kind = 'hosting'),
  foreign key (service_id, kind) references services (id, kind) on delete restrict
);

create table certificates (
  id bigint generated always as identity primary key,
  common_name text not null,
  issuer text not null,
  serial text not null,
  expires_at timestamptz not null,
  status text not null,
  dcv_method text,
  created_at timestamptz not null default now(),
  check (dcv_method is null or dcv_method in ('email', 'dns', 'http'))
);

create table ssl_services (
  service_id bigint primary key references services (id) on delete restrict,
  kind text not null,
  certificate_id bigint not null references certificates (id) on delete restrict,
  created_at timestamptz not null default now(),
  check (kind = 'ssl'),
  foreign key (service_id, kind) references services (id, kind) on delete restrict
);

create table contacts (
  id bigint generated always as identity primary key,
  customer_id bigint references customers (id) on delete restrict,
  name text not null,
  name_loc text,
  contact_kind text not null,
  organization text,
  email text not null,
  phone text not null,
  mobile text,
  street text not null,
  city text not null,
  state text,
  postal_code text not null,
  country_code text not null,
  registry_contact_id text,
  identity_kind text,
  identity_value text,
  created_at timestamptz not null default now(),
  check (contact_kind in ('individual', 'organization')),
  check ((contact_kind = 'organization') = (organization is not null)),
  check ((identity_kind is null) = (identity_value is null)),
  check (identity_kind is null or (contact_kind = 'individual' and identity_kind = 'birth_date') or (contact_kind = 'organization' and identity_kind = 'business_no')),
  check (identity_value is null or identity_value ~ '^v1:')
);

create table domain_contacts (
  id bigint generated always as identity primary key,
  domain_id bigint not null references domains (id) on delete restrict,
  role text not null,
  contact_id bigint not null references contacts (id) on delete restrict,
  linked_at timestamptz,
  unlinked_at timestamptz,
  created_at timestamptz not null default now(),
  check (role in ('registrant', 'admin', 'tech', 'billing'))
);

create table our_nameservers (
  host_name text primary key,
  created_at timestamptz not null default now(),
  check (host_name ~ '^[a-z0-9]([a-z0-9-]{0,61}[a-z0-9])?(\.[a-z0-9]([a-z0-9-]{0,61}[a-z0-9])?)+$')
);

create table domain_nameservers (
  id bigint generated always as identity primary key,
  domain_id bigint not null references domains (id) on delete restrict,
  host_name text not null,
  sort_order integer not null,
  ipv4 text,
  ipv6 text,
  created_at timestamptz not null default now(),
  check (host_name ~ '^[a-z0-9]([a-z0-9-]{0,61}[a-z0-9])?(\.[a-z0-9]([a-z0-9-]{0,61}[a-z0-9])?)+$')
);

create table dns_zones (
  id bigint generated always as identity primary key,
  zone_name text not null,
  domain_id bigint references domains (id) on delete restrict,
  service_id bigint references services (id) on delete restrict,
  parent_zone_id bigint references dns_zones (id) on delete restrict,
  closed_at timestamptz,
  created_at timestamptz not null default now(),
  check ((domain_id is null) <> (service_id is null)),
  check (zone_name ~ '^[a-z0-9]([a-z0-9-]{0,61}[a-z0-9])?(\.[a-z0-9]([a-z0-9-]{0,61}[a-z0-9])?)+$')
);

create table dns_records (
  id bigint generated always as identity primary key,
  zone_id bigint not null references dns_zones (id) on delete restrict,
  host text not null,
  record_type text not null,
  ttl integer not null,
  priority integer,
  value text not null,
  created_at timestamptz not null default now(),
  check (record_type in ('A', 'AAAA', 'CNAME', 'MX', 'TXT', 'CAA', 'SRV', 'DNAME'))
);

create table zone_publish (
  zone_id bigint primary key references dns_zones (id) on delete restrict,
  mode text not null,
  forward_url text,
  forward_keep_url boolean not null,
  parking_title text,
  parking_note text,
  inquiry_enabled boolean not null,
  created_at timestamptz not null default now(),
  check (mode in ('delegation', 'parking', 'http_forward')),
  check (
    (mode = 'delegation' and forward_url is null and forward_keep_url = false and parking_title is null and parking_note is null and inquiry_enabled = false)
    or (mode = 'parking' and forward_url is null and forward_keep_url = false)
    or (mode = 'http_forward' and forward_url is not null and forward_url ~ '^https?://' and parking_title is null and parking_note is null and inquiry_enabled = false)
  ),
  check (forward_keep_url = false or mode = 'http_forward'),
  check (inquiry_enabled = false or mode = 'parking')
);

create table mail_services (
  service_id bigint primary key references services (id) on delete restrict,
  kind text not null,
  quota_bytes bigint not null,
  zone_id bigint references dns_zones (id) on delete restrict,
  created_at timestamptz not null default now(),
  check (kind = 'mail'),
  foreign key (service_id, kind) references services (id, kind) on delete restrict
);

create table server_services (
  service_id bigint primary key references services (id) on delete restrict,
  kind text not null,
  hostname text not null,
  ipv4 text not null,
  created_at timestamptz not null default now(),
  check (kind = 'server'),
  foreign key (service_id, kind) references services (id, kind) on delete restrict
);

create table dbms_services (
  service_id bigint primary key references services (id) on delete restrict,
  kind text not null,
  host text not null,
  port integer not null,
  db_name text not null,
  created_at timestamptz not null default now(),
  check (kind = 'dbms'),
  foreign key (service_id, kind) references services (id, kind) on delete restrict
);

create table security_services (
  service_id bigint primary key references services (id) on delete restrict,
  kind text not null,
  security_kind text not null,
  created_at timestamptz not null default now(),
  check (kind = 'security'),
  check (security_kind in ('waf', 'vpn', 'nms')),
  foreign key (service_id, kind) references services (id, kind) on delete restrict
);

create table provisioning_jobs (
  id bigint generated always as identity primary key,
  order_item_id bigint not null references order_items (id) on delete restrict,
  idempotency_key text not null unique,
  job_type text not null,
  status text not null,
  attempts integer not null,
  last_error text,
  created_at timestamptz not null default now(),
  check (job_type in ('epp_create', 'epp_renew', 'epp_delete', 'epp_update_contact', 'epp_update_ns', 'epp_update_status', 'epp_transfer_reject', 'epp_authinfo', 'epp_update_ds', 'epp_transfer', 'zone_sync', 'privacy_on', 'privacy_off', 'migrate'))
);

create table registry_events (
  id bigint generated always as identity primary key,
  registry_account_id text not null,
  message_id text not null,
  domain_id bigint references domains (id) on delete restrict,
  event_type text not null,
  payload jsonb not null,
  processed_at timestamptz,
  created_at timestamptz not null default now(),
  unique (registry_account_id, message_id)
);

create table customer_tax_profiles (
  customer_id bigint primary key references customers (id) on delete restrict,
  buyer_kind text not null,
  legal_name text not null,
  business_no text,
  representative_name text,
  address text,
  email text,
  business_type text,
  business_item text,
  evidence_preference text not null,
  cash_receipt_purpose text,
  cash_receipt_identifier text,
  created_at timestamptz not null default now(),
  check (buyer_kind in ('individual', 'business')),
  check (evidence_preference in ('none', 'tax_invoice', 'cash_receipt')),
  check (cash_receipt_purpose is null or cash_receipt_purpose in ('personal', 'business')),
  check (cash_receipt_identifier is null or cash_receipt_identifier ~ '^v1:')
);

create table tax_documents (
  id bigint generated always as identity primary key,
  customer_id bigint not null references customers (id) on delete restrict,
  doc_type text not null,
  status text not null,
  supply_amount bigint not null,
  vat_amount bigint not null,
  amount bigint not null,
  written_on date not null,
  approval_no text,
  buyer_legal_name text not null,
  buyer_business_no text,
  buyer_representative text,
  buyer_address text,
  buyer_email text,
  business_type text,
  business_item text,
  cash_receipt_purpose text,
  cash_receipt_identifier text,
  adjusts_document_id bigint references tax_documents (id) on delete restrict,
  created_at timestamptz not null default now(),
  check (doc_type in ('tax_invoice', 'cash_receipt')),
  check (status in ('issued', 'void')),
  check (cash_receipt_purpose is null or cash_receipt_purpose in ('personal', 'business')),
  check (cash_receipt_identifier is null or cash_receipt_identifier ~ '^v1:')
);

create table tax_document_lines (
  id bigint generated always as identity primary key,
  tax_document_id bigint not null references tax_documents (id) on delete restrict,
  order_item_id bigint not null references order_items (id) on delete restrict,
  supply_amount bigint not null,
  vat_amount bigint not null,
  amount bigint not null,
  created_at timestamptz not null default now()
);

create table payment_notice_mails (
  id bigint generated always as identity primary key,
  customer_id bigint not null references customers (id) on delete restrict,
  service_id bigint not null references services (id) on delete restrict,
  "trigger" text not null,
  anchor_on date not null,
  offset_days integer,
  to_address text not null,
  subject text not null,
  body text not null,
  status text not null,
  sent_at timestamptz,
  last_error text,
  resend_of_id bigint references payment_notice_mails (id) on delete restrict,
  created_at timestamptz not null default now(),
  check ("trigger" in ('auto', 'manual')),
  check (status in ('queued', 'sent', 'failed')),
  check (("trigger" = 'auto' and offset_days in (14, 1)) or ("trigger" = 'manual' and offset_days is null))
);

create table domain_ds_records (
  id bigint generated always as identity primary key,
  domain_id bigint not null references domains (id) on delete restrict,
  key_tag integer not null,
  algorithm integer not null,
  digest_type integer not null,
  digest text not null,
  created_at timestamptz not null default now(),
  check (key_tag between 0 and 65535),
  check (algorithm between 1 and 255),
  check (digest_type = 2),
  check (digest ~ '^[0-9a-f]{64}$')
);

create unique index uq_open_addon
  on services (parent_service_id, product_id)
  where parent_service_id is not null
    and status in ('pending', 'active', 'past_due', 'suspended');

create unique index uq_paid_period
  on order_items (service_id, period_start)
  where action in ('register', 'renew', 'transfer_in')
    and status in ('paid', 'fulfilled')
    and period_start is not null;

create unique index uq_paid_register_name
  on order_items (requested_name)
  where action in ('register', 'transfer_in')
    and status = 'paid';

create unique index uq_live_domain_name
  on domains (domain_name)
  where lifecycle_status not in ('transferred_out', 'deleted');

create unique index uq_current_domain_link
  on domain_services (domain_id)
  where unlinked_at is null;

create unique index uq_open_reservation
  on domain_reservations (requested_name)
  where released_at is null;

create unique index uq_current_domain_contact
  on domain_contacts (domain_id, role)
  where linked_at is not null and unlinked_at is null;

create unique index uq_pending_domain_contact
  on domain_contacts (domain_id, role)
  where linked_at is null;

create unique index uq_domain_nameserver
  on domain_nameservers (domain_id, host_name);

create unique index uq_auto_payment_notice
  on payment_notice_mails (service_id, anchor_on, offset_days)
  where "trigger" = 'auto';

create unique index uq_open_owner_change
  on order_items (from_service_id)
  where action = 'owner_change' and status in ('pending', 'paid');

create unique index uq_zone_domain
  on dns_zones (domain_id) where domain_id is not null;

create unique index uq_zone_service
  on dns_zones (service_id) where service_id is not null;

create unique index uq_open_external_zone_name
  on dns_zones (zone_name)
  where service_id is not null and closed_at is null;

create unique index uq_default_payment_method
  on payment_methods (customer_id)
  where status = 'active' and is_default;

create unique index uq_active_billing_key
  on payment_methods (billing_key_digest)
  where status = 'active';

create unique index uq_customer_login_id
  on customers (login_id);

create unique index uq_customer_email
  on customers (email);

create unique index uq_usage_period
  on service_usage (service_id, meter, period_start);

create unique index uq_usage_line
  on order_items (usage_id)
  where usage_id is not null and status <> 'cancelled';

create function forbid_physical_delete() returns trigger
language plpgsql as $$
begin
  raise exception '% rows are not physically deleted', tg_table_name;
end;
$$;

create trigger products_no_delete
  before delete on products
  for each row execute function forbid_physical_delete();

create trigger orders_no_delete
  before delete on orders
  for each row execute function forbid_physical_delete();

create trigger domains_no_delete
  before delete on domains
  for each row execute function forbid_physical_delete();

create function order_items_freeze_paid() returns trigger
language plpgsql as $$
begin
  if old.status in ('paid', 'fulfilled', 'failed', 'refunded') then
    if new.product_name is distinct from old.product_name
      or new.unit_price is distinct from old.unit_price
      or new.supply_amount is distinct from old.supply_amount
      or new.vat_amount is distinct from old.vat_amount
      or new.cost_amount is distinct from old.cost_amount
      or new.amount is distinct from old.amount
      or new.period_start is distinct from old.period_start
      or new.period_end is distinct from old.period_end
    then
      raise exception 'paid order item money and period are immutable';
    end if;
  end if;
  return new;
end;
$$;

create trigger order_items_freeze_paid_money
  before update on order_items
  for each row execute function order_items_freeze_paid();