-- 订单管理系统数据库初始化脚本
-- 在Supabase SQL编辑器中运行此脚本

-- 创建订单表（如果不存在）
CREATE TABLE IF NOT EXISTS orders (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id TEXT NOT NULL,
  interpreter_id UUID NULL,
  type TEXT NOT NULL CHECK (type IN ('interpretation', 'document', 'video', 'localization')),
  status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'confirmed', 'in_progress', 'completed', 'cancelled')),
  source_language TEXT NOT NULL,
  target_language TEXT NOT NULL,
  scheduled_date TIMESTAMP WITH TIME ZONE NOT NULL,
  duration INTEGER NULL, -- 持续时间（分钟）
  service_address TEXT NULL,
  special_requirements TEXT NULL,
  total_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  payment_status TEXT NOT NULL DEFAULT 'pending' CHECK (payment_status IN ('pending', 'paid', 'refunded')),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 创建用户表（如果不存在）
CREATE TABLE IF NOT EXISTS users (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT UNIQUE NOT NULL,
  phone TEXT NULL,
  company TEXT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 创建口译员表（如果不存在）
CREATE TABLE IF NOT EXISTS interpreters (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT UNIQUE NOT NULL,
  phone TEXT NULL,
  languages TEXT[] NOT NULL DEFAULT '{}',
  specializations TEXT[] NOT NULL DEFAULT '{}',
  hourly_rate DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'inactive', 'busy')),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 创建服务费率表
CREATE TABLE IF NOT EXISTS service_rates (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  service_type TEXT NOT NULL,
  language_pair TEXT NOT NULL,
  base_rate DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  urgency_multiplier DECIMAL(3,2) NOT NULL DEFAULT 1.00,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  UNIQUE(service_type, language_pair)
);

-- 插入测试用户数据
INSERT INTO users (id, name, email, phone, company) VALUES 
  ('user_001', '张三', 'zhangsan@example.com', '13800138001', '北京科技有限公司'),
  ('user_002', '李四', 'lisi@example.com', '13800138002', '上海贸易集团'),
  ('user_003', '王五', 'wangwu@example.com', '13800138003', '深圳创新科技'),
  ('user_004', '赵六', 'zhaoliu@example.com', '13800138004', '广州国际贸易'),
  ('user_005', '孙七', 'sunqi@example.com', '13800138005', '杭州电子商务')
ON CONFLICT (email) DO NOTHING;

-- 插入测试口译员数据
INSERT INTO interpreters (id, name, email, phone, languages, specializations, hourly_rate) VALUES 
  ('int_001', '王译员', 'wang@interpreter.com', '13900139001', ARRAY['zh', 'en'], ARRAY['business', 'technology'], 150.00),
  ('int_002', '陈译员', 'chen@interpreter.com', '13900139002', ARRAY['en', 'zh'], ARRAY['legal', 'medical'], 180.00),
  ('int_003', '刘译员', 'liu@interpreter.com', '13900139003', ARRAY['zh', 'ja'], ARRAY['business', 'tourism'], 120.00)
ON CONFLICT (email) DO NOTHING;

-- 插入服务费率数据
INSERT INTO service_rates (service_type, language_pair, base_rate, urgency_multiplier) VALUES 
  ('interpretation', 'zh-en', 120.00, 1.5),
  ('interpretation', 'en-zh', 120.00, 1.5),
  ('document', 'zh-en', 0.15, 2.0),
  ('document', 'en-zh', 0.15, 2.0),
  ('video', 'zh-en', 200.00, 1.8),
  ('video', 'en-zh', 200.00, 1.8),
  ('localization', 'zh-ja', 0.20, 1.2),
  ('localization', 'ja-zh', 0.20, 1.2)
ON CONFLICT (service_type, language_pair) DO NOTHING;

-- 插入测试订单数据
INSERT INTO orders (
  user_id,
  interpreter_id,
  type,
  status,
  source_language,
  target_language,
  scheduled_date,
  duration,
  service_address,
  special_requirements,
  total_amount,
  payment_status,
  created_at,
  updated_at
) VALUES
  (
    'user_001',
    NULL,
    'interpretation',
    'pending',
    'zh',
    'en',
    '2024-12-05 14:00:00+08',
    120,
    '北京市朝阳区商务中心',
    '商务会议同声传译，需要专业商务背景',
    800.00,
    'pending',
    NOW() - INTERVAL '1 day',
    NOW() - INTERVAL '1 day'
  ),
  (
    'user_002',
    'int_001',
    'document',
    'completed',
    'zh',
    'en',
    '2024-11-28 09:00:00+08',
    480,
    '上海市浦东新区办公楼',
    '技术文档翻译，软件相关',
    1200.00,
    'paid',
    NOW() - INTERVAL '5 days',
    NOW() - INTERVAL '2 days'
  ),
  (
    'user_003',
    'int_002',
    'video',
    'in_progress',
    'en',
    'zh',
    '2024-12-01 15:30:00+08',
    90,
    '线上视频会议',
    '国际视频会议翻译，紧急项目',
    720.00,
    'pending',
    NOW() - INTERVAL '2 hours',
    NOW() - INTERVAL '1 hour'
  ),
  (
    'user_004',
    'int_003',
    'document',
    'confirmed',
    'zh',
    'en',
    '2024-12-03 10:00:00+08',
    240,
    '广州市天河区律师事务所',
    '商务合同翻译，需要法律专业背景',
    600.00,
    'pending',
    NOW() - INTERVAL '2 days',
    NOW() - INTERVAL '6 hours'
  ),
  (
    'user_005',
    NULL,
    'localization',
    'cancelled',
    'zh',
    'ja',
    '2024-12-02 09:00:00+08',
    360,
    '杭州市西湖区科技园',
    '产品说明书多语言翻译',
    900.00,
    'refunded',
    NOW() - INTERVAL '3 days',
    NOW() - INTERVAL '2 days'
  )
ON CONFLICT DO NOTHING;

-- 创建更新时间戳的触发器函数
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ language 'plpgsql';

-- 为各表创建更新时间戳触发器
DROP TRIGGER IF EXISTS update_orders_updated_at ON orders;
CREATE TRIGGER update_orders_updated_at
    BEFORE UPDATE ON orders
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

DROP TRIGGER IF EXISTS update_users_updated_at ON users;
CREATE TRIGGER update_users_updated_at
    BEFORE UPDATE ON users
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

DROP TRIGGER IF EXISTS update_interpreters_updated_at ON interpreters;
CREATE TRIGGER update_interpreters_updated_at
    BEFORE UPDATE ON interpreters
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- 启用行级安全策略（RLS）
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
ALTER TABLE interpreters ENABLE ROW LEVEL SECURITY;
ALTER TABLE service_rates ENABLE ROW LEVEL SECURITY;

-- 创建基本的RLS策略（允许所有操作，实际使用时需要根据需求调整）
CREATE POLICY "Allow all operations on orders" ON orders FOR ALL USING (true);
CREATE POLICY "Allow all operations on users" ON users FOR ALL USING (true);
CREATE POLICY "Allow all operations on interpreters" ON interpreters FOR ALL USING (true);
CREATE POLICY "Allow all operations on service_rates" ON service_rates FOR ALL USING (true);

-- 创建索引以提高查询性能
CREATE INDEX IF NOT EXISTS idx_orders_user_id ON orders(user_id);
CREATE INDEX IF NOT EXISTS idx_orders_interpreter_id ON orders(interpreter_id);
CREATE INDEX IF NOT EXISTS idx_orders_status ON orders(status);
CREATE INDEX IF NOT EXISTS idx_orders_scheduled_date ON orders(scheduled_date);
CREATE INDEX IF NOT EXISTS idx_orders_created_at ON orders(created_at);

-- 查询验证数据插入
SELECT 
  'orders' as table_name, 
  COUNT(*) as record_count 
FROM orders
UNION ALL
SELECT 
  'users' as table_name, 
  COUNT(*) as record_count 
FROM users
UNION ALL
SELECT 
  'interpreters' as table_name, 
  COUNT(*) as record_count 
FROM interpreters
UNION ALL
SELECT 
  'service_rates' as table_name, 
  COUNT(*) as record_count 
FROM service_rates;