

CREATE TABLE mmo_billing (
    billingno serial,
    City varchar(255),
    State varchar(50) default NULL,
    Street varchar(255) default NULL,
    Zip varchar(10) default NULL,
    Phone varchar(100) default NULL,
    PRIMARY KEY(billingno),
    FOREIGN KEY(billingno) references mmo_user(uid)
    on delete cascade
);

CREATE TABLE Characters (
    Character_Name VARCHAR(255),
    Last_Logoff_Location VARCHAR(255) DEFAULT 'Town',
    Character_Attack INT DEFAULT 0,
    Character_HP INT DEFAULT 0,
    Owner_ID SERIAL,
    Class VARCHAR(255) NOT NULL,
    Native_Server VARCHAR(255) NOT NULL,
    PRIMARY KEY (Character_Name),
    FOREIGN KEY (Owner_ID) REFERENCES mmo_user(uid)
    on delete cascade
);

CREATE TABLE Equipment (
    Equipment_Name VARCHAR(255),
    Slot INT NOT NULL,
    Attack INT NOT NULL,
    HP INT NOT NULL,
    Equipment_Icon VARCHAR(50),
    CONSTRAINT chk_slot CHECK (Slot >= 1 AND Slot <= 5),
    PRIMARY KEY (Equipment_Name)
);

CREATE TABLE Inventory (
    Character_Name VARCHAR(255),
    Equipment_Name VARCHAR(255),
    Size INT ,
    Equipped BOOL,
    CONSTRAINT chk_size CHECK (Size >= 16 AND Size <= 36),
    FOREIGN KEY (Character_Name) REFERENCES Characters(Character_Name),
    FOREIGN KEY (Equipment_Name) REFERENCES Equipment(Equipment_Name)
);

CREATE TABLE Server (
    Server_Name VARCHAR(50),
    Population INT DEFAULT 0,
    Timezone VARCHAR(3),
    Server_Type VARCHAR(3),
    Capacity INT DEFAULT 0,
    Server_Achievement VARCHAR(50),
    Server_Admin INT,
    PRIMARY KEY (Server_Name)
);

create table Achievement (
    achievement_id serial,
    name VARCHAR(50),
    point_amount INT,
    description VARCHAR(500),
    status BOOL DEFAULT FALSE,
    date DATE default NULL,
    A_uid int,
    PRIMARY KEY (achievement_id)
);

CREATE TABLE Ticket (
    Ticket_Number serial,
    AID INT,
    UID INT,
    Description VARCHAR(1000),
    PRIMARY KEY (Ticket_Number)
);

insert into mmo_billing (city, state, street, zip, phone) values ('Lincoln', 'Nebraska', '141 Prairieview Plaza', '68583', '402-649-9839');
insert into mmo_billing (city, state, street, zip, phone) values ('Albany', 'New York', '738 Fuller Center', '12222', '518-114-5916');
insert into mmo_billing (city, state, street, zip, phone) values ('Jacksonville', 'Florida', '000 Rieder Avenue', '32259', '904-916-2204');
insert into mmo_billing (city, state, street, zip, phone) values ('Topeka', 'Kansas', '1 Division Hill', '66629', '785-529-3073');
insert into mmo_billing (city, state, street, zip, phone) values ('Austin', 'Texas', '321 Warbler Drive', '78769', '512-451-0808');
insert into mmo_billing (city, state, street, zip, phone) values ('Des Moines', 'Iowa', '8494 Magdeline Trail', '50315', '515-634-0822');
insert into mmo_billing (city, state, street, zip, phone) values ('Los Angeles', 'California', '756 Vermont Road', '90030', '213-456-8053');
insert into mmo_billing (city, state, street, zip, phone) values ('Baton Rouge', 'Louisiana', '5 Hudson Center', '70826', '225-793-4698');
insert into mmo_billing (city, state, street, zip, phone) values ('Baton Rouge', 'Louisiana', '22 Gard Road', '70826', '225-793-5521');
insert into mmo_billing (city, state, street, zip, phone) values ('New Haven', 'Connecticut', '264 Calypso Avenue', '06520', '203-707-6033');
insert into mmo_billing (city, state, street, zip, phone) values ('Bakersfield', 'California', '502 Killdeer Road', '92648', '714-160-5158');
insert into mmo_billing (city, state, street, zip, phone) values ('Bakersfield', 'California', '782 Pywer Road', '90030', '213-556-8253');
insert into mmo_billing (city, state, street, zip, phone) values ('Bakersfield', 'California', '156 Georgia Street', '90030', '663-496-8073');

insert into Characters (Character_Name, Last_Logoff_Location, Character_Attack, Character_HP, Class, Native_Server) values ('Dagoth', 'City', 47, 52, 'Taco Vendor', 'Xel''lotath');
insert into Characters (Character_Name, Last_Logoff_Location, Character_Attack, Character_HP, Class, Native_Server) values ('Ario', 'Dungeon', 78, 99,  'Mage', 'Chattur''gha');
insert into Characters (Character_Name, Last_Logoff_Location, Character_Attack, Character_HP, Class, Native_Server) values ('Merle', 'Field', 91, 39,  'Warrior', 'Ulyaoth');
insert into Characters (Character_Name, Last_Logoff_Location, Character_Attack, Character_HP, Class, Native_Server) values ('Brnaba', 'Field', 57, 73,  'Warrior', 'Chattur''gha');
insert into Characters (Character_Name, Last_Logoff_Location, Character_Attack, Character_HP, Class, Native_Server) values ('Ester', 'Dungeon', 62, 14,  'Hunter', 'Ulyaoth');
insert into Characters (Character_Name, Last_Logoff_Location, Character_Attack, Character_HP, Class, Native_Server) values ('Sigismond', 'City', 15, 49,  'Taco Vendor', 'Xel''lotath');
insert into Characters (Character_Name, Last_Logoff_Location, Character_Attack, Character_HP, Class, Native_Server) values ('Cleavland', 'City', 56, 98,  'CowMan', 'Ulyaoth');
insert into Characters (Character_Name, Last_Logoff_Location, Character_Attack, Character_HP, Class, Native_Server) values ('Regan', 'City', 31, 58,  'Hunter', 'Xel''lotath');
insert into Characters (Character_Name, Last_Logoff_Location, Character_Attack, Character_HP, Class, Native_Server) values ('Shannon', 'Dungeon', 50, 27, 'Mage', 'Xel''lotath');
insert into Characters (Character_Name, Last_Logoff_Location, Character_Attack, Character_HP, Class, Native_Server) values ('Free', 'Field', 14, 90, 'Mage', 'Xel''lotath');

insert into Equipment (Equipment_Name, Slot, Attack, HP, Equipment_Icon) values ('Dagger', 1, 36, 89, null);
insert into Equipment (Equipment_Name, Slot, Attack, HP, Equipment_Icon) values ('Cloth_H', 3, 48, 81, null);
insert into Equipment (Equipment_Name, Slot, Attack, HP, Equipment_Icon) values ('Plate_H', 2, 100, 73, null);
insert into Equipment (Equipment_Name, Slot, Attack, HP, Equipment_Icon) values ('Horns', 1, 71, 33, null);
insert into Equipment (Equipment_Name, Slot, Attack, HP, Equipment_Icon) values ('Leather_B', 3, 93, 66, null);
insert into Equipment (Equipment_Name, Slot, Attack, HP, Equipment_Icon) values ('Taco', 2, 79, 17, null);
insert into Equipment (Equipment_Name, Slot, Attack, HP, Equipment_Icon) values ('Staff', 1, 57, 78, null);
insert into Equipment (Equipment_Name, Slot, Attack, HP, Equipment_Icon) values ('Plate_B', 5, 42, 41, null);
insert into Equipment (Equipment_Name, Slot, Attack, HP, Equipment_Icon) values ('Cloth_L', 2, 95, 89, null);
insert into Equipment (Equipment_Name, Slot, Attack, HP, Equipment_Icon) values ('Leather_H', 4, 56, 53, null);

insert into Inventory (Character_Name, Equipment_Name, Size, Equipped) values ('Dagoth', 'Horns', 19, false);
insert into Inventory (Character_Name, Equipment_Name, Size, Equipped) values ('Ario', 'Taco', 33, true);
insert into Inventory (Character_Name, Equipment_Name, Size, Equipped) values ('Merle', 'Cloth_H', 20, false);
insert into Inventory (Character_Name, Equipment_Name, Size, Equipped) values ('Brnaba', 'Cloth_H', 20, true);
insert into Inventory (Character_Name, Equipment_Name, Size, Equipped) values ('Ester', 'Cloth_H', 27, true);
insert into Inventory (Character_Name, Equipment_Name, Size, Equipped) values ('Sigismond', 'Staff', 31, false);
insert into Inventory (Character_Name, Equipment_Name, Size, Equipped) values ('Cleavland', 'Leather_B', 24, true);
insert into Inventory (Character_Name, Equipment_Name, Size, Equipped) values ('Regan', 'Horns', 35, false);
insert into Inventory (Character_Name, Equipment_Name, Size, Equipped) values ('Shannon', 'Staff', 22, false);
insert into Inventory (Character_Name, Equipment_Name, Size, Equipped) values ('Free', 'Staff', 19, true);

insert into Achievement (name, point_amount, description, status, date,a_uid) values ('Littletooth Sedge', 12, 'Contrast phlebogram-leg', true, '2020-5-23-',8);
insert into Achievement (name, point_amount, description, status, date,a_uid) values ('Spring Milletgrass', 11, 'Rt heart angiocardiogram', true, '2020-8-29-',2);
insert into Achievement (name, point_amount, description, status, date,a_uid) values ('Pride-of-rochester', 10, 'GAP flap, free', true, '2020-3-19',4);
insert into Achievement (name, point_amount, description, status, date,a_uid) values ('Haplophyton', 6, 'Insert spinal canal cath', true, '2019-12-3',2);
insert into Achievement (name, point_amount, description, status, date,a_uid) values ('Texas Gourd', 5, 'Chorioret les laser coag', true, '2020-1-21',9);
insert into Achievement (name, point_amount, description, status, date, a_uid) values ('Bigelow''s Tansyaster', 15, 'Other parathyroid ops', true, '2020-9-1',1);
insert into Achievement (name, point_amount, description, status, date, a_uid) values ('Black Jarba', 6, 'Spermat cord/vas dx NEC', true, '2020-9-3',7);
insert into Achievement (name, point_amount, description, status, date, a_uid) values ('Mexican Lindbergia Moss', 13, 'Bact smear-peritoneum', true, '2020-11-8',6);
insert into Achievement (name, point_amount, description, status, date, a_uid) values ('Purplestem Aster', 9, 'Periurethral incision', true, '2020-3-29',2);
insert into Achievement (name, point_amount, description, status, date, a_uid) values ('Navajo Evening Primrose', 5, 'Percu nephrostmy w frag', true, '2020-3-15',5);
insert into Achievement (achievement_id,name, point_amount, description, status, date, a_uid) values (112,'Baconator', 23, 'Percu nephrostmy w fragjjj', true, '2020-3-15',4);

insert into Server ( Server_Name, Timezone, Server_Type, Server_Achievement,server_admin) values ('Ulyaoth',  'PST', 'PvE', 'Fire Sword Badge',11);
insert into Server (Server_Name, Timezone, Server_Type, Server_Achievement,server_admin) values ('Chattur''gha',  'EST', 'PvE', 'Olaf Carrot',12);
insert into Server (Server_Name, Timezone, Server_Type,  Server_Achievement,server_admin) values ('Xel''lotath',  'JST', 'PvP', 'Samurai Badge',13);

insert into Ticket (AID, UID, Description) values (12, 4, 'Proin leo odio, porttitor id, consequat in, consequat ut, nulla. Sed accumsan felis.');
insert into Ticket (AID, UID, Description) values (11, 2, 'Cum sociis natoque penatibus et magnis dis parturient montes, nascetur ridiculus mus. Vivamus vestibulum sagittis sapien..');
insert into Ticket (AID, UID, Description) values (12, 1, 'In hac habitasse platea dictumst. Morbi vestibulum, velit id pretium iaculis, diam erat fermentum justo, nec condimentum neque sapien placerat ante.');
insert into Ticket (AID, UID, Description) values (13, 8, 'Suspendisse potenti. In eleifend quam a odio. In hac habitasse platea dictumst. Maecenas ut massa quis augue luctus tincidunt.');
insert into Ticket (AID, UID, Description) values (11, 9, 'Aenean fermentum. Donec ut mauris eget massa tempor convallis.');
insert into Ticket (AID, UID, Description) values (12, 8, 'Vestibulum ante ipsum primis in faucibus orci luctus et ultrices posuere cubilia Curae; Duis faucibus accumsan odio.');
insert into Ticket (AID, UID, Description) values (11, 3, 'Proin leo odio, porttitor id, consequat in, consequat ut, nulla. Sed accumsan felis.');
insert into Ticket (AID, UID, Description) values (11, 5, 'Morbi sem mauris, laoreet ut, rhoncus aliquet, pulvinar sed, nisl. Nunc rhoncus dui vel sem.');
insert into Ticket (AID, UID, Description) values (11, 5, 'In est risus, auctor sed, tristique in, tempus sit amet, sem. Fusce consequat.');
insert into Ticket (AID, UID, Description) values (12, 5, 'Donec diam neque, vestibulum eget, vulputate ut, ultrices vel, augue.');