-- Table licences
CREATE TABLE IF NOT EXISTS licences (
  id            uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  key_hash      text UNIQUE NOT NULL,
  email         text NOT NULL,
  plan          text NOT NULL CHECK (plan IN ('starter', 'pro', 'unlimited')),
  quota_month   integer NOT NULL DEFAULT 50,
  usage_month   integer NOT NULL DEFAULT 0,
  reset_at      timestamptz NOT NULL,
  active        boolean NOT NULL DEFAULT true,
  allowed_ip    inet,
  flagged       boolean NOT NULL DEFAULT false,
  created_at    timestamptz NOT NULL DEFAULT now()
);

-- Table usage_logs
CREATE TABLE IF NOT EXISTS usage_logs (
  id            uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  licence_id    uuid NOT NULL REFERENCES licences(id) ON DELETE CASCADE,
  key_hash      text NOT NULL,
  nb_photos     integer NOT NULL DEFAULT 0,
  nb_lignes     integer NOT NULL DEFAULT 0,
  latency_ms    integer NOT NULL DEFAULT 0,
  created_at    timestamptz NOT NULL DEFAULT now()
);

-- Function pour incrémenter usage
CREATE OR REPLACE FUNCTION increment_usage(licence_id uuid)
RETURNS void AS $$
  UPDATE licences
  SET usage_month = usage_month + 1
  WHERE id = licence_id;
$$ LANGUAGE SQL;

-- Index
CREATE INDEX IF NOT EXISTS idx_licences_key_hash ON licences(key_hash);
CREATE INDEX IF NOT EXISTS idx_usage_logs_licence_created ON usage_logs(licence_id, created_at);
