-- ============================================================================
-- LICENSE KEYS TABLE — run this once on the cPanel PostgreSQL database to
-- enable the POST /api/license/validate endpoint.
--
-- Apply via psql:
--   psql -h <cpanel-host> -U <cpanel-user> -d <cpanel-db> -f license_keys_setup.sql
-- ============================================================================

CREATE TABLE IF NOT EXISTS license_keys (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  license_key text UNIQUE NOT NULL,
  customer_name text,
  customer_email text,
  notes text,
  active boolean NOT NULL DEFAULT true,
  expires_at timestamptz,
  issued_at timestamptz NOT NULL DEFAULT now(),
  last_validated_at timestamptz,
  validation_count integer NOT NULL DEFAULT 0,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_license_keys_key ON license_keys(license_key);
CREATE INDEX IF NOT EXISTS idx_license_keys_active ON license_keys(active) WHERE active = true;

-- Auto-update updated_at
CREATE OR REPLACE FUNCTION touch_license_keys_updated_at()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trg_license_keys_updated_at ON license_keys;
CREATE TRIGGER trg_license_keys_updated_at
  BEFORE UPDATE ON license_keys
  FOR EACH ROW EXECUTE FUNCTION touch_license_keys_updated_at();

-- Sample insert (replace with real key + expiry):
INSERT INTO license_keys (license_key, customer_name, expires_at)
VALUES ('ATAPOLY-2026-TEST-TING', 'ATAPOLY', '2027-01-01T00:00:00Z');