create table categories(
  id integer primary key asc,
  title varchar(16) unique not null,
  description varchar(255) not null,
  created_at datetime default current_timestamp,
  updated_at datetime default current_timestamp
);

create table customers(
  id integer primary key asc,
  first_name varchar(255) not null,
  last_name varchar(255) not null,
  address text,
  created_at datetime default current_timestamp,
  updated_at datetime default current_timestamp
);

create table media(
  id integer primary key asc,
  title varchar(255) unique not null,
  m_type varchar(255) check(m_type in ('blu-ray', 'dvd', 'game')) not null default('dvd'),
  category_id integer not null,
  created_at datetime default current_timestamp,
  updated_at datetime default current_timestamp,
  foreign key(category_id) references categories(id)
);

create table rentals(
  id integer primary key asc,
  rented_at datetime not null,
  due_at datetime not null,
  customer_id integer not null,
  media_id integer not null,
  created_at datetime default current_timestamp,
  updated_at datetime default current_timestamp,
  foreign key(media_id) references media(id),
  foreign key(customer_id) references customers(id)
);

create index index_media_on_category_id on media(category_id);
create index index_rentals_on_customer_id_and_media_id on rentals(customer_id, media_id);

.schema