-- Create job_locks table to prevent duplicate job execution
CREATE TABLE IF NOT EXISTS job_locks (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  job_name TEXT NOT NULL UNIQUE,
  lock_id TEXT NOT NULL,
  acquired_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  expires_at TIMESTAMPTZ NOT NULL,
  status TEXT NOT NULL DEFAULT 'acquired',
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Create index for faster lookups
CREATE INDEX IF NOT EXISTS idx_job_locks_job_name ON job_locks(job_name);
CREATE INDEX IF NOT EXISTS idx_job_locks_expires_at ON job_locks(expires_at);

-- Add RLS policies
ALTER TABLE job_locks ENABLE ROW LEVEL SECURITY;

-- Only service role can manage job locks
CREATE POLICY "Service role full access" ON job_locks
  FOR ALL USING (auth.role() = 'service_role');

-- Function to update updated_at timestamp
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ language 'plpgsql';

-- Trigger to auto-update updated_at
CREATE TRIGGER update_job_locks_updated_at BEFORE UPDATE
  ON job_locks FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- Insert initial cleanup job lock (optional)
-- INSERT INTO job_locks (job_name, lock_id, expires_at) 
-- VALUES ('lock_cleanup', 'initial', NOW() + INTERVAL '1 year')
-- ON CONFLICT (job_name) DO NOTHING;