create table t_county
(
    id char(32) not null
        primary key,
    name varchar(64) null,
    manager varchar(64) null,
    manager_phone char(11) null
)
    comment '贫困县';

create table t_industry_type
(
    id char(32) not null
        primary key,
    name varchar(64) null
)
    comment '扶贫类型';

create table t_industry_info
(
    id char(32) not null
        primary key,
    name varchar(64) null,
    type_id char(64) null,
    constraint fk_info_type
        foreign key (type_id) references t_industry_type (id)
)
    comment '产业名';

create table t_village
(
    id char(32) not null
        primary key,
    name varchar(64) null,
    county_id char(32) null,
    manager varchar(64) null,
    manager_phone char(11) null,
    lat double(9,6) null,
    lng double(9,6) null,
    constraint fk_village_county
        foreign key (county_id) references t_county (id)
)
    comment '贫困村';

create table t_industry_pool
(
    id char(32) not null
        primary key,
    info_id char(32) null,
    village_id char(32) null,
    constraint fk_pool_info
        foreign key (info_id) references t_industry_info (id),
    constraint fk_pool_village
        foreign key (village_id) references t_village (id)
)
    comment '扶贫产业';

create view v_industry_info as
select `i`.`id` AS `id`, `i`.`name` AS `name`, `i`.`type_id` AS `type_id`, `t`.`name` AS `type_name`
from (`pa`.`t_industry_info` `i`
         left join `pa`.`t_industry_type` `t` on ((`i`.`type_id` = `t`.`id`)));

create view v_industry_pool as
select `p`.`id`         AS `id`,
       `p`.`info_id`    AS `info_id`,
       `p`.`village_id` AS `village_id`,
       `i`.`name`       AS `info_name`,
       `v`.`name`       AS `village_name`
from ((`pa`.`t_industry_pool` `p` left join `pa`.`t_industry_info` `i` on ((`i`.`id` = `p`.`info_id`)))
         left join `pa`.`t_village` `v` on ((`v`.`id` = `p`.`village_id`)));

create view v_village as
select `v`.`id`            AS `id`,
       `v`.`name`          AS `name`,
       `v`.`county_id`     AS `county_id`,
       `v`.`manager`       AS `manager`,
       `v`.`manager_phone` AS `manager_phone`,
       `v`.`lat`           AS `lat`,
       `v`.`lng`           AS `lng`,
       `c`.`name`          AS `county_name`
from (`pa`.`t_village` `v`
         left join `pa`.`t_county` `c` on ((`v`.`county_id` = `c`.`id`)));

create view v_village_all as
select `v`.`id`            AS `id`,
       `v`.`name`          AS `name`,
       `v`.`county_id`     AS `county_id`,
       `v`.`manager`       AS `manager`,
       `v`.`manager_phone` AS `manager_phone`,
       `v`.`lat`           AS `lat`,
       `v`.`lng`           AS `lng`,
       `t`.`id`            AS `type_id`,
       `t`.`name`          AS `type_name`,
       `i`.`name`          AS `info_name`
from (((`pa`.`t_industry_pool` `p` left join `pa`.`t_village` `v` on ((`v`.`id` = `p`.`village_id`))) left join `pa`.`t_industry_info` `i` on ((`p`.`info_id` = `i`.`id`)))
         left join `pa`.`t_industry_type` `t` on ((`t`.`id` = `i`.`type_id`)));