-- Create tags table
CREATE TABLE IF NOT EXISTS tags (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL UNIQUE,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_tags_name ON tags(name);

-- Create member_tags junction table
CREATE TABLE IF NOT EXISTS member_tags (
  member_id INTEGER NOT NULL,
  tag_id INTEGER NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (member_id, tag_id),
  FOREIGN KEY (member_id) REFERENCES mobile_users(id) ON DELETE CASCADE,
  FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_member_tags_member_id ON member_tags(member_id);
CREATE INDEX IF NOT EXISTS idx_member_tags_tag_id ON member_tags(tag_id);

-- Migrate existing tags from metadata to tags table
-- This will extract tags from metadata and create tag entries
INSERT INTO tags (name)
SELECT DISTINCT json_each.value
FROM mobile_users,
     json_each(mobile_users.metadata, '$.tags')
WHERE mobile_users.metadata IS NOT NULL
  AND json_extract(mobile_users.metadata, '$.tags') IS NOT NULL
  AND json_each.value IS NOT NULL
  AND json_each.value != ''
ON CONFLICT(name) DO NOTHING;

-- Link existing members to tags
INSERT INTO member_tags (member_id, tag_id)
SELECT DISTINCT mu.id, t.id
FROM mobile_users mu,
     json_each(mu.metadata, '$.tags') je,
     tags t
WHERE mu.metadata IS NOT NULL
  AND json_extract(mu.metadata, '$.tags') IS NOT NULL
  AND je.value = t.name
ON CONFLICT(member_id, tag_id) DO NOTHING;