"""
示例Oracle数据库创建脚本
用于创建测试数据库表和示例数据
"""

-- 创建员工表
CREATE TABLE EMPLOYEES (
    ID NUMBER PRIMARY KEY,
    NAME VARCHAR2(100) NOT NULL,
    DEPARTMENT VARCHAR2(50),
    SALARY NUMBER(10,2),
    HIRE_DATE DATE,
    EMAIL VARCHAR2(100),
    PHONE VARCHAR2(20)
);

-- 创建部门表
CREATE TABLE DEPARTMENTS (
    ID NUMBER PRIMARY KEY,
    NAME VARCHAR2(50) NOT NULL,
    LOCATION VARCHAR2(100),
    BUDGET NUMBER(15,2)
);

-- 创建项目表
CREATE TABLE PROJECTS (
    ID NUMBER PRIMARY KEY,
    NAME VARCHAR2(100) NOT NULL,
    DEPARTMENT_ID NUMBER,
    START_DATE DATE,
    END_DATE DATE,
    STATUS VARCHAR2(20),
    BUDGET NUMBER(15,2),
    FOREIGN KEY (DEPARTMENT_ID) REFERENCES DEPARTMENTS(ID)
);

-- 插入部门数据
INSERT INTO DEPARTMENTS VALUES (1, 'IT', '北京', 1000000);
INSERT INTO DEPARTMENTS VALUES (2, 'HR', '上海', 500000);
INSERT INTO DEPARTMENTS VALUES (3, 'Finance', '深圳', 800000);
INSERT INTO DEPARTMENTS VALUES (4, 'Marketing', '广州', 600000);
INSERT INTO DEPARTMENTS VALUES (5, 'Operations', '成都', 700000);

-- 插入员工数据
INSERT INTO EMPLOYEES VALUES (1, '张三', 'IT', 12000, TO_DATE('2020-01-15', 'YYYY-MM-DD'), 'zhangsan@company.com', '13800138001');
INSERT INTO EMPLOYEES VALUES (2, '李四', 'IT', 15000, TO_DATE('2019-05-20', 'YYYY-MM-DD'), 'lisi@company.com', '13800138002');
INSERT INTO EMPLOYEES VALUES (3, '王五', 'HR', 8000, TO_DATE('2021-03-10', 'YYYY-MM-DD'), 'wangwu@company.com', '13800138003');
INSERT INTO EMPLOYEES VALUES (4, '赵六', 'Finance', 10000, TO_DATE('2020-08-25', 'YYYY-MM-DD'), 'zhaoliu@company.com', '13800138004');
INSERT INTO EMPLOYEES VALUES (5, '钱七', 'IT', 18000, TO_DATE('2018-11-30', 'YYYY-MM-DD'), 'qianqi@company.com', '13800138005');
INSERT INTO EMPLOYEES VALUES (6, '孙八', 'Marketing', 9000, TO_DATE('2021-07-15', 'YYYY-MM-DD'), 'sunba@company.com', '13800138006');
INSERT INTO EMPLOYEES VALUES (7, '周九', 'Operations', 8500, TO_DATE('2020-12-01', 'YYYY-MM-DD'), 'zhoujiu@company.com', '13800138007');
INSERT INTO EMPLOYEES VALUES (8, '吴十', 'IT', 14000, TO_DATE('2019-09-20', 'YYYY-MM-DD'), 'wushi@company.com', '13800138008');
INSERT INTO EMPLOYEES VALUES (9, '郑十一', 'Finance', 11000, TO_DATE('2020-04-10', 'YYYY-MM-DD'), 'zhengshiyi@company.com', '13800138009');
INSERT INTO EMPLOYEES VALUES (10, '王十二', 'HR', 7500, TO_DATE('2022-02-28', 'YYYY-MM-DD'), 'wangshier@company.com', '13800138010');

-- 插入项目数据
INSERT INTO PROJECTS VALUES (1, '数字化转型项目', 1, TO_DATE('2023-01-01', 'YYYY-MM-DD'), TO_DATE('2023-12-31', 'YYYY-MM-DD'), '进行中', 500000);
INSERT INTO PROJECTS VALUES (2, '人力资源系统升级', 2, TO_DATE('2023-03-01', 'YYYY-MM-DD'), TO_DATE('2023-09-30', 'YYYY-MM-DD'), '已完成', 200000);
INSERT INTO PROJECTS VALUES (3, '财务审计系统', 3, TO_DATE('2023-06-01', 'YYYY-MM-DD'), TO_DATE('2024-03-31', 'YYYY-MM-DD'), '进行中', 300000);
INSERT INTO PROJECTS VALUES (4, '市场推广活动', 4, TO_DATE('2023-04-01', 'YYYY-MM-DD'), TO_DATE('2023-10-31', 'YYYY-MM-DD'), '已完成', 250000);
INSERT INTO PROJECTS VALUES (5, '运营优化项目', 5, TO_DATE('2023-02-01', 'YYYY-MM-DD'), TO_DATE('2023-08-31', 'YYYY-MM-DD'), '已完成', 150000);

COMMIT;

-- 创建一些有用的视图
CREATE OR REPLACE VIEW V_EMPLOYEE_DEPT AS
SELECT e.ID, e.NAME, e.DEPARTMENT, e.SALARY, e.HIRE_DATE, d.LOCATION, d.BUDGET AS DEPT_BUDGET
FROM EMPLOYEES e
LEFT JOIN DEPARTMENTS d ON e.DEPARTMENT = d.NAME;

CREATE OR REPLACE VIEW V_DEPARTMENT_STATS AS
SELECT d.ID, d.NAME, d.LOCATION, d.BUDGET, COUNT(e.ID) AS EMP_COUNT, AVG(e.SALARY) AS AVG_SALARY
FROM DEPARTMENTS d
LEFT JOIN EMPLOYEES e ON d.NAME = e.DEPARTMENT
GROUP BY d.ID, d.NAME, d.LOCATION, d.BUDGET;

COMMIT;