create table if not exists client (
    id integer primary key autoincrement,
    name text not null unique,
    email text,
    billable_rate integer,
    archived integer not null default 0,
    created_at datetime default current_timestamp
);

create table if not exists project (
    id integer primary key autoincrement,
    name text not null unique,
    client_id integer not null,
    billable_rate integer,
    archived integer not null default 0,
    created_at datetime default current_timestamp,
    foreign key (client_id) references client(id)
);

create table if not exists contractor (
    id integer primary key autoincrement,
    name text not null,
    label text not null,
    email text not null,
    created_at datetime default current_timestamp
);

create table if not exists invoice (
    id integer primary key autoincrement,
    year integer not null,
    month integer not null,
    number integer not null,
    client_id integer not null,
    total_amount integer not null,
    created_at datetime default current_timestamp,
    unique(year, month, number),
    foreign key (client_id) references client(id)
);

create table if not exists time_entry (
    id integer primary key autoincrement,
    start_time datetime not null,
    end_time datetime,
    description text,
    client_id integer not null,
    project_id integer,
    billable_rate integer,
    foreign key (client_id) references client(id),
    foreign key (project_id) references project(id)
);