-- Wireframe Storage Database Schema
-- Extends the user authentication schema to store wireframe data

-- Drop tables if they exist (for development)
DROP TABLE IF EXISTS wireframe_versions CASCADE;
DROP TABLE IF EXISTS wireframe_elements CASCADE;
DROP TABLE IF EXISTS wireframes CASCADE;

-- Wireframes table - Main wireframe storage
CREATE TABLE wireframes (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID REFERENCES users(id) ON DELETE CASCADE,
  project_id UUID REFERENCES user_projects(id) ON DELETE SET NULL,
  name VARCHAR(200) NOT NULL,
  description TEXT,
  device_type VARCHAR(20) DEFAULT 'desktop' CHECK (device_type IN ('mobile', 'tablet', 'desktop')),
  dimensions JSONB NOT NULL, -- {width: number, height: number}
  metadata JSONB, -- Additional metadata like prompt, generation settings
  is_active BOOLEAN DEFAULT true,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

-- Wireframe elements table - Store individual elements/shapes
CREATE TABLE wireframe_elements (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  wireframe_id UUID REFERENCES wireframes(id) ON DELETE CASCADE,
  element_type VARCHAR(50) NOT NULL, -- 'shape', 'text', 'image', 'group'
  element_data JSONB NOT NULL, -- TLDraw element data
  position JSONB NOT NULL, -- {x: number, y: number}
  size JSONB, -- {width: number, height: number}
  style JSONB, -- {color, strokeWidth, etc.}
  parent_id VARCHAR(255), -- TLDraw uses string IDs like "page:page", not UUIDs
  tldraw_id VARCHAR(255), -- Store the original TLDraw ID
  z_index INTEGER DEFAULT 0,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

-- Wireframe versions table - Version control for wireframes
CREATE TABLE wireframe_versions (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  wireframe_id UUID REFERENCES wireframes(id) ON DELETE CASCADE,
  version_number INTEGER NOT NULL,
  version_name VARCHAR(100),
  version_description TEXT,
  snapshot_data JSONB NOT NULL, -- Complete wireframe state at this version
  created_by UUID REFERENCES users(id) ON DELETE SET NULL,
  created_at TIMESTAMP DEFAULT NOW(),
  UNIQUE(wireframe_id, version_number)
);

-- Indexes for performance
CREATE INDEX idx_wireframes_user_id ON wireframes(user_id);
CREATE INDEX idx_wireframes_project_id ON wireframes(project_id);
CREATE INDEX idx_wireframes_active ON wireframes(is_active);
CREATE INDEX idx_wireframes_device_type ON wireframes(device_type);
CREATE INDEX idx_wireframe_elements_wireframe_id ON wireframe_elements(wireframe_id);
CREATE INDEX idx_wireframe_elements_parent_id ON wireframe_elements(parent_id);
CREATE INDEX idx_wireframe_elements_type ON wireframe_elements(element_type);
CREATE INDEX idx_wireframe_versions_wireframe_id ON wireframe_versions(wireframe_id);
CREATE INDEX idx_wireframe_versions_number ON wireframe_versions(version_number);

-- Apply triggers for updated_at columns
CREATE TRIGGER update_wireframes_updated_at 
  BEFORE UPDATE ON wireframes
  FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_wireframe_elements_updated_at 
  BEFORE UPDATE ON wireframe_elements
  FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- Functions for wireframe management
CREATE OR REPLACE FUNCTION create_wireframe_version(
  p_wireframe_id UUID,
  p_version_name VARCHAR(100),
  p_version_description TEXT,
  p_snapshot_data JSONB,
  p_created_by UUID
)
RETURNS UUID AS $$
DECLARE
  next_version INTEGER;
  new_version_id UUID;
BEGIN
  -- Get next version number
  SELECT COALESCE(MAX(version_number), 0) + 1
  INTO next_version
  FROM wireframe_versions
  WHERE wireframe_id = p_wireframe_id;
  
  -- Create new version
  INSERT INTO wireframe_versions (
    wireframe_id, version_number, version_name, 
    version_description, snapshot_data, created_by
  ) VALUES (
    p_wireframe_id, next_version, p_version_name,
    p_version_description, p_snapshot_data, p_created_by
  ) RETURNING id INTO new_version_id;
  
  RETURN new_version_id;
END;
$$ LANGUAGE plpgsql;

-- Function to get wireframe with all elements
CREATE OR REPLACE FUNCTION get_wireframe_with_elements(p_wireframe_id UUID)
RETURNS TABLE(
  wireframe_data JSONB,
  elements_data JSONB
) AS $$
BEGIN
  RETURN QUERY
  SELECT 
    to_jsonb(w.*) as wireframe_data,
    COALESCE(
      jsonb_agg(
        jsonb_build_object(
          'id', we.id,
          'tldraw_id', we.tldraw_id,
          'type', we.element_type,
          'data', we.element_data,
          'position', we.position,
          'size', we.size,
          'style', we.style,
          'parent_id', we.parent_id,
          'z_index', we.z_index
        ) ORDER BY we.z_index, we.created_at
      ) FILTER (WHERE we.id IS NOT NULL),
      '[]'::jsonb
    ) as elements_data
  FROM wireframes w
  LEFT JOIN wireframe_elements we ON w.id = we.wireframe_id
  WHERE w.id = p_wireframe_id
  GROUP BY w.id, w.user_id, w.project_id, w.name, w.description, 
           w.device_type, w.dimensions, w.metadata, w.is_active, 
           w.created_at, w.updated_at;
END;
$$ LANGUAGE plpgsql;

-- Insert sample wireframe for testing
INSERT INTO wireframes (
  id, user_id, name, description, device_type, dimensions, metadata
) VALUES (
  uuid_generate_v4(),
  (SELECT id FROM users WHERE username = 'testuser' LIMIT 1),
  'Sample Wireframe',
  'A sample wireframe for testing',
  'desktop',
  '{"width": 1440, "height": 1024}'::jsonb,
  '{"prompt": "Sample prompt", "generator": "ai"}'::jsonb
) ON CONFLICT DO NOTHING;

-- Success message
SELECT 'Wireframe database schema created successfully!' as message;

-- Display created tables
SELECT 
  schemaname,
  tablename,
  tableowner
FROM pg_tables 
WHERE schemaname = 'public' 
AND tablename IN ('wireframes', 'wireframe_elements', 'wireframe_versions')
ORDER BY tablename;
