create extension if not exists "uuid-ossp";

create table clients (
  id uuid primary key default uuid_generate_v4(),
  name varchar(180) not null,
  legal_name varchar(220),
  tenant_key varchar(120) not null unique,
  environment varchar(20) not null default 'sandbox',
  openpay_merchant_id varchar(180),
  openpay_private_key_encrypted text,
  openpay_public_key_encrypted text,
  webhook_secret_encrypted text,
  callback_url text,
  callback_secret_encrypted text,
  active boolean not null default true,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table api_tokens (
  id uuid primary key default uuid_generate_v4(),
  client_id uuid not null references clients(id),
  name varchar(180) not null,
  token_hash text not null,
  scopes text[] not null default array['references:write','references:read'],
  rate_limit_per_minute integer not null default 120,
  active boolean not null default true,
  last_used_at timestamptz,
  expires_at timestamptz,
  created_at timestamptz not null default now()
);

create table payment_references (
  id uuid primary key default uuid_generate_v4(),
  client_id uuid not null references clients(id),
  external_reference varchar(120) not null,
  idempotency_key varchar(120) not null,
  contract_number varchar(120) not null,
  customer_name varchar(180) not null,
  customer_email varchar(180),
  customer_phone varchar(30),
  amount numeric(12, 2) not null check (amount > 0),
  currency char(3) not null default 'MXN',
  description varchar(250) not null,
  period varchar(80) not null,
  due_date timestamptz not null,
  status varchar(40) not null default 'pending',
  openpay_charge_id varchar(180),
  openpay_authorization varchar(180),
  paynet_reference varchar(180),
  barcode_url text,
  barcode_number varchar(180),
  payment_method varchar(40) not null default 'store',
  raw_request jsonb,
  raw_openpay_response jsonb,
  raw_openpay_error jsonb,
  metadata jsonb,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  paid_at timestamptz,
  unique(client_id, external_reference),
  unique(client_id, idempotency_key),
  unique(openpay_charge_id)
);

create index idx_payment_references_client_status on payment_references(client_id, status);
create index idx_payment_references_due_date on payment_references(due_date);
create index idx_payment_references_paynet_reference on payment_references(paynet_reference);

create table payment_batches (
  id uuid primary key default uuid_generate_v4(),
  client_id uuid not null references clients(id),
  batch_external_id varchar(120),
  idempotency_key varchar(120) not null,
  source_type varchar(20) not null check (source_type in ('json','csv','xlsx')),
  file_name varchar(260),
  status varchar(40) not null default 'pending',
  total_items integer not null default 0,
  processed_items integer not null default 0,
  failed_items integer not null default 0,
  raw_request jsonb,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique(client_id, idempotency_key)
);

create table payment_batch_items (
  id uuid primary key default uuid_generate_v4(),
  batch_id uuid not null references payment_batches(id) on delete cascade,
  payment_reference_id uuid references payment_references(id),
  line_number integer not null,
  external_reference varchar(120),
  status varchar(40) not null default 'pending',
  error_code varchar(120),
  error_message text,
  raw_item jsonb,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table webhook_events (
  id uuid primary key default uuid_generate_v4(),
  client_id uuid references clients(id),
  payment_reference_id uuid references payment_references(id),
  provider varchar(40) not null default 'openpay',
  provider_event_id varchar(180),
  event_type varchar(80) not null,
  status varchar(40) not null default 'webhook_received',
  headers jsonb,
  payload jsonb not null,
  signature_valid boolean not null default false,
  source_ip inet,
  processed_at timestamptz,
  created_at timestamptz not null default now()
);

create table callback_events (
  id uuid primary key default uuid_generate_v4(),
  client_id uuid not null references clients(id),
  payment_reference_id uuid references payment_references(id),
  event_type varchar(80) not null,
  callback_url text not null,
  request_payload jsonb not null,
  request_signature varchar(180),
  response_status integer,
  response_body text,
  status varchar(40) not null default 'pending',
  attempt_count integer not null default 0,
  next_retry_at timestamptz,
  sent_at timestamptz,
  created_at timestamptz not null default now()
);

create table audit_logs (
  id uuid primary key default uuid_generate_v4(),
  client_id uuid references clients(id),
  actor_type varchar(40) not null,
  actor_id varchar(180),
  action varchar(120) not null,
  resource_type varchar(80) not null,
  resource_id varchar(180),
  source_ip inet,
  user_agent text,
  request_id varchar(120),
  before_state jsonb,
  after_state jsonb,
  metadata jsonb,
  created_at timestamptz not null default now()
);

create table reconciliation_jobs (
  id uuid primary key default uuid_generate_v4(),
  client_id uuid not null references clients(id),
  job_type varchar(40) not null,
  status varchar(40) not null default 'pending',
  total_items integer not null default 0,
  processed_items integer not null default 0,
  differences_found integer not null default 0,
  errors_found integer not null default 0,
  started_at timestamptz,
  finished_at timestamptz,
  created_at timestamptz not null default now()
);

create table reconciliation_items (
  id uuid primary key default uuid_generate_v4(),
  reconciliation_job_id uuid not null references reconciliation_jobs(id) on delete cascade,
  payment_reference_id uuid references payment_references(id),
  external_reference varchar(120),
  internal_status varchar(40),
  openpay_status varchar(80),
  amount_matches boolean,
  status_matches boolean,
  updated boolean not null default false,
  error_message text,
  raw_openpay_response jsonb,
  created_at timestamptz not null default now()
);

create table event_logs (
  id uuid primary key default uuid_generate_v4(),
  client_id uuid references clients(id),
  payment_reference_id uuid references payment_references(id),
  batch_id uuid references payment_batches(id),
  event_type varchar(80) not null,
  status varchar(40),
  payload jsonb,
  created_at timestamptz not null default now()
);

create table callback_retries (
  id uuid primary key default uuid_generate_v4(),
  callback_event_id uuid not null references callback_events(id) on delete cascade,
  attempt_number integer not null,
  response_status integer,
  response_body text,
  error_message text,
  attempted_at timestamptz not null default now()
);

create table payment_status_history (
  id uuid primary key default uuid_generate_v4(),
  payment_reference_id uuid not null references payment_references(id) on delete cascade,
  previous_status varchar(40),
  new_status varchar(40) not null,
  reason varchar(120),
  source varchar(40) not null,
  source_event_id uuid,
  created_at timestamptz not null default now()
);

create table openpay_events (
  id uuid primary key default uuid_generate_v4(),
  client_id uuid references clients(id),
  payment_reference_id uuid references payment_references(id),
  openpay_event_id varchar(180),
  openpay_charge_id varchar(180),
  event_type varchar(80),
  payload jsonb not null,
  received_at timestamptz not null default now()
);
