set nocount on;
set xact_abort on;

/*
  03仓密集库货位初始化

  cargo_space_id: 仓库(2位) + 排(2位) + 列(2位) + 层(2位)
  status:
    1 普通空货位
    7 提升机
    8 输送机
    9 母通道
    10 充电桩

  group_coding:
    同一排、同一层、同一连续存储通道使用该通道最小货位编码。
    例如 03010101～03010601 的 group_coding 均为 03010101。
*/

if exists (select 1 from dbo.bas_cargo_space where warehouse_id = N'03')
begin
    throw 51000, N'03仓已经存在货位数据，脚本拒绝覆盖。', 1;
end;

declare @footprint table
(
    the_column int not null,
    platoon_from int not null,
    platoon_to int not null
);

-- 按平面图录入实际存在的坐标范围；列25无节点。
insert into @footprint(the_column, platoon_from, platoon_to)
values
    (1, 1, 3), (2, 1, 3), (3, 1, 3),
    (4, 1, 5), (4, 9, 13),
    (5, 1, 7), (5, 9, 13),
    (6, 1, 13),
    (7, 1, 13), (8, 1, 13), (9, 1, 13),
    (10, 1, 13), (11, 1, 13), (12, 1, 13),
    (13, 1, 13), (14, 1, 13), (15, 1, 13),
    (16, 1, 13), (17, 1, 13), (18, 1, 13),
    (19, 1, 13), (20, 1, 13), (21, 1, 13),
    (22, 1, 5), (22, 9, 13),
    (23, 9, 13), (24, 9, 13);

declare @numbers table (number_value int not null primary key);
insert into @numbers(number_value)
values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),
       (14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24),(25);

declare @generated table
(
    cargo_space_id nvarchar(80) not null primary key,
    warehouse_id nvarchar(80) not null,
    roadway_id nvarchar(80) not null,
    cargo_area_id nvarchar(80) null,
    the_type nvarchar(5) null,
    the_type_name nvarchar(80) null,
    cargo_space_name nvarchar(80) not null,
    the_platoon int not null,
    the_column int not null,
    the_floor int not null,
    status nvarchar(5) not null,
    locked nvarchar(5) not null,
    tray_code nvarchar(255) null,
    iss_double nvarchar(2) null,
    group_coding_no nvarchar(80) null,
    group_coding nvarchar(80) null,
    cargo_space_logo nvarchar(2) null,
    this_cargo_area nvarchar(10) null,
    channel_band int null,
    dept_id int null,
    dept_name nvarchar(255) null,
    the_weight float null,
    the_height float null
);

;with coordinates as
(
    select
        p.number_value as the_platoon,
        f.the_column,
        fl.number_value as the_floor
    from @footprint f
    inner join @numbers p
        on p.number_value between f.platoon_from and f.platoon_to
    inner join @numbers fl
        on fl.number_value between 1 and 5
), classified as
(
    select
        c.*,
        case
            when c.the_platoon = 6 and c.the_column = 5 and c.the_floor in (1, 2) then N'10'
            when c.the_platoon = 7 and c.the_column in (5, 21) then N'7'
            when c.the_platoon = 7 and c.the_column in (6, 20) then N'8'
            when c.the_column in (7, 13, 19)
              or (c.the_platoon = 7 and c.the_column between 7 and 19) then N'9'
            else N'1'
        end as status,
        case
            when c.the_column between 1 and 6 then 1
            when c.the_column between 8 and 12 then 2
            when c.the_column between 14 and 18 then 3
            when c.the_column between 20 and 25 then 4
        end as channel_band
    from coordinates c
)
insert into @generated
(
    cargo_space_id, warehouse_id, roadway_id, cargo_area_id,
    the_type, the_type_name, cargo_space_name,
    the_platoon, the_column, the_floor, status, locked,
    tray_code, iss_double, group_coding_no, group_coding,
    cargo_space_logo, this_cargo_area, channel_band,
    dept_id, dept_name, the_weight, the_height
)
select
    concat(
        N'03',
        right(N'00' + convert(nvarchar(2), the_platoon), 2),
        right(N'00' + convert(nvarchar(2), the_column), 2),
        right(N'00' + convert(nvarchar(2), the_floor), 2)
    ),
    N'03',
    N'1',
    null,
    N'1',
    case status when N'1' then N'普通货位' when N'7' then N'提升机' when N'8' then N'输送机' when N'9' then N'母通道' when N'10' then N'充电桩' end,
    concat(
        right(N'00' + convert(nvarchar(2), the_platoon), 2), N'排',
        right(N'00' + convert(nvarchar(2), the_column), 2), N'列',
        right(N'00' + convert(nvarchar(2), the_floor), 2), N'层',
        case status when N'7' then N'-提升机' when N'8' then N'-输送机' when N'9' then N'-母通道' when N'10' then N'-充电桩' else N'' end
    ),
    the_platoon,
    the_column,
    the_floor,
    status,
    N'N',
    null,
    N'N',
    null,
    null,
    null,
    case
        when status <> N'1' then null
        when the_platoon between 1 and 6 then nchar(unicode(N'A') + (channel_band - 1) * 2)
        when the_platoon between 8 and 13 then nchar(unicode(N'A') + (channel_band - 1) * 2 + 1)
    end,
    channel_band,
    null,
    null,
    null,
    null
from classified;

-- 普通货位以每条通道的最小货位编码作为统一分组编码。
;with lane_groups as
(
    select
        cargo_space_id,
        min(cargo_space_id) over
        (
            partition by the_platoon, the_floor, channel_band
        ) as lane_group_coding
    from @generated
    where status = N'1'
)
update g
set g.group_coding = lg.lane_group_coding
from @generated g
inner join lane_groups lg on lg.cargo_space_id = g.cargo_space_id;

-- 写库前完整性校验。
if (select count(*) from @generated) <> 1295
    throw 51001, N'03仓生成总数异常，预期1295。', 1;

if (select count(*) from @generated where status = N'1') <> 1028
    throw 51002, N'03仓普通货位数量异常，预期1028。', 1;

if (select count(*) from @generated where status = N'7') <> 10
    throw 51003, N'03仓提升机节点数量异常，预期10。', 1;

if (select count(*) from @generated where status = N'8') <> 10
    throw 51004, N'03仓输送机节点数量异常，预期10。', 1;

if (select count(*) from @generated where status = N'9') <> 245
    throw 51005, N'03仓母通道节点数量异常，预期245。', 1;

if (select count(*) from @generated where status = N'10') <> 2
    throw 51010, N'03仓充电桩节点数量异常，预期2。', 1;

if (select count(distinct group_coding) from @generated where status = N'1') <> 240
    throw 51006, N'03仓通道分组数量异常，预期240。', 1;

if (select count(*) from @generated where status = N'1' and this_cargo_area between N'A' and N'H') <> 1028
    throw 51011, N'03仓A-H货区编码数量异常，预期1028。', 1;

if (select count(distinct this_cargo_area) from @generated where status = N'1') <> 8
    throw 51012, N'03仓货区编码数量异常，预期A-H共8个。', 1;

if exists
(
    select 1
    from @generated
    where (status = N'1' and group_coding is null)
       or (status <> N'1' and group_coding is not null)
)
    throw 51007, N'03仓group_coding分配异常。', 1;

if
(
    select count(*)
    from @generated
    where cargo_space_id in (N'03010101', N'03010201', N'03010301', N'03010401', N'03010501', N'03010601')
      and group_coding = N'03010101'
) <> 6
    throw 51008, N'示例通道03010101-03010601分组异常。', 1;

begin try
    begin transaction;

    if not exists (select 1 from dbo.bas_warehouse with (updlock, holdlock) where warehouse_id = N'03')
    begin
        insert into dbo.bas_warehouse
        (
            warehouse_id, warehouse_name, address, c_desc,
            warehouse_kind, warehouse_type, is_active
        )
        values
        (
            N'03', N'密集库', null, N'四向穿梭车密集库',
            null, N'1', N'1'
        );
    end;

    if not exists
    (
        select 1
        from dbo.bas_roadway with (updlock, holdlock)
        where warehouse_id = N'03' and roadway_id = N'1'
    )
    begin
        insert into dbo.bas_roadway
        (
            roadway_id, warehouse_id, roadway_name,
            device_id, in_locked, out_locked
        )
        values
        (
            N'1', N'03', N'密集库巷道',
            null, N'N', N'N'
        );
    end;

    if not exists
    (
        select 1 from dbo.sys_dict_data
        where dict_type = N'bas_cargo_space_status' and dict_value = N'7'
    )
    begin
        insert into dbo.sys_dict_data
        (
            dict_sort, dict_label, dict_value, dict_type,
            css_class, list_class, is_default, status,
            create_by, create_time, update_by, update_time, remark
        )
        values
        (
            7, N'提升机', N'7', N'bas_cargo_space_status',
            null, N'info', N'N', N'0',
            N'admin', getdate(), N'', null, N'03仓提升机节点'
        );
    end;

    if not exists
    (
        select 1 from dbo.sys_dict_data
        where dict_type = N'bas_cargo_space_status' and dict_value = N'10'
    )
    begin
        insert into dbo.sys_dict_data
        (
            dict_sort, dict_label, dict_value, dict_type,
            css_class, list_class, is_default, status,
            create_by, create_time, update_by, update_time, remark
        )
        values
        (
            10, N'充电桩', N'10', N'bas_cargo_space_status',
            null, N'info', N'N', N'0',
            N'admin', getdate(), N'', null, N'03仓充电桩节点'
        );
    end;

    if not exists
    (
        select 1 from dbo.sys_dict_data
        where dict_type = N'bas_cargo_space_status' and dict_value = N'8'
    )
    begin
        insert into dbo.sys_dict_data
        (
            dict_sort, dict_label, dict_value, dict_type,
            css_class, list_class, is_default, status,
            create_by, create_time, update_by, update_time, remark
        )
        values
        (
            8, N'输送机', N'8', N'bas_cargo_space_status',
            null, N'warning', N'N', N'0',
            N'admin', getdate(), N'', null, N'03仓输送机节点'
        );
    end;

    if not exists
    (
        select 1 from dbo.sys_dict_data
        where dict_type = N'bas_cargo_space_status' and dict_value = N'9'
    )
    begin
        insert into dbo.sys_dict_data
        (
            dict_sort, dict_label, dict_value, dict_type,
            css_class, list_class, is_default, status,
            create_by, create_time, update_by, update_time, remark
        )
        values
        (
            9, N'母通道', N'9', N'bas_cargo_space_status',
            null, N'success', N'N', N'0',
            N'admin', getdate(), N'', null, N'03仓母通道节点'
        );
    end;

    insert into dbo.bas_cargo_space
    (
        cargo_space_id, warehouse_id, roadway_id, cargo_area_id,
        the_type, the_type_name, cargo_space_name,
        the_platoon, the_column, the_floor, status, locked,
        tray_code, iss_double, group_coding_no, group_coding,
        cargo_space_logo, this_cargo_area,
        dept_id, dept_name, the_weight, the_height
    )
    select
        cargo_space_id, warehouse_id, roadway_id, cargo_area_id,
        the_type, the_type_name, cargo_space_name,
        the_platoon, the_column, the_floor, status, locked,
        tray_code, iss_double, group_coding_no, group_coding,
        cargo_space_logo, this_cargo_area,
        dept_id, dept_name, the_weight, the_height
    from @generated;

    if (select count(*) from dbo.bas_cargo_space where warehouse_id = N'03') <> 1295
        throw 51009, N'03仓写入后数量校验失败。', 1;

    commit transaction;
end try
begin catch
    if xact_state() <> 0
        rollback transaction;
    throw;
end catch;

select
    status,
    count(*) as cargo_space_count
from dbo.bas_cargo_space
where warehouse_id = N'03'
group by status
order by status;

select
    count(*) as total_count,
    count(distinct case when status = N'1' then group_coding end) as lane_group_count,
    count(distinct the_floor) as floor_count
from dbo.bas_cargo_space
where warehouse_id = N'03';