create database nsa;

use nsa;

create table academy(id int auto_increment primary key, academyName varchar(200) not null unique, academyEmail varchar(200) not null, academyGrndName varchar(200) not null, academyCntctName varchar(200) not null, academyAddress varchar(200) not null, academyMobile varchar(20) not null, status char not null default 'y', createdDate timestamp default now(), updatedDate timestamp default now());

create table skaters(id int auto_increment primary key, skaterName varchar(200) not null unique, skaterEmail varchar(200) not null, skaterAcademyID varchar(200), skaterDOB date not null, skaterAddress varchar(200) not null, skaterMobile varchar(20) not null, bloodGrp varchar(100) default '', RSFIRegNumber varchar(100) default '', status char not null default 'y', createdDate timestamp default now(), updatedDate timestamp default now());

create table attendance(id int auto_increment primary key, skaterID varchar(200) not null, academyID varchar(200) not null, classDate timestamp, attStatus boolean default FALSE, createdDate timestamp default CURRENT_TIMESTAMP, updatedDate timestamp default CURRENT_TIMESTAMP, CONSTRAINT attMatchFinder UNIQUE (skaterID, academyID, classDate));

