-- ============================================================
-- 第 5 题：各区域代理人产能统计 — 测试数据
-- 对应题目：problems/06-region-agent-productivity-sql.md
-- 查询语句：sql/05-region-agent-productivity.sql
-- ============================================================

-- ----------------------------------------------------------
-- 示例 1
-- ----------------------------------------------------------
DROP TABLE IF EXISTS tb_policy;
DROP TABLE IF EXISTS tb_agent;
DROP TABLE IF EXISTS tb_region;

CREATE TABLE tb_region (
    region_id VARCHAR(10) PRIMARY KEY,
    region_name VARCHAR(50) NOT NULL
) CHARACTER SET utf8 COLLATE utf8_bin;

CREATE TABLE tb_agent (
    agent_id VARCHAR(10) PRIMARY KEY,
    agent_name VARCHAR(50) NOT NULL,
    region_id VARCHAR(10) NOT NULL
) CHARACTER SET utf8 COLLATE utf8_bin;

CREATE TABLE tb_policy (
    policy_id VARCHAR(10) PRIMARY KEY,
    agent_id VARCHAR(10) NOT NULL,
    premium DECIMAL(10,2) NOT NULL,
    issue_date datetime NOT NULL
) CHARACTER SET utf8 COLLATE utf8_bin;

INSERT INTO tb_region(region_id, region_name) VALUES
    ('R1', '华东区'),
    ('R2', '华南区'),
    ('R3', '华北区');

INSERT INTO tb_agent(agent_id, agent_name, region_id) VALUES
    ('A1', '张伟', 'R1'),
    ('A2', '李娜', 'R1'),
    ('A3', '王强', 'R2'),
    ('A4', '赵敏', 'R3');

INSERT INTO tb_policy(policy_id, agent_id, premium, issue_date) VALUES
    ('P1', 'A1', 10000, '2024-01-15 10:00:00'),
    ('P2', 'A1', 20000, '2024-03-20 14:00:00'),
    ('P3', 'A2', 15000, '2024-05-10 09:00:00'),
    ('P4', 'A3', 30000, '2024-02-05 11:00:00'),
    ('P5', 'A3', 25000, '2024-06-18 13:00:00'),
    ('P6', 'A1', 5000, '2023-12-01 10:00:00');

-- 预期输出：
-- region_name | agent_count | policy_count | total_premium | avg_premium
-- 华南区       | 1           | 2            | 55000.00      | 55000.00
-- 华东区       | 2           | 3            | 45000.00      | 22500.00
-- （华北区赵敏无 2024 年保单 → 不计入；P6 是 2023 年 → 不计入）


-- ----------------------------------------------------------
-- 示例 2
-- ----------------------------------------------------------
DROP TABLE IF EXISTS tb_policy;
DROP TABLE IF EXISTS tb_agent;
DROP TABLE IF EXISTS tb_region;

CREATE TABLE tb_region (
    region_id VARCHAR(10) PRIMARY KEY,
    region_name VARCHAR(50) NOT NULL
) CHARACTER SET utf8 COLLATE utf8_bin;

CREATE TABLE tb_agent (
    agent_id VARCHAR(10) PRIMARY KEY,
    agent_name VARCHAR(50) NOT NULL,
    region_id VARCHAR(10) NOT NULL
) CHARACTER SET utf8 COLLATE utf8_bin;

CREATE TABLE tb_policy (
    policy_id VARCHAR(10) PRIMARY KEY,
    agent_id VARCHAR(10) NOT NULL,
    premium DECIMAL(10,2) NOT NULL,
    issue_date datetime NOT NULL
) CHARACTER SET utf8 COLLATE utf8_bin;

INSERT INTO tb_region(region_id, region_name) VALUES
    ('R1', '东北区'),
    ('R2', '西南区'),
    ('R3', '西北区');

INSERT INTO tb_agent(agent_id, agent_name, region_id) VALUES
    ('A1', '孙一', 'R1'),
    ('A2', '钱二', 'R1'),
    ('A3', '周三', 'R1'),
    ('A4', '吴四', 'R2'),
    ('A5', '郑五', 'R3');

INSERT INTO tb_policy(policy_id, agent_id, premium, issue_date) VALUES
    ('P1', 'A1', 8000, '2024-01-10 10:00:00'),
    ('P2', 'A1', 12000, '2024-04-15 14:00:00'),
    ('P3', 'A2', 5000, '2024-02-20 09:00:00'),
    ('P4', 'A3', 15000, '2024-06-01 11:00:00'),
    ('P5', 'A3', 10000, '2024-08-10 13:00:00'),
    ('P6', 'A4', 20000, '2024-03-05 10:00:00'),
    ('P7', 'A4', 30000, '2024-07-20 14:00:00'),
    ('P8', 'A4', 10000, '2024-11-11 09:00:00');

-- 预期输出：
-- region_name | agent_count | policy_count | total_premium | avg_premium
-- 西南区       | 1           | 3            | 60000.00      | 60000.00
-- 东北区       | 3           | 5            | 50000.00      | 16666.67
-- （西北区郑五无 2024 年保单 → 不计入）
-- 东北区人均 = 50000 / 3 = 16666.67