CREATE TABLE IF NOT EXISTS users (
    id UUID PRIMARY KEY,
    email TEXT NOT NULL DEFAULT '',
    name TEXT NOT NULL DEFAULT '',
    status TEXT NOT NULL DEFAULT 'active',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

INSERT INTO users (id, email, name, status)
VALUES ('00000000-0000-0000-0000-000000000001', 'local@example.test', 'Local User', 'active')
ON CONFLICT (id) DO NOTHING;
ALTER TABLE users ALTER COLUMN id TYPE UUID USING NULLIF(id::text, '')::uuid;
ALTER TABLE users ADD COLUMN IF NOT EXISTS region TEXT NOT NULL DEFAULT 'china';
ALTER TABLE users ADD COLUMN IF NOT EXISTS phone_country_code TEXT NOT NULL DEFAULT '';
ALTER TABLE users ADD COLUMN IF NOT EXISTS phone TEXT NOT NULL DEFAULT '';
ALTER TABLE users ADD COLUMN IF NOT EXISTS avatar_url TEXT NOT NULL DEFAULT '';
ALTER TABLE users ADD COLUMN IF NOT EXISTS password_hash TEXT NOT NULL DEFAULT '';
ALTER TABLE users ADD COLUMN IF NOT EXISTS wechat_open_id TEXT NOT NULL DEFAULT '';
ALTER TABLE users ADD COLUMN IF NOT EXISTS wechat_union_id TEXT NOT NULL DEFAULT '';
ALTER TABLE users ADD COLUMN IF NOT EXISTS google_subject TEXT NOT NULL DEFAULT '';
ALTER TABLE users ADD COLUMN IF NOT EXISTS phone_verified_at TIMESTAMPTZ;
ALTER TABLE users ADD COLUMN IF NOT EXISTS email_verified_at TIMESTAMPTZ;
ALTER TABLE users ADD COLUMN IF NOT EXISTS last_login_at TIMESTAMPTZ;
CREATE UNIQUE INDEX IF NOT EXISTS users_phone_unique_idx ON users(phone_country_code, phone) WHERE phone <> '';
CREATE UNIQUE INDEX IF NOT EXISTS users_email_unique_idx ON users(LOWER(email)) WHERE email <> '';
CREATE UNIQUE INDEX IF NOT EXISTS users_google_subject_unique_idx ON users(google_subject) WHERE google_subject <> '';
CREATE UNIQUE INDEX IF NOT EXISTS users_wechat_union_id_unique_idx ON users(wechat_union_id) WHERE wechat_union_id <> '';
CREATE UNIQUE INDEX IF NOT EXISTS users_wechat_open_id_unique_idx ON users(wechat_open_id) WHERE wechat_open_id <> '' AND wechat_union_id = '';

CREATE TABLE IF NOT EXISTS auth_verification_codes (
    id TEXT PRIMARY KEY,
    region TEXT NOT NULL,
    channel TEXT NOT NULL,
    target TEXT NOT NULL,
    purpose TEXT NOT NULL,
    code_hash TEXT NOT NULL,
    attempts INTEGER NOT NULL DEFAULT 0,
    expires_at TIMESTAMPTZ NOT NULL,
    consumed_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS auth_codes_target_idx ON auth_verification_codes(region, channel, target, purpose, created_at DESC);

CREATE TABLE IF NOT EXISTS projects (
    id TEXT PRIMARY KEY,
    user_id UUID NOT NULL DEFAULT '00000000-0000-0000-0000-000000000001'::uuid,
    title TEXT NOT NULL,
    brief TEXT NOT NULL,
    status TEXT NOT NULL,
    thumbnail TEXT NOT NULL,
    canvas TEXT NOT NULL DEFAULT '',
    version TEXT NOT NULL DEFAULT '',
    snapshot_id TEXT NOT NULL DEFAULT '',
    last_thread_id TEXT NOT NULL DEFAULT '',
    viewport_x DOUBLE PRECISION NOT NULL DEFAULT 0,
    viewport_y DOUBLE PRECISION NOT NULL DEFAULT 0,
    viewport_k DOUBLE PRECISION NOT NULL DEFAULT 1,
    updated_at TIMESTAMPTZ NOT NULL
);

ALTER TABLE projects ADD COLUMN IF NOT EXISTS user_id UUID;
ALTER TABLE projects ALTER COLUMN user_id DROP DEFAULT;
ALTER TABLE projects ALTER COLUMN user_id TYPE UUID USING NULLIF(user_id::text, '')::uuid;
UPDATE projects SET user_id = '00000000-0000-0000-0000-000000000001'::uuid WHERE user_id IS NULL;
ALTER TABLE projects ALTER COLUMN user_id SET DEFAULT '00000000-0000-0000-0000-000000000001'::uuid;
ALTER TABLE projects ALTER COLUMN user_id SET NOT NULL;
ALTER TABLE projects ADD COLUMN IF NOT EXISTS canvas TEXT NOT NULL DEFAULT '';
ALTER TABLE projects ADD COLUMN IF NOT EXISTS version TEXT NOT NULL DEFAULT '';
ALTER TABLE projects ADD COLUMN IF NOT EXISTS snapshot_id TEXT NOT NULL DEFAULT '';
ALTER TABLE projects ADD COLUMN IF NOT EXISTS last_thread_id TEXT NOT NULL DEFAULT '';
CREATE INDEX IF NOT EXISTS projects_user_updated_idx ON projects(user_id, updated_at DESC);

CREATE TABLE IF NOT EXISTS canvas_nodes (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    user_id UUID NOT NULL DEFAULT '00000000-0000-0000-0000-000000000001'::uuid,
    node_type TEXT NOT NULL,
    title TEXT NOT NULL,
    x DOUBLE PRECISION NOT NULL,
    y DOUBLE PRECISION NOT NULL,
    width DOUBLE PRECISION NOT NULL,
    height DOUBLE PRECISION NOT NULL,
    content TEXT NOT NULL,
    tone TEXT NOT NULL,
    status TEXT NOT NULL,
    sort_order INTEGER NOT NULL DEFAULT 0,
    parent_id TEXT NOT NULL DEFAULT '',
    layer_role TEXT NOT NULL DEFAULT '',
    font_size DOUBLE PRECISION NOT NULL DEFAULT 0,
    font_family TEXT NOT NULL DEFAULT '',
    font_weight TEXT NOT NULL DEFAULT '',
    color TEXT NOT NULL DEFAULT '',
    text_align TEXT NOT NULL DEFAULT '',
    line_height DOUBLE PRECISION NOT NULL DEFAULT 0,
    letter_spacing DOUBLE PRECISION NOT NULL DEFAULT 0,
    text_decoration TEXT NOT NULL DEFAULT '',
    text_transform TEXT NOT NULL DEFAULT '',
    opacity DOUBLE PRECISION NOT NULL DEFAULT 0,
    fill_color TEXT NOT NULL DEFAULT '',
    stroke_color TEXT NOT NULL DEFAULT '',
    stroke_width DOUBLE PRECISION NOT NULL DEFAULT 0,
    stroke_style TEXT NOT NULL DEFAULT '',
    flip_x BOOLEAN NOT NULL DEFAULT FALSE,
    flip_y BOOLEAN NOT NULL DEFAULT FALSE,
    rotation DOUBLE PRECISION NOT NULL DEFAULT 0
);

ALTER TABLE canvas_nodes ADD COLUMN IF NOT EXISTS user_id UUID;
ALTER TABLE canvas_nodes ALTER COLUMN user_id DROP DEFAULT;
ALTER TABLE canvas_nodes ALTER COLUMN user_id TYPE UUID USING NULLIF(user_id::text, '')::uuid;
UPDATE canvas_nodes
SET user_id = COALESCE((SELECT p.user_id FROM projects p WHERE p.id = canvas_nodes.project_id), '00000000-0000-0000-0000-000000000001'::uuid)
WHERE user_id IS NULL;
ALTER TABLE canvas_nodes ALTER COLUMN user_id SET DEFAULT '00000000-0000-0000-0000-000000000001'::uuid;
ALTER TABLE canvas_nodes ALTER COLUMN user_id SET NOT NULL;
ALTER TABLE canvas_nodes ADD COLUMN IF NOT EXISTS sort_order INTEGER NOT NULL DEFAULT 0;
ALTER TABLE canvas_nodes ADD COLUMN IF NOT EXISTS parent_id TEXT NOT NULL DEFAULT '';
ALTER TABLE canvas_nodes ADD COLUMN IF NOT EXISTS layer_role TEXT NOT NULL DEFAULT '';
ALTER TABLE canvas_nodes ADD COLUMN IF NOT EXISTS font_size DOUBLE PRECISION NOT NULL DEFAULT 0;
ALTER TABLE canvas_nodes ADD COLUMN IF NOT EXISTS font_family TEXT NOT NULL DEFAULT '';
ALTER TABLE canvas_nodes ADD COLUMN IF NOT EXISTS font_weight TEXT NOT NULL DEFAULT '';
ALTER TABLE canvas_nodes ADD COLUMN IF NOT EXISTS color TEXT NOT NULL DEFAULT '';
ALTER TABLE canvas_nodes ADD COLUMN IF NOT EXISTS text_align TEXT NOT NULL DEFAULT '';
ALTER TABLE canvas_nodes ADD COLUMN IF NOT EXISTS line_height DOUBLE PRECISION NOT NULL DEFAULT 0;
ALTER TABLE canvas_nodes ADD COLUMN IF NOT EXISTS letter_spacing DOUBLE PRECISION NOT NULL DEFAULT 0;
ALTER TABLE canvas_nodes ADD COLUMN IF NOT EXISTS text_decoration TEXT NOT NULL DEFAULT '';
ALTER TABLE canvas_nodes ADD COLUMN IF NOT EXISTS text_transform TEXT NOT NULL DEFAULT '';
ALTER TABLE canvas_nodes ADD COLUMN IF NOT EXISTS opacity DOUBLE PRECISION NOT NULL DEFAULT 0;
ALTER TABLE canvas_nodes ADD COLUMN IF NOT EXISTS fill_color TEXT NOT NULL DEFAULT '';
ALTER TABLE canvas_nodes ADD COLUMN IF NOT EXISTS stroke_color TEXT NOT NULL DEFAULT '';
ALTER TABLE canvas_nodes ADD COLUMN IF NOT EXISTS stroke_width DOUBLE PRECISION NOT NULL DEFAULT 0;
ALTER TABLE canvas_nodes ADD COLUMN IF NOT EXISTS stroke_style TEXT NOT NULL DEFAULT '';
ALTER TABLE canvas_nodes ADD COLUMN IF NOT EXISTS flip_x BOOLEAN NOT NULL DEFAULT FALSE;
ALTER TABLE canvas_nodes ADD COLUMN IF NOT EXISTS flip_y BOOLEAN NOT NULL DEFAULT FALSE;
ALTER TABLE canvas_nodes ADD COLUMN IF NOT EXISTS rotation DOUBLE PRECISION NOT NULL DEFAULT 0;

CREATE INDEX IF NOT EXISTS canvas_nodes_project_id_idx ON canvas_nodes(project_id);
CREATE INDEX IF NOT EXISTS canvas_nodes_user_project_idx ON canvas_nodes(user_id, project_id);

CREATE TABLE IF NOT EXISTS mockup_blobs (
    data_id TEXT PRIMARY KEY,
    width INTEGER NOT NULL,
    height INTEGER NOT NULL,
    data BYTEA NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS canvas_connections (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    user_id UUID NOT NULL DEFAULT '00000000-0000-0000-0000-000000000001'::uuid,
    from_node_id TEXT NOT NULL,
    to_node_id TEXT NOT NULL
);

ALTER TABLE canvas_connections ADD COLUMN IF NOT EXISTS user_id UUID;
ALTER TABLE canvas_connections ALTER COLUMN user_id DROP DEFAULT;
ALTER TABLE canvas_connections ALTER COLUMN user_id TYPE UUID USING NULLIF(user_id::text, '')::uuid;
UPDATE canvas_connections
SET user_id = COALESCE((SELECT p.user_id FROM projects p WHERE p.id = canvas_connections.project_id), '00000000-0000-0000-0000-000000000001'::uuid)
WHERE user_id IS NULL;
ALTER TABLE canvas_connections ALTER COLUMN user_id SET DEFAULT '00000000-0000-0000-0000-000000000001'::uuid;
ALTER TABLE canvas_connections ALTER COLUMN user_id SET NOT NULL;
CREATE INDEX IF NOT EXISTS canvas_connections_project_id_idx ON canvas_connections(project_id);
CREATE INDEX IF NOT EXISTS canvas_connections_user_project_idx ON canvas_connections(user_id, project_id);

CREATE TABLE IF NOT EXISTS canvas_snapshots (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    user_id UUID NOT NULL DEFAULT '00000000-0000-0000-0000-000000000001'::uuid,
    version TEXT NOT NULL,
    canvas TEXT NOT NULL,
    viewport_json JSONB NOT NULL,
    nodes_json JSONB NOT NULL,
    connections_json JSONB NOT NULL,
    created_at TIMESTAMPTZ NOT NULL
);

ALTER TABLE canvas_snapshots ALTER COLUMN user_id DROP DEFAULT;
ALTER TABLE canvas_snapshots ALTER COLUMN user_id TYPE UUID USING NULLIF(user_id::text, '')::uuid;
UPDATE canvas_snapshots SET user_id = '00000000-0000-0000-0000-000000000001'::uuid WHERE user_id IS NULL;
ALTER TABLE canvas_snapshots ALTER COLUMN user_id SET DEFAULT '00000000-0000-0000-0000-000000000001'::uuid;
ALTER TABLE canvas_snapshots ALTER COLUMN user_id SET NOT NULL;
CREATE INDEX IF NOT EXISTS canvas_snapshots_project_version_idx ON canvas_snapshots(user_id, project_id, created_at DESC);

CREATE TABLE IF NOT EXISTS agent_messages (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    user_id UUID NOT NULL DEFAULT '00000000-0000-0000-0000-000000000001'::uuid,
    role TEXT NOT NULL,
    message_type TEXT NOT NULL DEFAULT '',
    title TEXT NOT NULL,
    content TEXT NOT NULL,
    thread_id TEXT NOT NULL DEFAULT '',
    action_id TEXT NOT NULL DEFAULT '',
    step_id TEXT NOT NULL DEFAULT '',
    tool_call_id TEXT NOT NULL DEFAULT '',
    name TEXT NOT NULL DEFAULT '',
    tool_hint TEXT NOT NULL DEFAULT '',
    status TEXT NOT NULL DEFAULT '',
    created_at TIMESTAMPTZ NOT NULL
);

ALTER TABLE agent_messages ADD COLUMN IF NOT EXISTS user_id UUID;
ALTER TABLE agent_messages ALTER COLUMN user_id DROP DEFAULT;
ALTER TABLE agent_messages ALTER COLUMN user_id TYPE UUID USING NULLIF(user_id::text, '')::uuid;
UPDATE agent_messages
SET user_id = COALESCE((SELECT p.user_id FROM projects p WHERE p.id = agent_messages.project_id), '00000000-0000-0000-0000-000000000001'::uuid)
WHERE user_id IS NULL;
ALTER TABLE agent_messages ALTER COLUMN user_id SET DEFAULT '00000000-0000-0000-0000-000000000001'::uuid;
ALTER TABLE agent_messages ALTER COLUMN user_id SET NOT NULL;
ALTER TABLE agent_messages ADD COLUMN IF NOT EXISTS message_type TEXT NOT NULL DEFAULT '';
ALTER TABLE agent_messages ADD COLUMN IF NOT EXISTS thread_id TEXT NOT NULL DEFAULT '';
ALTER TABLE agent_messages ADD COLUMN IF NOT EXISTS action_id TEXT NOT NULL DEFAULT '';
ALTER TABLE agent_messages ADD COLUMN IF NOT EXISTS step_id TEXT NOT NULL DEFAULT '';
ALTER TABLE agent_messages ADD COLUMN IF NOT EXISTS tool_call_id TEXT NOT NULL DEFAULT '';
ALTER TABLE agent_messages ADD COLUMN IF NOT EXISTS name TEXT NOT NULL DEFAULT '';
ALTER TABLE agent_messages ADD COLUMN IF NOT EXISTS tool_hint TEXT NOT NULL DEFAULT '';
ALTER TABLE agent_messages ADD COLUMN IF NOT EXISTS status TEXT NOT NULL DEFAULT '';

CREATE INDEX IF NOT EXISTS agent_messages_project_id_idx ON agent_messages(project_id);
CREATE INDEX IF NOT EXISTS agent_messages_user_project_idx ON agent_messages(user_id, project_id);

CREATE TABLE IF NOT EXISTS outbox_events (
    id TEXT PRIMARY KEY,
    user_id UUID NOT NULL DEFAULT '00000000-0000-0000-0000-000000000001'::uuid,
    aggregate_type TEXT NOT NULL,
    aggregate_id TEXT NOT NULL,
    event_type TEXT NOT NULL,
    thread_id TEXT NOT NULL DEFAULT '',
    payload_json JSONB NOT NULL,
    status TEXT NOT NULL DEFAULT 'pending',
    published_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

ALTER TABLE outbox_events ALTER COLUMN user_id DROP DEFAULT;
ALTER TABLE outbox_events ALTER COLUMN user_id TYPE UUID USING NULLIF(user_id::text, '')::uuid;
UPDATE outbox_events SET user_id = '00000000-0000-0000-0000-000000000001'::uuid WHERE user_id IS NULL;
ALTER TABLE outbox_events ALTER COLUMN user_id SET DEFAULT '00000000-0000-0000-0000-000000000001'::uuid;
ALTER TABLE outbox_events ALTER COLUMN user_id SET NOT NULL;
CREATE INDEX IF NOT EXISTS outbox_events_status_created_idx ON outbox_events(status, created_at);
CREATE INDEX IF NOT EXISTS outbox_events_project_idx ON outbox_events(aggregate_type, aggregate_id, created_at);