-- users_info
create table if not exists users_info(
    id int(10) unsigned PRIMARY KEY AUTO_INCREMENT NOT NULL,
    user_id int(10) unsigned NOT NULL,
    name_kana varchar(255) DEFAULT NULL,
    company varchar(255) DEFAULT NULL,
    affiliation varchar(255) DEFAULT NULL,
    address varchar(255) DEFAULT NULL,
    telephone varchar(255) DEFAULT NULL,
    created_at timestamp NOT NULL,
    updated_at timestamp NOT NULL,
    deleted_at timestamp NULL DEFAULT NULL,
    index(user_id),
    foreign key(user_id) references users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- courses
create table if not exists courses(
    id int(10) unsigned PRIMARY KEY AUTO_INCREMENT NOT NULL,
    course_no int(10) unsigned NOT NULL,
    name varchar(255) DEFAULT NULL,
    description varchar(255) DEFAULT NULL,
    price  int(10) unsigned NOT NULL DEFAULT 0,
    enabled tinyint(1) NOT NULL DEFAULT 0,
    created_at timestamp NOT NULL,
    updated_at timestamp NOT NULL,
    index(course_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- contracts
create table if not exists contracts(
    id int(10) unsigned PRIMARY KEY NOT NULL AUTO_INCREMENT,
    user_id int(10) unsigned NOT NULL,
    course_no int(10) unsigned NOT NULL,
    expiramation_date timestamp NOT NULL,
    created_at timestamp NOT NULL,
    updated_at timestamp NOT NULL,
    index(user_id, course_no),
    foreign key(user_id) references users(id),
    foreign key(course_no) references courses(course_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- chapters
create table if not exists chapters(
    id int(10) unsigned PRIMARY KEY NOT NULL AUTO_INCREMENT,
    course_no int(10) unsigned NOT NULL,
    chapter_no int(10) unsigned NOT NULL,
    name varchar(255) DEFAULT NULL,
    description varchar(255) DEFAULT NULL,
    enabled tinyint(1) DEFAULT 0 NOT NULL,
    movie mediumtext DEFAULT NULL,
    textbook mediumtext DEFAULT NULL,
    created_at timestamp NOT NULL,
    updated_at timestamp NOT NULL,
    index(course_no, chapter_no),
    foreign key(course_no) references courses(course_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- role_admin
create table if not exists role_admin(
    id int(10) unsigned PRIMARY KEY NOT NULL AUTO_INCREMENT,
    user_id int(10) unsigned NOT NULL,
    created_at timestamp NOT NULL,
    updated_at timestamp NOT NULL,
    index(user_id),
    foreign key(user_id) references users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- news
create table if not exists news(
    id int(10) unsigned PRIMARY KEY NOT NULL AUTO_INCREMENT,
    title varchar(255) DEFAULT NULL,
    content mediumtext DEFAULT NULL,
    created_user int(10) unsigned,
    updated_user int(10) unsigned,
    created_at timestamp NOT NULL,
    updated_at timestamp NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;