CREATE TABLE IF NOT EXISTS schema_migrations (
  version text PRIMARY KEY,
  applied_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE branches (
  id uuid PRIMARY KEY,
  code text NOT NULL UNIQUE,
  name text NOT NULL,
  active boolean NOT NULL DEFAULT true,
  archived_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE warehouses (
  id uuid PRIMARY KEY,
  branch_id uuid NOT NULL REFERENCES branches(id),
  code text NOT NULL UNIQUE,
  name text NOT NULL,
  active boolean NOT NULL DEFAULT true,
  archived_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE users (
  id uuid PRIMARY KEY,
  branch_id uuid REFERENCES branches(id),
  warehouse_id uuid REFERENCES warehouses(id),
  name text NOT NULL,
  username text NOT NULL,
  username_normalized text NOT NULL UNIQUE,
  email text,
  password_hash text NOT NULL,
  force_password_change boolean NOT NULL DEFAULT true,
  active boolean NOT NULL DEFAULT true,
  archived_at timestamptz,
  session_version integer NOT NULL DEFAULT 1,
  last_login_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  CHECK (length(username_normalized) >= 3)
);

CREATE TABLE roles (
  id uuid PRIMARY KEY,
  code text NOT NULL UNIQUE,
  name_fa text NOT NULL,
  is_system boolean NOT NULL DEFAULT false,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE permissions (
  id uuid PRIMARY KEY,
  code text NOT NULL UNIQUE,
  name_fa text NOT NULL,
  module text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE user_roles (
  user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  role_id uuid NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
  PRIMARY KEY (user_id, role_id)
);

CREATE TABLE role_permissions (
  role_id uuid NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
  permission_id uuid NOT NULL REFERENCES permissions(id) ON DELETE CASCADE,
  PRIMARY KEY (role_id, permission_id)
);

CREATE TABLE user_permissions (
  user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  permission_id uuid NOT NULL REFERENCES permissions(id) ON DELETE CASCADE,
  allowed boolean NOT NULL,
  PRIMARY KEY (user_id, permission_id)
);

CREATE TABLE requesters (
  id uuid PRIMARY KEY,
  code text,
  name text NOT NULL,
  normalized_name text NOT NULL,
  active boolean NOT NULL DEFAULT true,
  archived_at timestamptz,
  created_by uuid REFERENCES users(id),
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX requesters_search_idx ON requesters(normalized_name);

CREATE TABLE products (
  id uuid PRIMARY KEY,
  code text NOT NULL,
  normalized_code text NOT NULL UNIQUE,
  name text NOT NULL,
  normalized_name text NOT NULL,
  unit text NOT NULL,
  active boolean NOT NULL DEFAULT true,
  archived_at timestamptz,
  created_by uuid REFERENCES users(id),
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX products_name_search_idx ON products(normalized_name);

CREATE TABLE imports (
  id uuid PRIMARY KEY,
  kind text NOT NULL CHECK (kind IN ('invoice', 'product_catalog')),
  original_filename text NOT NULL,
  stored_path text NOT NULL,
  mime_type text NOT NULL,
  file_size bigint NOT NULL CHECK (file_size >= 0),
  checksum_sha256 text NOT NULL,
  imported_by uuid NOT NULL REFERENCES users(id),
  status text NOT NULL CHECK (status IN ('uploaded', 'validated', 'queued', 'processing', 'completed', 'failed', 'duplicate')),
  progress integer NOT NULL DEFAULT 0 CHECK (progress BETWEEN 0 AND 100),
  started_at timestamptz,
  completed_at timestamptz,
  confirmed_at timestamptz,
  valid_rows integer NOT NULL DEFAULT 0,
  rejected_rows integer NOT NULL DEFAULT 0,
  new_invoices integer NOT NULL DEFAULT 0,
  updated_invoices integer NOT NULL DEFAULT 0,
  unchanged_invoices integer NOT NULL DEFAULT 0,
  changed_products integer NOT NULL DEFAULT 0,
  discrepancies integer NOT NULL DEFAULT 0,
  outcome jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (kind, checksum_sha256)
);

CREATE TABLE import_row_errors (
  id uuid PRIMARY KEY,
  import_id uuid NOT NULL REFERENCES imports(id) ON DELETE CASCADE,
  sheet_name text NOT NULL,
  row_number integer NOT NULL CHECK (row_number > 0),
  raw_values jsonb NOT NULL,
  reasons jsonb NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX import_errors_import_idx ON import_row_errors(import_id, row_number);

CREATE TABLE invoices (
  id uuid PRIMARY KEY,
  invoice_number text NOT NULL,
  normalized_number text NOT NULL UNIQUE,
  origin text NOT NULL CHECK (origin IN ('manual', 'financial', 'mixed')),
  customer_code text,
  customer_name text,
  sale_type text,
  financial_date text,
  manual_current_version_id uuid,
  financial_current_version_id uuid,
  operational_version_id uuid,
  has_discrepancy boolean NOT NULL DEFAULT false,
  manager_edited boolean NOT NULL DEFAULT false,
  archived_at timestamptz,
  archive_reason text,
  created_by uuid REFERENCES users(id),
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX invoices_number_search_idx ON invoices(normalized_number);
CREATE INDEX invoices_active_idx ON invoices(archived_at, updated_at DESC);
CREATE INDEX invoices_discrepancy_idx ON invoices(has_discrepancy) WHERE has_discrepancy = true;

CREATE TABLE invoice_versions (
  id uuid PRIMARY KEY,
  invoice_id uuid NOT NULL REFERENCES invoices(id),
  import_id uuid REFERENCES imports(id),
  version_number integer NOT NULL CHECK (version_number > 0),
  source text NOT NULL CHECK (source IN ('manual', 'financial', 'resolved')),
  identity_data jsonb NOT NULL,
  fingerprint_sha256 text NOT NULL,
  created_by uuid REFERENCES users(id),
  created_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE(invoice_id, version_number),
  UNIQUE(invoice_id, source, fingerprint_sha256)
);

ALTER TABLE invoices
  ADD CONSTRAINT invoices_manual_version_fk FOREIGN KEY (manual_current_version_id) REFERENCES invoice_versions(id),
  ADD CONSTRAINT invoices_financial_version_fk FOREIGN KEY (financial_current_version_id) REFERENCES invoice_versions(id),
  ADD CONSTRAINT invoices_operational_version_fk FOREIGN KEY (operational_version_id) REFERENCES invoice_versions(id);

CREATE TABLE invoice_version_items (
  id uuid PRIMARY KEY,
  invoice_version_id uuid NOT NULL REFERENCES invoice_versions(id),
  line_number integer NOT NULL CHECK (line_number > 0),
  product_id uuid REFERENCES products(id),
  product_code text NOT NULL,
  product_name text NOT NULL,
  unit text NOT NULL,
  quantity numeric(20,4) NOT NULL CHECK (quantity > 0),
  original_data jsonb NOT NULL DEFAULT '{}'::jsonb,
  UNIQUE(invoice_version_id, line_number)
);
CREATE INDEX invoice_version_items_product_idx ON invoice_version_items(product_id);

CREATE TABLE financial_changes (
  id uuid PRIMARY KEY,
  invoice_id uuid NOT NULL REFERENCES invoices(id),
  from_version_id uuid REFERENCES invoice_versions(id),
  to_version_id uuid NOT NULL REFERENCES invoice_versions(id),
  import_id uuid NOT NULL REFERENCES imports(id),
  changes jsonb NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX financial_changes_time_idx ON financial_changes(created_at DESC);

CREATE TABLE discrepancies (
  id uuid PRIMARY KEY,
  invoice_id uuid NOT NULL REFERENCES invoices(id),
  manual_version_id uuid REFERENCES invoice_versions(id),
  financial_version_id uuid REFERENCES invoice_versions(id),
  kind text NOT NULL CHECK (kind IN ('manual_financial', 'operational_financial')),
  status text NOT NULL CHECK (status IN ('open', 'resolved')) DEFAULT 'open',
  differences jsonb NOT NULL,
  resolution jsonb,
  resolution_reason text,
  resolved_by uuid REFERENCES users(id),
  resolved_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX discrepancies_open_idx ON discrepancies(status, created_at DESC);

CREATE TABLE delivery_requests (
  id uuid PRIMARY KEY,
  invoice_id uuid NOT NULL REFERENCES invoices(id),
  requester_id uuid NOT NULL REFERENCES requesters(id),
  warehouse_id uuid NOT NULL REFERENCES warehouses(id),
  delivery_type text NOT NULL CHECK (delivery_type IN ('complete', 'partial')),
  whole_invoice_no_lines boolean NOT NULL DEFAULT false,
  status text NOT NULL CHECK (status IN ('pending', 'delivered', 'cancelled', 'returned')) DEFAULT 'pending',
  notes text,
  approved_by uuid NOT NULL REFERENCES users(id),
  approved_at timestamptz NOT NULL DEFAULT now(),
  state_version integer NOT NULL DEFAULT 1,
  created_at timestamptz NOT NULL DEFAULT now(),
  CHECK (NOT (delivery_type = 'partial' AND whole_invoice_no_lines))
);
CREATE INDEX delivery_queue_idx ON delivery_requests(warehouse_id, status, approved_at DESC);
CREATE INDEX delivery_invoice_idx ON delivery_requests(invoice_id, approved_at DESC);

CREATE TABLE delivery_request_items (
  id uuid PRIMARY KEY,
  delivery_request_id uuid NOT NULL REFERENCES delivery_requests(id),
  product_id uuid REFERENCES products(id),
  product_code text NOT NULL,
  product_name text NOT NULL,
  unit text NOT NULL,
  approved_quantity numeric(20,4) NOT NULL CHECK (approved_quantity > 0),
  UNIQUE(delivery_request_id, product_code)
);

CREATE TABLE delivery_events (
  id uuid PRIMARY KEY,
  delivery_request_id uuid NOT NULL REFERENCES delivery_requests(id),
  sequence integer NOT NULL CHECK (sequence > 0),
  event_type text NOT NULL CHECK (event_type IN ('approved', 'delivered', 'cancelled', 'returned', 'redelivered')),
  actor_id uuid NOT NULL REFERENCES users(id),
  reason text,
  idempotency_key text NOT NULL,
  occurred_at timestamptz NOT NULL DEFAULT now(),
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
  UNIQUE(delivery_request_id, sequence),
  UNIQUE(actor_id, idempotency_key),
  CHECK ((event_type IN ('cancelled', 'returned') AND length(trim(reason)) >= 3) OR event_type NOT IN ('cancelled', 'returned'))
);
CREATE INDEX delivery_events_time_idx ON delivery_events(occurred_at DESC);

CREATE TABLE notifications (
  id uuid PRIMARY KEY,
  user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  kind text NOT NULL,
  title text NOT NULL,
  body text NOT NULL,
  data jsonb NOT NULL DEFAULT '{}'::jsonb,
  read_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX notifications_unread_idx ON notifications(user_id, read_at, created_at DESC);

CREATE TABLE web_push_subscriptions (
  id uuid PRIMARY KEY,
  user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  endpoint text NOT NULL UNIQUE,
  p256dh text NOT NULL,
  auth text NOT NULL,
  user_agent text,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE audit_events (
  id uuid PRIMARY KEY,
  actor_id uuid REFERENCES users(id),
  actor_role text,
  action text NOT NULL,
  entity_type text NOT NULL,
  entity_id text NOT NULL,
  old_values jsonb,
  new_values jsonb,
  reason text,
  ip_address inet,
  user_agent text,
  import_id uuid REFERENCES imports(id),
  delivery_event_id uuid REFERENCES delivery_events(id),
  occurred_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX audit_entity_idx ON audit_events(entity_type, entity_id, occurred_at DESC);
CREATE INDEX audit_time_idx ON audit_events(occurred_at DESC);

CREATE TABLE jobs (
  id uuid PRIMARY KEY,
  queue text NOT NULL,
  type text NOT NULL,
  payload jsonb NOT NULL,
  status text NOT NULL CHECK (status IN ('queued', 'processing', 'completed', 'failed')) DEFAULT 'queued',
  attempts integer NOT NULL DEFAULT 0,
  max_attempts integer NOT NULL DEFAULT 3,
  available_at timestamptz NOT NULL DEFAULT now(),
  locked_at timestamptz,
  locked_by text,
  last_error text,
  created_at timestamptz NOT NULL DEFAULT now(),
  completed_at timestamptz
);
CREATE INDEX jobs_poll_idx ON jobs(queue, status, available_at);

CREATE TABLE system_settings (
  key text PRIMARY KEY,
  value jsonb NOT NULL,
  is_secret boolean NOT NULL DEFAULT false,
  updated_by uuid REFERENCES users(id),
  updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE OR REPLACE FUNCTION prevent_immutable_change() RETURNS trigger AS $$
BEGIN
  RAISE EXCEPTION 'immutable_record';
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER audit_events_immutable BEFORE UPDATE OR DELETE ON audit_events
  FOR EACH ROW EXECUTE FUNCTION prevent_immutable_change();
CREATE TRIGGER invoice_versions_immutable BEFORE UPDATE OR DELETE ON invoice_versions
  FOR EACH ROW EXECUTE FUNCTION prevent_immutable_change();
CREATE TRIGGER invoice_version_items_immutable BEFORE UPDATE OR DELETE ON invoice_version_items
  FOR EACH ROW EXECUTE FUNCTION prevent_immutable_change();
CREATE TRIGGER delivery_events_immutable BEFORE UPDATE OR DELETE ON delivery_events
  FOR EACH ROW EXECUTE FUNCTION prevent_immutable_change();
CREATE TRIGGER delivery_request_items_immutable BEFORE UPDATE OR DELETE ON delivery_request_items
  FOR EACH ROW EXECUTE FUNCTION prevent_immutable_change();

