DROP TABLE IF EXISTS items, records, market, players;

CREATE TABLE players
(
    p_id              INT AUTO_INCREMENT,
    p_name            VARCHAR(32) NOT NULL,
    p_gender          CHAR DEFAULT '男',
    registration_time DATE        NOT NULL,
    warehouse_id      INT         NOT NULL UNIQUE,
    p_grade           INT  DEFAULT 1,
    PRIMARY KEY (p_id)
) AUTO_INCREMENT = 10001;

CREATE TABLE items
(
    i_id          INT AUTO_INCREMENT,
    i_name        VARCHAR(16) NOT NULL,
    i_weight      INT         NOT NULL DEFAULT 1,
    i_space       INT         NOT NULL DEFAULT 1,
    i_value       INT         NOT NULL,
    i_information TEXT,
    i_class       VARCHAR(16) NOT NULL,
    PRIMARY KEY (i_id)
) AUTO_INCREMENT = 1001;

CREATE TABLE records
(
    r_id         INT AUTO_INCREMENT,
    seller_id    INT     NOT NULL,
    ri_id        INT     NOT NULL,
    ri_num       TINYINT NOT NULL DEFAULT 1,
    ri_price     INT     NOT NULL DEFAULT 1,
    shelf_time   DATE    NOT NULL,
    closing_time DATE,
    buyer_id     INT     NULL,
    PRIMARY KEY (r_id),
    FOREIGN KEY sellers_id (seller_id) REFERENCES players (p_id),
    FOREIGN KEY buyers_id (buyer_id) REFERENCES players (p_id),
    FOREIGN KEY items_id (ri_id) REFERENCES items (i_id)
) AUTO_INCREMENT = 100001;

CREATE TABLE market
(
    mi_id    INT AUTO_INCREMENT,
    mi_price INT NOT NULL,
    PRIMARY KEY (mi_id),
    FOREIGN KEY items_id (mi_id) REFERENCES items (i_id)
) AUTO_INCREMENT = 1001;

INSERT INTO players (p_name, p_gender, registration_time, warehouse_id, p_grade)
        VALUES
            ('医疗兵_001', '女', '2022-10-10', 10001, 24),
            ('突击兵_106', '男', '2023-7-2', 10002, 23),
            ('弗雷德', '男', '2023-5-2', 10003, 29),
            ('雷诺伊尔', '男', '2023-3-7', 10004, 29),
            ('多斯', '男', '2022-12-20', 10005, 30),
            ('杰克逊', '男', '2023-1-7', 10006, 23),
            ('寄术大师', '男', '2022-4-7', 10007, 30),
            ('无敌CS大王', '女', '2023-10-21', 10008, 27),
            ('沃伦', '男', '2022-12-10', 10009, 30),
            ('尤文', '男', '2022-1-1', 10010, 30),
            ('阿贾克斯', '男', '2022-1-1', 10011, 30),
            ('德文潘', '男', '2022-1-2', 10012, 30),
            ('兰德尔', '男', '2023-9-24', 10013, 25),
            ('罗尔夫', '男', '2023-11-5', 10014, 21),
            ('卡尔', '男', '2023-7-3', 10015, 25),
            ('思密达', '男', '2023-12-5', 10016, 22),
            ('科特', '男', '2023-11-5', 10017, 23),
            ('萌新', '女', '2024-1-13', 10018, 17);

    INSERT INTO items (i_name, i_weight, i_space, i_value, i_class)
        VALUES
            ('花瓶', 5, 4, 40, '收藏品'),
            ('保温水壶', 1, 2, 1, '生活用品'),
            ('茶壶', 5, 4, 40, '收藏品'),
            ('金豹雕像', 4, 2, 2, '收藏品'),
            ('金杯', 4, 2, 4, '收藏品'),
            ('金魔方', 4, 1, 5, '收藏品'),
            ('机密文件', 1, 2, 600, '收藏品'),
            ('金狮雕像', 9, 6, 42, '收藏品'),
            ('金块', 4, 2, 5, '收藏品'),
            ('金手镯', 3, 1, 2, '收藏品'),
            ('金手表', 3, 1, 2, '收藏品'),
            ('银徽章', 2, 1, 1, '纪念品'),
            ('银片手链', 2, 1, 1, '收藏品'),
            ('钻戒', 1, 1, 2, '收藏品'),
            ('红色火药', 1, 2, 1, '易燃物'),
            ('绿色火药', 1, 2, 1, '易燃物'),
            ('蓝色火药', 1, 2, 1, '易燃物'),
            ('主客房钥匙', 1, 1, 27, '钥匙'),
            ('201钥匙', 1, 1, 6, '钥匙'),
            ('别墅钥匙', 1, 1, 8, '钥匙'),
            ('储藏室钥匙', 1, 1, 4, '钥匙'),
            ('民宅钥匙', 1, 4, 1, '钥匙'),
            ('北村住宅钥匙', 1, 1, 1, '钥匙'),
            ('马厩钥匙', 1, 1, 2, '钥匙'),
            ('海滨别墅钥匙', 1, 1, 8, '钥匙'),
            ('车库钥匙', 1, 1, 3, '钥匙'),
            ('墓地钥匙', 1, 1, 8, '钥匙'),
            ('一楼休息室钥匙', 1, 1, 10, '钥匙'),
            ('武器贮藏室钥匙', 1, 1, 16, '钥匙'),
            ('水闸房钥匙', 1, 1, 11, '钥匙');

    INSERT INTO market (mi_price)
        VALUES
            (44),
            (1),
            (47),
            (2),
            (5),
            (6),
            (49),
            (6),
            (2),
            (3),
            (1),
            (1),
            (2),
            (1),
            (1),
            (1),
            (32),
            (6),
            (10),
            (4),
            (1),
            (1),
            (2),
            (8),
            (3),
            (1),
            (2),
            (13);

    INSERT INTO records (seller_id, ri_id, ri_num, ri_price, shelf_time, closing_time, buyer_id)
        VALUES
            (10001, 1001, 3, 44, '2024-4-10', '2024-4-11', 10002),
            (10001, 1022, 1, 1, '2024-4-1', null, null),
            (10001, 1030, 2, 13, '2024-4-10', '2024-4-11', 10005),
            (10001, 1004, 1, 2, '2024-4-7', '2024-4-8', 10006),
            (10001, 1023, 1, 1, '2024-4-7', '2024-4-8', 10005),
            (10001, 1008, 4, 49, '2024-4-7', '2024-4-8', 10009),
            (10001, 1011, 1, 13, '2024-4-2', '2024-4-8', 10013),
            (10001, 1017, 1, 1, '2024-4-2', '2024-4-8', 10018),
            (10001, 1014, 5, 2, '2024-4-1', '2024-4-8', 10017),
            (10002, 1006, 1, 6, '2024-4-1', '2024-4-8', 10012),
            (10002, 1015, 1, 1, '2024-4-5', '2024-4-8', 10013),
            (10002, 1017, 5, 1, '2024-4-5', '2024-4-8', 10001),
            (10002, 1028, 1, 1, '2024-4-5', '2024-4-8', 10017),
            (10002, 1005, 5, 5, '2024-4-5', '2024-4-8', 10003),
            (10002, 1008, 2, 50, '2024-4-5', '2024-4-8', 10006),
            (10002, 1018, 5, 32, '2024-4-7', '2024-4-8', 10009),
            (10003, 1001, 1, 46, '2024-4-7', '2024-4-8', 10005),
            (10003, 1021, 2, 4 , '2024-4-5', '2024-4-8', 10004),
            (10003, 1020, 1, 10, '2024-4-5', '2024-4-8', 10010),
            (10003, 1029, 2, 2, '2024-4-8', '2024-4-10', 10011),
            (10004, 1017, 1, 1, '2024-4-8', '2024-4-10', 10003),
            (10004, 1013, 1, 1, '2024-4-8', '2024-4-11', 10001),
            (10005, 1012, 1, 1, '2024-4-8', null, null),
            (10005, 1015, 3, 1, '2024-4-8', '2024-4-9', 10006),
            (10005, 1016, 1, 1, '2024-4-8', '2024-4-9', 10012),
            (10005, 1019, 1, 6, '2024-4-1', '2024-4-9', 10014),
            (10007, 1023, 4, 1, '2024-4-1', '2024-4-9', 10016),
            (10007, 1024, 1, 2, '2024-4-1', '2024-4-9', 10017),
            (10007, 1025, 3, 8, '2024-4-3', '2024-4-9', 10013),
            (10007, 1009, 1, 6, '2024-4-3', '2024-4-9', 10001),
            (10007, 1026, 1, 3, '2024-4-3', '2024-4-8', 10002),
            (10007, 1028, 2, 1, '2024-4-7', '2024-4-8', 10003),
            (10007, 1030, 1, 13,'2024-4-7', '2024-4-8', 10004),
            (10008, 1022, 1, 1 ,'2024-4-4', '2024-4-8', 10005),
            (10008, 1029, 1, 2 ,'2024-4-4', null, null),
            (10009, 1029, 3, 2 ,'2024-4-4', null, null),
            (10009, 1010, 1, 2 ,'2024-4-10', '2024-4-11', 10008),
            (10012, 1003, 1, 47,'2024-4-10', '2024-4-11', 10009),
            (10013, 1001, 4, 46,'2024-4-3', '2024-4-5', 10010),
            (10013, 1002, 1, 1 ,'2024-4-3', null, null),
            (10014, 1013, 2, 1 ,'2024-4-3', '2024-4-4', 10012),
            (10015, 1016, 1, 1 ,'2024-4-3', '2024-4-4', 10013),
            (10016, 1015, 1, 1 ,'2024-4-3', '2024-4-5', 10014),
            (10016, 1023, 3, 1 ,'2024-4-3', '2024-4-7', 10015),
            (10016, 1017, 1, 1 ,'2024-4-1', '2024-4-4', 10018),
            (10017, 1017, 1, 1 ,'2024-4-1', null, null),
            (10017, 1018, 1, 32,'2024-4-1', '2024-4-1', 10004),
            (10017, 1015, 5, 1 ,'2024-4-1', '2024-4-2', 10006),
            (10017, 1001, 1, 48,'2024-4-1', '2024-4-3', 10007),
            (10017, 1016, 1, 1 ,'2024-4-1', '2024-4-3', 10011);