drop table if exists user_tool_values;
drop table if exists user_tools;

drop table if exists tool_attribute_map;
drop table if exists tool_attributes;
drop table if exists tool_attribute_groups;

-- tool_attribute_groups
create table if not exists tool_attribute_groups (
  id                tinyint unsigned    not null                        auto_increment  primary key ,
  name              varchar(50)                    default null
) engine=innodb charset=utf8 collate=utf8_general_ci;
insert into tool_attribute_groups (id, name) values
(1, 'css'),
(2, 'columns'),
(3, 'rake_back'),
(4, 'prop'),
(5, 'welcome_email'),
(6, 'convert_request_email'),
(7, 'xml_link');

-- tool_attributes
create table if not exists tool_attributes (
  id                tinyint unsigned    not null                        auto_increment  primary key ,
  name              varchar(50)                    default null
) engine=innodb charset=utf8 collate=utf8_general_ci;
insert into tool_attributes (id, name) values
-- css
(1, 'css'),
-- columns, multiple values
(2, 'columns'),
-- site_ids, multiple values
(3, 'site_ids'),
-- email
(4, 'from_address'),
(5, 'reply_to_address'),
(6, 'body'),
-- link
(7, 'href');


-- tool_attribute_map
create table if not exists tool_attribute_map (
  tool_id                   tinyint unsigned                default null            ,
  tool_attribute_group_id   tinyint unsigned                default null            ,
  tool_attribute_id         tinyint unsigned                default null            ,
  foreign key (tool_id                  ) references tools                  (id )   ,
  foreign key (tool_attribute_group_id  ) references tool_attribute_groups  (id )   ,
  foreign key (tool_attribute_id        ) references tool_attributes        (id )
) engine=innodb charset=utf8 collate=utf8_general_ci;

insert into tool_attribute_map (tool_id, tool_attribute_group_id, tool_attribute_id) values
-- site_index
(1, 1, 1),  -- css
(1, 2, 2),  -- columns
(1, 3, 3),  -- rakeback offers
(1, 4, 3),  -- propping offers
-- wizard
(2, 1, 1),  -- css
(2, 5, 4),  -- welcome_email from
(2, 5, 5),  -- welcome_email reply_to
(2, 5, 6),  -- welcome_email body
-- quick_signup
(3, 1, 1),  -- css
(3, 5, 4),  -- welcome_email from
(3, 5, 5),  -- welcome_email reply_to
(3, 5, 6),  -- welcome_email body
-- convert_account
(4, 1, 1),  -- css
(4, 5, 4),  -- welcome_email from
(4, 5, 5),  -- welcome_email reply_to
(4, 5, 6),  -- welcome_email body
(4, 6, 4),  -- convert_request_email from
(4, 6, 5),  -- convert_request_email reply_to
(4, 6, 6),  -- convert_request_email body
-- member_area
(5, 1, 1),  -- css
-- importer
(6, 7, 7);  -- xml href


-- user_tools
create table if not exists user_tools (
  id                int unsigned        not null                        auto_increment  primary key ,
  user_id           int unsigned                    default null                                    ,
  user_tag          varchar(255)                    default null                                    ,
  tool_id           tinyint unsigned                default null                                    ,
  preset            varchar(255)                    default null                                    ,
  created_at        datetime                        default null                                    ,
  updated_at        timestamp                                                                       ,
  index (user_tag, preset)                                                                          ,
  foreign key (user_id  ) references users(id)                                                      ,
  foreign key (tool_id  ) references tools(id)                                                      ,
  foreign key (user_tag ) references user_tags(tag) on update cascade
) engine=innodb charset=utf8 collate=utf8_general_ci;

    -- user_tools trigger
    create trigger user_tools_before_insert
    before insert on user_tools for each row set new.created_at = now();

    -- import current data
    insert into user_tools (id, user_id, user_tag, tool_id, preset, created_at, updated_at)
    (select id, user_id, user_tag, tool_id, name, created_at, updated_at from css order by id);



-- user_tool_values
create table if not exists user_tool_values (
  id                int unsigned        not null                        auto_increment  primary key ,
  user_tool_id      int unsigned                    default null                                    ,
  tool_attribute_id tinyint unsigned                default null                                    ,
  value             text                            default null                                    ,
  created_at        datetime                        default null                                    ,
  updated_at        timestamp                                                                       ,
  index (user_tool_id, tool_attribute_id  )                                                         ,
  foreign key (user_tool_id       ) references user_tools       (id )                               ,
  foreign key (tool_attribute_id  ) references tool_attributes  (id )
) engine=innodb charset=utf8 collate=utf8_general_ci;

      -- user_tool_values trigger
      create trigger user_tool_values_before_insert
      before insert on user_tool_values for each row set new.created_at = now();

      -- import current data
      insert into user_tool_values (user_tool_id, tool_attribute_id, value, created_at, updated_at)
      (select id, 1, code, created_at, updated_at from css order by id);